Backup only data older than 1 year

[ktai]

Does anyone know if there's a way to backup only data older than 1 year. What I am trying to do is first backup data older than 1 year and then purge that data from the database. I only want 1 year of data in the database, so I would probably run the backup/purge operation monthly.

Thanks for any help or suggestions!

 

[ptheriault]

You will have to write your own script to select the data out of each table into tables in another database. Then you can backup that database and remove the data from the other database.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[ktai]

Thanks, Paul. I probably should have mentioned that there are over 100 tables in the database. Do I have to manually put the name of each table into my script, or is there a way to simplify the proccess?

Thanks again.

 

[ptheriault]

Here is a quick way to create a copy of a table without any data.

SELECT * INTO <new_table_name>
FROM <old_table>
WHERE 1=2
[code]

I would create a new database.
Then run this in your current database.
It will create a script that will create your tables for you.
[code]
SELECT 'SELECT * INTO '+ name+'_bak FROM <db_name>.dbo.'+ name + ' WHERE 1=2'
FROM sysobjects
WHERE xtype = 'U'
[code]
Then you will need to write scripts to select data older than 1 year and populate your tables in the new database.
You can then back it up and remove the old data from your prod db.

- Paul  [img]http://i19.tinypic.com/10qkyfp.gif[/img]
- If at first you don't succeed, find out if the loser gets anything.