Automated SQL Server Backups

SQL Server 2000

1. Open SQL Server Enterprise Manager
   
2. Register your server
   
3. Right-click the node Management > Backup, then select menu Add backup devices... We create devices "DBName Mondays.bak" to "DBName Sundays.bak" corresponding to each day of the week but you can use your own naming convention. We have assumed that DBName is the name of the database to backup.
   
4. Right-click the node Databases > DBName, then select menu option All Tasks > Backup database...
   
5. The SQL Server Backup – DBName dialog box is displayed.
   
6. On the General tab, rename the backup "DBName Mondays", click the Remove button if an item appears in the backup to list, then click Add...
   
7. In the Select Backup Destination backup select Backup Device and "DBName Mondays" then click OK.
   
8. Make sure Database - complete and Overwrite existing media are selected.
   
9. Check schedule and click ... to edit. Rename the schedule "DBName Mondays" and schedule to occur weekly on Mondays. Click OK.
   
10. On the Options tab, click Verify backup upon completion and Remove inactive entries from transaction log. Keep other options unchecked.
    
11. Go through steps 4 to 10 with "DBName Tuesdays" to "DBName Sundays".
    
12. Go to the Management > SQL Server Agent > Jobs node, refresh the list and check the 7 backup jobs which you have just created.
    

SQL Server 2005

This is slightly more complicated in SQL Server 2005 because you cannot use the backup dialog which has no scheduling option. You need to use a maintenance plan. A maintenance plan is an SSIS package, which means you either need SSIS or SP2 installed, because SP2 includes a limited version of SSIS for maintenance tasks including backups.

1. Open SQL Server Management Studio (Run as Administrator on Windows Vista).
   
2. Right-click the node Server Objects > Backup Devices and select menu "Add Backup Device...".
   
3. Name your backup device "DBName Mondays" and make it a file named "DBName Mondays.bak".
   
4. Repeat steps 2 and 3 for "DBName Tuesdays" to "DBName Sundays".
   
5. Start and configure SQL Server Agent which is a requirement for maintenance plans.
   
6. Right click the node Management > Maintenance Plans and select menu "New Maintenance Plan..."
   
7. Rename your plan "DBName Plan".
   
8. Double-click "Subplan_1" to edit its properties.
   
9. Name it "DBName Mondays" and create a schedule of the same name recurring weekly every Monday.
   
10. Drop a "Back Up Database Task" from the toolbox onto the light yellow design surface.
    
11. Right-click the task on the design surface and select menu "Edit...".
    
12. Select the database(s) you want to back up, check option "Back up databases across one or more files", then click button "Add..." and select the backup device called DBName Mondays.
    
13. Select "Overwrite" if backup file exists, check "Verify backup integrity" and click OK.
    
14. On top of the list of subplans of "DBName Plan", click "Add Subplan".
    
15. Go through steps 9 to 14 with "DBName Tuesdays" to "DBName Sundays".
    
16. Go to the SQL Server Agent > Jobs , refresh the list and check the 7 backup jobs which you have just created.
    

For both SQL Server 2000 and SQL Server 2005, you can consider adding tasks to shrink databases, rebuild indexes, check database integrity in the scheduled jobs, but this goes beyond the scope of this article. In a following article, we will show you how to schedule FTP transfers to push and/or pull the backup files.