SQL Server Performance

"rolling" database backup -- how to do?

Discussion in 'General DBA Questions' started by Malcolmx, Aug 8, 2006.

  1. Malcolmx New Member


    (1) I want to backup a database nightly (easy . . .).

    (2) And retain only the last 7 days of backups (can't figure that out . . .).

    (3) How do I define/schedule a backup that will only retain the previous 7 occasions?

    Thank you.
  2. Malcolmx New Member

    CLARIFICATION: I want to backup to disk. If I "append" each day's backup to the backup device, I can access any given day for a restore. Fine. But how do I delete instances of the backup from the backup device that are older than 7 days? (So that the backup device contains only the most recent 7 days of backups?)
  3. SQLDBcontrol New Member

    There are several ways in which you can achieve this but the easiest way is probably to set up a database maintenance plan and indicate that you want to remove files older than 7 days old.

    Karl Grambow

  4. satya Moderator

  5. xiebo2010cx Member

    The best way is maintenance plan, it is easily manageable,

    for my database, each instance, I created 2 maintenance plan, one is for SystemDB, backup msdb and master weekly, retention is 2 weeks. The other is UserDB maintenance plan, full backup daily, retain 3 days. log backup every 2 hours during the busy time, retain 3 days. So everytime when the maintenance plan job kicks off, it will first do backup, then delete the backup files more than 3 days.

    You are able to specify the folder path (SQL Server will go there to check the file age) and file extension you want to delete when you create the maintenance plan.

Share This Page