-
SQL 05 questions
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.
-
Originally Posted by rmiao
Sql doesn't do table backup, you can set maintenance plan to delete old db backup files.
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?
-
Sql
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
Originally Posted by rmiao
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?
-
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)
-
Originally Posted by h2sut
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)
What are your business requirements here? The solution seems a little inelegant and there may be a better one .
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|