Wednesday, November 30, 2011
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.
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 databaseName
TO DISK = 'file location'
OK, let me explain a couple of these items.
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 Model
TO DISK = 'c:\ModelBackup.bak'
backup database Model
To disk = 'c:\ModelBackup.bak'BACKUP DATABASE MODEL
To 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…’
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’.
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.
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.
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.
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.
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.
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
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.
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.
BACKUP Database Model
To disk = ‘c:\ModelBackup.bak’
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.
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.
