Express Essentials: Restoring Your Databases

Although backups are an essential part of data protection, as I explained in "Backing Up Your Databases" at http://www.sqlmag.com/articles/index.cfm?articleid=98130, they are only half the story. The other half lies in the ability to restore the backups you've made--after all, what good is a backup if you can’t restore it?

If you’re doing simple full backups every night, the restore process is quite straightforward. You just use the T-SQL RESTORE DATABASE command with your last full backup to perform the restore. When the restore is finished, you’re good to go. For example, to restore a backup of the Northwind database, you'd use the code

RESTORE DATABASE NorthwindFROM DISK = 'C:BACKUPSNorthwind_Full.bak'WITH RECOVERY, REPLACE

where C:BACKUPSNorthwind_Full.bak is the path to your most recent full backup of the database. Remember that the RESTORE DATABASE command returns your database to the state it was in at the time you performed the backup that you're restoring; any subsequent activity will need to be redone. If you’re running Windows Vista, you must also remember to use the Run as Administrator option when you do the restore so that you have the authority necessary to restore the database.

If you use transaction-log backups, you'll lose less data, of course, but the restore process is a bit more involved. First you need to restore the last full database backup. However, because you’ll subsequently restore transaction logs, you must use the RESTORE DATABASE command's NORECOVERY option:

RESTORE DATABASE NorthwindFROM DISK = 'C:BACKUPSNorthwind_Full.bak'  WITH NORECOVERY, REPLACE

The NORECOVERY clause tells SQL Server Express that you'll be performing subsequent restore operations. SQL Server Express does not allow the restored database to be used until you've performed the final restore, which you indicate by using the RECOVERY clause.

After you've restored your database backup, you’ll restore your transaction-log backups one at a time, in the order in which they were created. For all but the last transaction log, use the RESTORE LOG command with the NORECOVERY option:

RESTORE LOG NorthwindFROM DISK = 'C:BACKUPSNorthwind_Log1.bak'WITH NORECOVERY

When you restore the final transaction log, use the WITH RECOVERY option so that the database will be usable after you complete the restore process:

RESTORE LOG NorthwindFROM DISK = 'C:BACKUPSNorthwind_Log2.bak'WITH RECOVERY

This code assumes that you've performed transaction-log backups of the Northwind database to the files C:BACKUPSNorthwind_Log1.bak and C:BACKUPSNorthwind_Log2.bak. Restoring transaction logs returns your database to the state it was in when you backed up the final transaction log.

When discussing database backup and restore, it's important to remember that if your database backup contains sensitive data, you need to keep the backup in a secure location. Also, if you’re backing up to disk to protect against disk failure, save your backup to a drive other than the one that holds your SQL Server Express data and log files.