DougHughes.net

Over the weekend I received a question from a blog reader asking how to create a backup script for SQL server. Rather than answering this in email I thought I’d share this knowledge with the world at large.

First off, you don’t need to do anything too complicated to create a backup script. SQL Server Management Studio has everything built in that you need.

To start creating a backup script first check that SQL Server Agent is running. If not, you’ll get error saying that the ‘Agent XPs’ component is turned off. To turn SQL Server on, simply open SQL Server Management Studio and rick click on SQL Server Agent and click Start. SQL Server Agent will start and you should no longer get the error about ‘Agent XPs’.

Unlike SQL 2000, creating a backup plan in SQL 2005 is not really very intuitive. In SQL 2000 you could simply use a wizard to create a maintenance plan. However in SQL 2005 you actually create a SQL Server Integration Services project which runs the backup for you. (Actually this is the same basic thing you did in SQL 2000, but the interface was nicer.)

First, click to expand the Management node under your server and then right click on the Maintenance Plans node and click New Maintenance Plan.

Give the new plan a name and click ok. This opens a new maintenance plan. On the surface the new interface looks completely useless.

However, you actually have a fair amount of power. The first thing you may notice is that you can give you plan a description and set the schedule for it. I’ll come back to scheduling this in a bit. Also note the Connections button. This is where you can select which servers your backup script will apply to. This automatically uses localhost, which is what I want for my script. You may wish to change this to another server for your script.

Look under the Object Explorer and you’ll see a Toolbox window. This is where you can decide what you want to do in your Maintenance Plan. Your options include backing up you databases, checking database integrity and more. All of the tasks essentially follow the same patterns as well. I’ll show you how to backup your databases and run integrity checks. After that you should know enough to start playing with some of the other tasks available to you.

Let’s start dragging the Back Up Database Task to the big open area in the center of your window. Once it’s there right click on it and click Edit.

Now you will be looking at the properties for the Back Up Database Task. If you’ve ever manually backed up a database this will look familiar.

Select which databases you want to backup from the databases drop down. I tend to choose all user databases.

Once you have selected the databases you want to backup you choose a destination. I’m assuming you’re backing up to disk. I also create a backup file with a subdirectory for each database.

Note that when you select the directory you want to backup into you may expect to see the directory structure on the computer you’re currently using. If you are not working from the console of your SQL server you may be surprised to instead see the directory structure of you SQL Server. Keep in mind that in this case the SQL Server Management Studio is actually connecting you to a server process that is on another computer. For this reason you’ll see that server’s directory structure. You also can’t browse to any network shares. For the purposes of this tutorial I’m simply going to select a directory local to the SQL server. However, if you want to backup to a network share you’ll need to make sure that the SQL Server Agent (I believe) is running on an user account that has access to that network share. And at that point you can manually type in the path to the network share you want to backup to.

Lastly I always check the backup integrity.

Click OK to return back to you plan.

Next let’s add a Check Database Integrity Task by dragging it into your backup plan.

Right click on this task and click edit to view its properties. For my plan I selected to check all user databases.

The last thing you need to do is tell your backup plan what order you want the backup and check database tasks to run in. This is actually very simple and (once you’ve done it once) intuitive. I want my backup script to run first so I’m going to select my Back Up Database Task. Note that once it’s selected a green arrow hangs down from it. Just select the tip of that arrow and drag it to the Check Database Integrity Task. This will set the sequence that first the backup task is run then the Check Database task.

Save your plan by clicking the save icon or using the File menu. If you now refresh your Maintenance Plans node in the Object Explorer you’ll see your plan listed.

To run you plan you simply right click on it and click Execute.

The last thing you’ll want to do is create a schedule for your plan. To do so, click on the ellipsis (…) next to the schedule box under the description of your plan.

Use the dialog to control when you want backups to run. I’m setting mine to run once a day at 1:00 AM.

And that’s it. Your databases will now be backed up to the location you specified according to the schedule you specified.

Of course, this was a simple example. You can get a lot more complicated if you dig around in the various tasks you have at your disposal.