How to Create a Simple Backup Job in SQL Server

There are several ways to create a backup job in SQL Server 2008 and I’m going to show you how to create a very simple one for both a single database and for multiple databases. And since there are so many ways to do this, I’m going to briefly discuss most of them at the end of this article so you’ll have a good idea of what other options you have.

So let’s go ahead and jump in and get started with a very simple backup routine.

First you start by opening a query window in SSMS and connect to the server you want to backup. It’s worth noting that it doesn’t matter which database you’re connect to, but I typically do it from master just to be consistent.

The basic backup syntax that you need to type is here:

BACKUP DATABASE databaseNameTO DISK = 'file location'

OK, let me explain a couple of these items.

BACKUP Database – T-SQL command for what you want to do, which in this case is backing up a database.

databaseName – this is the name of the database you want to backup.

To disk = ‘file location’ – Backups are written to files. This is the full path to the file. Typically, a full backup such as this one will have the extension of .bak, but that’s just what everyone does. It can have any extension you like or no extension at all if you like.

Now let’s see a real example with all of the values filled in. Here we’re going to backup the Model database to a file called ModelBackup.bak on C:\.

BACKUP DATABASE ModelTO DISK = 'c:\ModelBackup.bak'

That’s it. You have now taken a full backup of the Model database. I’ll also go ahead and add that unless SQL Server 2008 was installed with a case-sensitive collation (this is very rare), the parser ignores whitespace and case. For example, the following 3 commands are equal on a case-insensitive system.backup database ModelTo disk = 'c:\ModelBackup.bak'

BACKUP DATABASE MODELTo disk = 'c:\ModelBackup.bak'

bacKup DataBasE ModeL To dIsk = 'C:\MODELBACKUP.BAK'

However, you don’t want to backup your database by hand every time so you want to schedule this to run on its own. And for this you’re going to put the above code into a job and schedule it. The easiest way to do this is to create a job in SSMS. Follow these steps to create the job and put your backup statement inside of it.

Expand the ‘SQL Server Agent’ tree and right-click on ‘Jobs’. Then choose the top item, ‘New Job…’

2. Now you’ve got the new job dialog box. Filling in the info is pretty easy. You need to give your job a name, and everything else is optional. Here I’m going to fill in the name of the job as ‘Backup user database’.

3. Next click on the ‘Steps’ pane on the left and you’ll be presented with this screen. It’s blank because you haven’t created any steps yet. So go ahead and click on the ‘New’ button at the bottom.

4. This is where the real magic happens. Again, you have to fill in a name so you know what the step is called. Make it something descriptive. There are several step types to choose from, but the default is T-SQL and since we’re running a T-SQL command that’s clearly the one we want to go with. The database defaults to ‘master’ and that’s just fine with us. Here’s what we have so far. You see the only thing we’ve had to do is fill in the step name.

5. The only thing left to do is to copy your backup statement into the query window. Of course it’s always a good idea to make sure your code will parse before you try to run it. Just click the ‘Parse’ button I’ve circled. Here’s what that looks like.

6. Now click OK and it’ll take you back to your new job window and now you’ll see your job step listed. And you can stack as many as you like inside there.

7. To schedule your job, just click on ‘Schedules’ on the left and then choose the schedule that’s right for you. It works similar to the the way it does in Windows so there’s really not much need for me to rehash it here.

8. Once you click OK all the way out until the new job box is closed, your job will be added to the job tree. You may have to right-click on ‘Jobs’ and refresh the display for it to show up.

Now that you’ve created the job to backup your database I’d like to go back to the command again and add one more part. See, when you backup a database to a file it won’t overwrite the file by default. By default it will append to the file, so your backup file will just get bigger and bigger. So what you want to do is add a special flag to the command that tells it to initialize (or overwrite) the file every time. That makes our backup command look like this now:

BACKUP Database Model

To disk = ‘c:\ModelBackup.bak’

With INIT

This is a good thing to keep in mind while you’re creating your job.

But what if you wanted to backup more than one database? Well, again, there are several ways to do this and I’m going to show you the easiest just to get you going.

There are 2 methods we’re going to discuss here:

1. In the job step you just created, you can just stack backup commands one after another. So the window that holds your backup code inside the step could easily hold the code for several databases. That would look something like this:

2. The other way you could do it is to add each one of your backup commands to their own step by repeating from step 3 above.

Now here’s a quick chart of some of the more common ways to create a backup routine with a short list of pros and cons. I’ll hash these out full in future articles.

Method

Pro

Con

SQL Server Maintenance Plan

Created in GUI and fairly easy to click your way through.

Not as flexible as some of the other options. Has historically suffered from misc. bugs.

Stored Procedure that cursors through databases

Extremely flexible and probably used most often.

Requires much deeper knowledge of T-SQL.

Single job

All backup code is contained in a single place.

Doesn’t scale well. It’s hard to do 100 databases this way. And depending on how you do it, if one backup fails the rest won’t run either so your entire server is at risk.

Multiple jobs

Each database backup is self-contained.

Doesn’t scale well. While you can just add another job if you have another database to backup, who wants 100 backup jobs on their server?

SSIS Package

Scales well and can contain complex workflow logic and error handling.

Requires a deeper knowledge of SSIS.

SSMS backup wizard

Easy to backup through the wizard

Doesn’t scale at all because it’s a completely manual process. Don’t use this one.

Here I’ve shown you how to create a simple backup job. Most tutorials will show you how to use the backup wizard, but that’s ridiculous because you’ll never do that in production. Nobody wants to manually backup their databases every day. So I’d rather show you something you can build from in later articles rather than have to switch paradigms on you.

MEMBER LOGIN:

BECOME A PETRI MEMBER:

About the Contributor

I’m a SQL Server MVP and have been involved with SQL Server since 1995. I’ve also been working with multi-terabyte, high-transaction DBs since before they were popular.

I’m a Contributing Editor and the sole database expert for InfoWorld Magazine which means I also touch DB2, Oracle, Sybase, and any other DB out there. I’ve also written for SQL Server Magazine, SQL Server Standard, SSWUG, and SQLServerCentral.com.

At the magazine I concentrate on benchmarking vendor products, reviews, and industry analysis.

I’m also the founder of MidnightDBA.com.

I’m a very prolific speaker and teacher in my community and my 3 main areas of teaching are backups, SSIS, and powershell, though I can teach on a myriad of subjects.