Please: (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.
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?)
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 www.sqldbcontrol.com
http://www.sql-server-performance.com/ak_inside_sql_server_maintenance_plans.asp http://www.ftponline.com/Archives/premier/mgznarch/vbpj/2001/10oct01/sqlpro0110/jf0110/jf0110-1.asp http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1084411,00.html Satya SKJ Microsoft SQL Server MVP Contributing Editor & Forums Moderator http://www.SQL-Server-Performance.Com This posting is provided �AS IS� with no rights for the sake of knowledge sharing.
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.
