Scheduled Backups in Microsoft SQL 2005

Open SQL Server Management Studio

Management Studio is installed with SQL Server by default, but can be found on Microsoft’s website:

http://www.microsoft.com/downloads/details.aspx?FamilyId=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en

Connect to your SQL server

When running Management Studio on your database server via Remote Desktop, a Windows administrative account will grant access to manage the database server. When connecting remotely, it is necessary to login with a SQL Server role, usually the “sa” (server administrator) role.

Navigate to the Maintenance Plans section

The section for configuring Maintenance Plans is displayed below:

Create a blank Maintenance Plan

Right-click the Maintenance Plans category and choose “New Maintenance Plan” from the menu. A dialog will appear for naming the plan; it is best practice to give the plan a descriptive name like “Daily Backup & Cleanup”. Once created, the blank plan will be displayed in the right pane.

After creation, you should see something similar to this:

Add tasks to the maintenance plan

Step 1: Shrink Database task

Create a Shrink Database task by dragging it from the list in the sidebar into the “drag tasks here” section. Double-click the task, and choose the databases to shrink (usually “All user databases” is the best option). After choosing the databases to shrink, the grayed-out options in the first dialog will activate, allowing fine-tuning of the shrink operation.

Step 2: Back Up Database task

Create a backup task and double-click it, select the databases to backup and a location to store the backups, and whether to use separate folders for each database’s backups (recommended if you have a large number of databases).

It is VERY IMPORTANT to choose a location that will not run out of space quickly, preferably on a drive/partition separate from where your MS SQL data files are stored.

Step 3: History Cleanup task

Create a History Cleanup task,  and edit its properties to select only “Backup and restore history” (or keep the others checked if desired). Choose a duration for backup retention; we typically recommend a week (7 days) for daily backups:

Step 4: Link the tasks together

When selecting any single task, you will notice green arrows extending downward from the task. Dragging the arrows to another task will cause SQL Server to execute the tasks in the chosen sequence. It is best to link them as shown:

Step 5: Set a schedule

Since our example is a daily backup, we will configure the plan to run every night at 2:00AM. Click the ellipsis “…” button on the right side of the window, across from the Schedule label, to present the scheduling dialog.

Step 6: Save the plan to complete the process

Right-click the maintenance plan and choose Save Selected Items, which will activate the plan, placing it in the sidebar under Maintenance Plans. If desired, the plan can be executed by right-clicking its sidebar entry and choosing Execute from the menu.

Conclusion

After setting up the schedule for the maintenance plan, configuration is complete, and backups will be taken regularly as scheduled.