I would like for you to come up with the SQL that will automatically delete any table backups that are older than 3 days.
Printable View
I would like for you to come up with the SQL that will automatically delete any table backups that are older than 3 days.
Sql doesn't do table backup, you can set maintenance plan to delete old db backup files.
Quote:
Originally Posted by rmiao
Yea but i need to a store procedure that i can run that will delte any table that are 3 days old. I already have maintence plan for that but i want a store procedure that i can run that does it automatcially when i run the query.
What do you mean delete any table that are 3 days old? You only use table for three days?
this stored proc (sp) should first backup the table if it doesn't exist and then delete all but the most recent 3.
take the number of backups (3) as a parameter to the sp and the database name as a parameter to the sp
that way it can be stored in one db and used across databases
that means it will need to build the sql dynamically and use the sp_executesql
Again, sql can't backup table directly. Sounds like you want to backup db and delete backup files older than 3 days. Why don't do it in maintenance plan?
Say i had a system table in that table i had dbo.user then (dd,mm,yyyy) after that i had dbo.user_backup_ 10_12_2007,dbo.user_backup_ 10_13_2007,dbo.user_backup_ 10_14_2007,dbo.user_backup_ 10_15_2007,dbo.user_backup_ 10_16_2007
If i use the SQL command
SELECT * FROM paychexdb.dbo.sysobjects
WHERE name like 'users_backup_%' AND xtype = 'U'
AND name not in( SELECT Top 3 name FROM paychexdb.dbo.sysobjects
WHERE name like 'users_backup_%' AND xtype = 'U'
ORDER BY Name DESC)
results would be 10_12_2007,10_13_2007.
So i want to put those results in a backup file and keep top 3 which is 10_14,10_15,10_16. I want to create a store procedure that i can execute and do that for all my tables i
Quote:
Originally Posted by rmiao
What do you mean backup file? Another table to save this result? If you want to drop those tables, you can generate sql statements like following then run them:
SELECT 'drop table ' + name FROM paychexdb.dbo.sysobjects
WHERE name like 'users_backup_%' AND xtype = 'U'
AND name not in( SELECT Top 3 name FROM paychexdb.dbo.sysobjects
WHERE name like 'users_backup_%' AND xtype = 'U'
ORDER BY Name DESC)
What are your business requirements here? The solution seems a little inelegant and there may be a better one :) .Quote:
Originally Posted by h2sut