Purging data from database every 3 months

Every 3 months I need to backup that sql database and archive it off. I only want to keep 3 months of data in the database and want to know how you can set a backup to run and then remove all the data older than a specified date. I am guessing you will have to back it all up then run a query to remove the data I dont want. ANy ideas if this can be done with a simple backup routine ?You can't purge the old data using BACKUP. You have to execute TSQL commands to delete the old data,
which in turn of course mean that the data need some datetime column from which you can determine if
it is > 3 months old. Basically, the TSQL script will look something like:
DECLARE @.now datetime
SET @.now = CURRENT_TIMESTAMP
DELETE FROM tbl1 WHERE DATEDIFF(month, dtcol, @.now) > 3
DELETE FROM tbl2 WHERE DATEDIFF(month, dtcol, @.now) > 3
Make sure you check that I get the DATEDIFF right. Also, you might want to protect the delete's
inside a transaction. And if you have relationships between the tables you need to start with the
referencing table before you can delete from the referenced table (unless you have cascading foreign
keys). If you aren't well versed in TSQL, I suggest you get someone who is which can help you with
that TSQL script.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"c1tr1cks" <anonymous@.discussions.microsoft.com> wrote in message
news:EED1D1C0-4A93-4822-AB09-1CDEBCBA6277@.microsoft.com...
> Every 3 months I need to backup that sql database and archive it off. I only want to keep 3 months
of data in the database and want to know how you can set a backup to run and then remove all the
data older than a specified date. I am guessing you will have to back it all up then run a query to
remove the data I dont want. ANy ideas if this can be done with a simple backup routine ?|||You can see an example here:
http://vyaskn.tripod.com/sql_archive_data.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"c1tr1cks" <anonymous@.discussions.microsoft.com> wrote in message
news:EED1D1C0-4A93-4822-AB09-1CDEBCBA6277@.microsoft.com...
Every 3 months I need to backup that sql database and archive it off. I only
want to keep 3 months of data in the database and want to know how you can
set a backup to run and then remove all the data older than a specified
date. I am guessing you will have to back it all up then run a query to
remove the data I dont want. ANy ideas if this can be done with a simple
backup routine ?