SQL Backup Plan for MSSQL 2005 / 2008

If you use MS SQL 2000, click here.

If you use SQL Express, click here.

Note:  If you have any questions or need assistance on creating a SQL maintenance plan, please contact Technical Support.

This article will show you how to create a SQL maintenance plan so that the MS SQL database(s) on your dedicated server can be backed up if you have our Dedicated Backup option.

With the creation of a SQL maintenance plan, your databases will be backed up daily. Since programs like NTBackup and CommVault do not backup running databases, you will thus be assured of always having backups.

Follow these steps to create a SQL backup plan for MSSQL 2005 /2008:

Microsoft SQL Server Management Studio
1. From your dedicated server, open up SQL Server Management Studio (Start -> All Programs -> Microsoft SQL Server -> SQL Server Management Studio).

2. Login with Windows authentication.

3. Verify that the SQL Server Agent is turned on (Go to Start -> Run -> type services.msc and right click SQL Server Agent then set the Startup type to Automatic).

Management
4. Expand the Local Windows database server and double click on 'Management.'

5. Right click on Maintenance Plans and click 'New Maintenance Plan'.

Subplan
6. From the toolbox window, on the bottom left, drag the following tasks into the white space to the right:
'Back Up Database Task'
'Maintenance Cleanup Task'

7. Click on the box for the Back Up Database Task, and you will see a green arrow at the bottom of the box. Click and hold onto the green arrow and drag it to Maintenance Cleanup Task ( the green arrow should be pointing to the Maintenance Cleanup task now). Right click on “Back Up Database Task” and click edit.

Back Up Database Task
8. The settings should look like the following:
Connection: Local server connection
Databases: All databases (click on down arrow and choose the All databases radio button)
Backup type: Full
Back up to: Disk
Make sure the “Create a backup file for every database” is marked.
Folder: C:\DB backups (create this folder under C:\ before saving)
Check Verify backup integrity.
Click OK.
Right click on the “Maintenance Cleanup Task” and click edit.

Maintenance Cleanup Task
9. Connection: Local server connection
Delete files of the following type: Backup files
Make sure “Search folder and delete files based on an extension” is checked
Folder: C:\DB backups
File extension: bak
Make sure the “Delete files based on the age of the file at task run time” is checked
Delete files older than the following: 1 Week(s)
Click OK.

You will now need to schedule this job to run every night. At the top of the DB backups [Design]*, you will see Schedule. Click on the “…” button to the right.

Schedule
10. Schedule type: Recurring
Occurs: Daily
Recurs every: 1 day(s)
Occurs once at: 10:00:00 PM (adjustable to what you would like)
Click OK.
Go to File and select Save Selected Items.

This will create a SQL Maintenance plan to backup your MSSQL database(s).

Add Feedback