SQL Server Reference Guide

One of the most important things a DBA can do is to become familiar with regular backups of the systems they are responsible for. I’ve explained how database backups work, and I’ve also explained the impact the “recovery level” has on the backups. But as important as backups are, they are only half the story. Once the data is safe, at some point you may have to restore it either to the original system where it came from or on another system. So in this set of tutorials we’ll create a simple database, create a table or two and then back it up. We’ll not only back up the database but we’ll restore it as well, and watch the effects on the data as we do.

Although I’ll show you how to create a database to “play” with during this tutorial, it’s still important to do this on a testing system. It’s impossible for me to know your environment, and these steps might have unintended consequences. I highly doubt it, mind you, but you never know. Better safe than sorry!

Backup Locations

You can back up a database, log or database filegroup to three basic locations: tape, disk or a network share.

For these examples, I’ll show you how to back up a database to a file on the hard drive. To follow along, you’ll need to create a directory on your c:\ drive called TEMP. If you want to send the backup someplace else, just create whatever directory you want and change the parts of the scripts where I have C:\TEMP to your drive and directory.

You can also back up a database to a tape drive. For SQL Server, the first tape drive is called \\.\tape0, and then the next device is called \\.\tape1 and so on. The tape format used is the MTF — the Microsoft Tape Format — which it shares with Windows. So you could store the SQL Server backups along with the Windows backups. It’s beyond the scope of this tutorial to cover the various options you have here, and various hardware vendors might change those device names.

You can also back up the database to a network share. The important things to remember here are that the SQL Server services (the accounts you assigned to them) need to be able to access that share. 90% of the time when I’ve seen issues with network backups it’s because the service accounts don’t have access to the share. The other important thing to know about network backups is that they have a bandwidth impact, and they are kept to the speed of the data transfer. So if you try to back up a database across a really slow network, you’ll bury your bandwidth and it will take a long time.

Whether you back up to tape, disk or a share, think about the backup location as a big bucket. Unless you specify otherwise (we will in this tutorial) the backups “stack” on top of each other — the first backup will be numbered “1," the next “2” and so on. If you do have multiple backups in the “bucket," you’ll need to specify which one you want to restore. We’ll talk about this more as we move along.

SQL Server has the ability to create “Backup Devices." This is just a logical pointer to a tape device, disk location or network share. You can then send the backup to the Backup Device name, rather than typing in all of the parameters for the file or tape store. It’s a great way to simplify your commands, and it allows the backup scripts to be portable. You simply create a Backup Device, specifying the parameters. This example creates a disk device called DiskBackup that sends to a file on the hard drive called C:\TEMP\TEST.BAK:

USE master;
GO
EXEC sp_addumpdevice 'disk'
, 'DiskBackup'
, 'C:\TEMP\TEST.BAK';
GO

Then on another system you can create a Backup Device with the same name, but pointing to a different location. The backup script will work on both systems, since it refers to this logical name. For this tutorial, I’ll use actual file names to be very clear about where things are going. I’ll add a link at the bottom of this article that shows the full syntax for creating backup devices.

One final thing here — in many shops, the IT staff doesn’t use SQL Server backups at all. Instead they rely on third-party software that might use the SQL Server commands or actually hook into the binaries of SQL Server to backup and restore the databases and logs along with the Windows file system, Exchange, or other software all at once.

Oh, one more thing. Many times what I’ve done is to back up the databases and logs to disk on my system, and then pick up those backup files with my third party backup application. There are a couple reasons I do this. For one, I have lots of copies of my data that way. There’s the database, the logs, the database and log backups, and the tapes. Also, I have the database backups “close," so I can restore them if I have to without pulling down a tape from my offsite location. And — I’m kind of cheap. This way I don’t have to purchase the add-ins for the database and log backups from the third-party vendor.

Simple Database Backup and Restore

Let’s get started by creating a database, and ensuring that it is in the “Simple” recovery model. As you’ll recall from my earlier overviews, this mode means that the database transaction log sends the transactions on to the database as soon as it can, and once they make it to the database, they are erased from the transaction log. While this is, well, simple to administer, you can see that it might be fairly dangerous. The reason is that when the transactions are only in one place (the database, in this case) if you lose the system, you only have what was on the last backup.

Creating the database is pretty simple — we’ll just take all the defaults, which in effect just makes a copy of the “model” system database.

/* Create a database, set it to simple mode */
USE master;
GO
CREATE DATABASE BackupExample;
GO

Now we’ll set that database to the Simple recovery model with the ALTER DATABASE statement:

/* Set the database to Full Recovery */
USE master;
GO
ALTER DATABASE BackupExample 
SET RECOVERY SIMPLE;
GO

Now we’ll add a table and a single row of data:

/* Create a table, fill it with some data */
USE BackupExample;
GO
CREATE TABLE TestTable (SimpleText varchar(255));
GO
INSERT INTO TestTable VALUES ('Thing number one');
GO

Now we’ll take a “Full” backup. This instructs the system to copy all of the data from the database out to the location we specify — in this case, the “C:\TEMP” directory I mentioned. You can see the filename in the example code:

/* Take a Complete Backup */
BACKUP DATABASE BackupExample 
TO DISK = 'c:\temp\BackupExample.BAK'
WITH INIT;
GO

You can take a database backup while users are on the system. The performance is not affected very much, and the data is current as of the time the backup completes.

You’ll notice the WITH INIT parameter there at the end of the script. This tells the backup to erase the contents of the backup device — a file in this case. Unless you include this qualifier, the backup will be added to the “end” of the file — making it bigger, and making it a higher number backup than the last one. This is fine, if that’s what you want. In this case, we don’t.

So with that backup taken, let’s practice a restore operation. For this example we’ll restore the database back to the same system, and we’ll replace the one we have. In future tutorials I’ll show you how to move the database using a backup, or make a copy of one database to the same system, using a different database name.

To begin, we need to be in the “master” system database. This is different from the backup operation, because in a restore operation you need to have everyone out of the database until the restore operation is complete. This makes sense, since you’re sort of building the house they are standing in, so to speak.

Second, we issue the restore command with the parameter of “WITH REPLACE” to replace the current database that has the same name:

/* Restore a Complete Backup */
USE master;
GO
RESTORE DATABASE BackupExample
FROM DISK = 'c:\temp\BackupExample.BAK'
WITH REPLACE, RECOVERY;
GO

Notice also the “RECOVERY” parameter. That tells the restore operation to close the process and make the database ready for use by the users.

Full Recovery Model and Transaction Log Restore Example

Now we’ll go a step further. As you can see, a complete database restore over a database in the “Simple” recovery model is only able to replace the data using the last full backup. The data that comes in between the last backup and the time the database is restored is simply lost.

By setting the database to the “Full” recovery model, the log is not emptied when the piece of data makes it to the database — it is kept in the log until one of two things happen: a full database backup is taken, or a log backup is taken. In effect, you have the data on the system twice at all times.

Of course, this means that the data from the log eventually needs to get emptied or it will grow even larger than the database. Remember, the log records every addition, change or deletion, so it grows all the time.

So the process is this: you take a full backup regularly at some point, and then log backups after that on a more frequent basis. For instance, you could take a full backup once a day, and then log backups every hour or few hours. If you have a system failure, you restore the database full backup, then the first log backup, the second, and so on until the last log backup. Your data is then safe as of the last log backup. Let’s walk through an example to see how this works.

First, we’ll set that database to the “Full” recovery model:

/* Set the database to Full Recovery */
USE master;
GO
ALTER DATABASE BackupExample 
SET RECOVERY FULL;
GO

Remember, we only have one entry in the table so far. Let’s take another full backup:

/* Take a Complete Backup */
BACKUP DATABASE BackupExample 
TO DISK = 'c:\temp\BackupExample.BAK'
WITH INIT;
GO

Let’s assume that happened at midnight. Now the users come back to work, and add a single row of additional data:

/* Add some new data */
USE BackupExample;
GO
INSERT INTO TestTable VALUES ('Thing number Two');
GO

An hour has passed, so we take another backup, this time only of the log. This will be much smaller, and take almost no time at all:

/* Take a Log Backup */
BACKUP LOG BackupExample
TO DISK = 'c:\temp\BackupExample_Log1.BAK';
GO

Notice the file names and locations — they need to be different than the database backup.

Now, our users are back from their break, ready to do some more work. (OK, in real life there would be thousands or millions of new transactions, but let’s keep the typing to a minimum):

/* Add some more data */
INSERT INTO TestTable VALUES ('Thing number Three');
GO
And another hour has gone by. Let’s take another log backup, shall we?
/* Take a second Log Backup */
BACKUP LOG BackupExample
TO DISK = 'c:\temp\BackupExample_Log2.BAK';
GO

Now we have the following data in the backups:

1. Full database backup: Thing number One
2. First log backup: Thing number Two
3. Second log backup: Thing number Three

So, let’s simulate another disaster and see what we do with all this data. First, let’s restore the full backup, and then the first log only — remember, we have to be in the master database first:

 /* Restore the first log backup */
USE master;
GO
RESTORE DATABASE BackupExample
FROM DISK = 'c:\temp\BackupExample.BAK'
WITH REPLACE, NORECOVERY;
GO
RESTORE LOG BackupExample
FROM DISK = 'c:\temp\BackupExample_Log1.BAK'
WITH RECOVERY;
GO

Pay attention to the “WITH” parameters — in the first restore operation we use “NORECOVERY," telling the restore operation to “stay open” because there is more to come. In the second restore operation, this one for the log, we use the “RECOVERY” parameter to close the restore operation and let people back into the database.

Now let’s take a look at the data and see what we have restored:

/* View the data */
USE BackupExample;
GO
SELECT * FROM TestTable;
GO

And you get “Thing number One” and “Thing number Two," just as expected. Now let’s repeat the process, this time restoring everything all the way through the complete set of backups:

/* Restore the second Log Backup */
USE master;
GO
RESTORE DATABASE BackupExample
FROM DISK = 'c:\temp\BackupExample.BAK'
WITH REPLACE, NORECOVERY;
GO
RESTORE LOG BackupExample
FROM DISK = 'c:\temp\BackupExample_Log1.BAK'
WITH NORECOVERY;
GO
RESTORE LOG BackupExample
FROM DISK = 'c:\temp\BackupExample_Log2.BAK'
WITH RECOVERY;
GO
And now let’s see the data:
/* View the data */
USE BackupExample;
GO
SELECT * FROM TestTable;
GO

And of course it is all there. As you can see, you need the first full backup and all of the log backups to recover the data as completely as possible. In the next few tutorials we’ll take a look at backing up a single table, and also how to restore data to a certain point in time or another mark.

InformIT Articles and Sample Chapters

If you’re also in charge of Oracle systems, fear not. We have lots of information on backing that kind of data up in this sample chapter from the book Oracle DBA Automation Scripts.

Books and eBooks

We have even more maintenance information in the Adobe e-book called Microsoft SQL Server 2005 Unleashed. It's also available in a print version, and in online subscription form via Safari Books Online.

Online Resources

Microsoft has the syntax for adding a backup device here.