Results 1 to 9 of 9

Thread: SQL 05 questions

  1. #1
    Join Date
    Dec 2007
    Posts
    4

    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.

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Sql doesn't do table backup, you can set maintenance plan to delete old db backup files.

  3. #3
    Join Date
    Dec 2007
    Posts
    4
    Quote 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.

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    What do you mean delete any table that are 3 days old? You only use table for three days?

  5. #5
    Join Date
    Dec 2007
    Posts
    4
    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

  6. #6
    Join Date
    Sep 2002
    Posts
    5,938
    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?

  7. #7
    Join Date
    Dec 2007
    Posts
    4

    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

    Quote 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?

  8. #8
    Join Date
    Sep 2002
    Posts
    5,938
    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)

  9. #9
    Join Date
    Oct 2007
    Posts
    11

    Smile

    Quote 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
  •