Microsoft SQL Server is a very powerful and robust database application. It is the premiere choice of most large organizations who utilize a database solution. In most cases the data being stored in these databases are mission critical and coming up with a disaster recovery plan is top priority.
There are many parts to a proper disaster recovery plan but in this guide I am going to go over setting up SQL Server backups. By backups, I mean using SQL Servers built in backup system. This is called creating a SQL Server 2005 Maintenance Plan. I’m going to strongly recommend that you install the SQL Server 2005 Service Pack 1 first. There are a lot of improvements in this Service Pack and it would be beneficial to have it loaded.
The first step in creating a Maintenance Plan is connecting to the database server with Management Studio.
Once connected to the server, go under the Management folder, then right click the Maintenance Plan folder in object explorer and choose new Maintenance Plan. Enter the name for your Maintenance Plan and hit okay. Now a Maintenance Plan design panel will appear on the right, and a toolbox with available Maintenance Plan Tasks will be displayed on the left.
Now we need to create a task for this SQL Server 2005 Maintenance Plan to execute. Go ahead and drag and drop the “Backup database Task” from the toolbox to the design panel. Then double click the item and you will be presented with several options.
Click on the databases dropdown box and choose the database(s) that you are wanting to backup. For the backup type, choose Full. You can also choose differential or transaction log, but you will most likely want to create a Full backup first. For the Back up to parameter, choose Disk. Make sure that the Create a backup file for every database option is selected and the Create a sub-directory for each database box is checked. You can use the default destination folder or specify a different one. For example, you could specify the network folder, \servernameDBbackupssql2005. For the Backup file extension make sure that its value is “bak” without a leading dot. Check the Verify backup integrity box. Once you have verified all of these settings, then hit okay.
Now we want to setup a schedule for the SQL Server 2005 backup job. Click the little calendar button next to the subplan.
The job schedule properties window will appear. On this window you will want to start by choosing the schedule type. In most cases you will want to choose recurring, but you could also choose one time or start whenever CPU’s become idle, so that the backup happens when there is low or no activity on the server. If you chose recurring you can choose the frequency that it occurs, such as weekly on Monday. Choose the time in which this should occur. I usually choose somewhere between 12am-3am for SQL Server 2005 backups, depending when other backups or processes run at night. Choose the start date, which will usually be today, and then hit okay.
Next you may want to configure the reporting and logging of this Maintenance Plan. Click the reporting and logging button below the design window.
On the reporting and logging window, choose to either generate a text file report or append to an existing log file. You can also choose to have the report sent via email to a recipient. Keep in mind that the recipient must be setup as an operator in SQL Server 2005 and have an email address specified. You must also have SQL Server 2005 configured to use Database Mail. This is beyond the scope of this guide, but you can research the topic and find instructions for setting this up. This is a very good feature to have, in case you manage multiple SQL Server backups and need to receive emails when backups fail.
Now that we have the backup job created in SQL Server 2005 and the logging set, we can test the job to make sure it completes successfully. Click on the plus next to SQL Server Agent in the Object Explorer. Then click the plus next to the jobs folder and you should see your Maintenance Plan and its corresponding job. Right click the job and choose “Start Job at Step…”. The job should begin and will eventually succeed or fail.
If the job fails, you will need to go look at the log files to find out why. The log file will be located in the directory specified in the reporting and logging screen. If the error is hard to understand, search for it online and you will most likely find information on steps you can take to resolve it.
There are several other tasks you can add in and link in your SQL Server 2005 Maintenance Plan. One is the Maintenance Cleanup Task. This is the task you use to delete old backups so that you don’t run out of hard drive space. Drag and drop the Maintenance Cleanup Task icon from the toolbox to the design panel and double click on it. From the Maintenance Cleanup Task window, first choose what type of files you want to delete. Usually it will be backup files. Then specify the location to search for these files, which will be the location you specified earlier to backup your database at. Make sure to enter the file extension bak without a period. Check to include first level sub folders, if you created a folder for each database. Then choose the age of the files you are going to delete. I chose 2 weeks or older. Then hit okay. Now you have a Maintenance Cleanup Task. You will probably want to link this task to the Back Up Database Task. If you click on the Backup Database Task, you can click the little square at the bottom and link it to the Maintenance Cleanup Task. That way it will execute after the Backup Database Task.
Now you may want to run the job again to make sure it completes. If it does, then you are finished setting up your SQL Server 2005 Backups. Be sure to either monitor the backups or setup an alert if the jobs fail, so you can fix it.
