Restoring Databases

New in SQL Server 7.0

One difference between SQL Server 7.0 and previous SQL Server releases is that SQL Server 7.0 introduces some new backup and restore terminology. SQL Server 7.0 uses BACKUP instead of DUMP and RESTORE instead of LOAD. The new terminology is more familiar to nondatabase professionals, such as systems and network administrators, who are now serving as DBAs. But the new terms also better describe SQL Server 7.0's backup and restore processes.

In pre-SQL Server 7.0 releases, the backup process doesn't capture changes that users make to the data during the backup. The backup is essentially a snapshot of the database at the moment the DUMP begins. Thus, a LOAD statement returns the database to the state it was in when the DUMP statement began. With this backup process, whenever a user requests a data page, the backup process skips to that page, backs it up, then lets the user access the page. The backup process then returns to where it left off and continues its sequential reading of pages. When the backup process arrives at the page the user had requested, it skips that page because the page was already backed up.

SQL Server 7.0 introduces several changes to the backup process that helps speed up both backups and user access to data during backups. Unlike earlier releases, SQL Server 7.0 writes to the backup file or device any changes users make during the backup. The backup simply reads extents (a block of eight pages) sequentially and writes them to the backup. If a user requests a page for modification before the backup has reached that page, the backup picks up the new data. And if a user modifies a page that the backup process has already backed up, you can restore the page as the backup process captured it, then restore the user's changes from the transaction log. Thus, the SQL Server 7.0 backup process doesn't skip around or make users wait for data. And with all the pages and extents in order on the backup file, the restore process can quickly write the pages and extents back to the database with no jumping around the file. You can then restore the transaction log to apply any changes users made during the backup and roll back any incomplete transactions. The database is now in the exact state it was in when the backup completed. (For more information about the transaction log, see "The Transaction Log".

SQL Server 7.0 also introduces differential backups, which significantly speed up both the backup and restore process. And SQL Server 7.0 adds the RECOVERY option, which controls the point in the restore at which database recovery is performed, and the STANDBY option, which lets you maintain a warm backup server that users can query on a read-only basis.

New in SQL Server 2000

In addition, with SQL Server 2000, you can control the type of recovery model you want for each database. New options let you trade off performance and log-space requirements against your exposure to data loss. The more secure your data is against loss, the more complex the restore process and the more log space you'll need. SQL Server 2000 supports three recovery models: simple, full, and bulk-logged. (For detailed coverage of these models, see Kalen Delaney, Inside SQL Server, "Database Recovery Models," published in the June 2000 issue of SQL Server Magazine and available online. You can use the ALTER DATABASE statement to set these options, or you can set them from Enterprise Manager. To use the ALTER DATABASE statement to set the simple recovery option on Northwind, for example, you would run

ALTER DATABASE northwind
SET  recovery simple

The simple recovery option turns on the truncate log on checkpoint option, so your recovery options are to restore either your latest full backup or your latest differential backup. The primary advantage to the simple recovery option is that your log stays small. The full recovery option gives you all the recovery options, including the new ability to recover all operations, such as the bulk operations SELECT INTO, CREATE INDEX, and bulk data loading. However, because SQL Server 2000 logs these operations, your transaction logs will take up more space.

The bulk-logged recovery option sounds opposite of what it is. In bulk-logged recovery, SQL Server doesn't log the bulk operations as fully as it logs them under the full recovery model. SQL Server 2000 Books Online (BOL) says that "transaction log backups under this model capture both the log and the results \[italics added\] of any bulk operations." If you lose your data files and you've performed one of the bulk operations since the last log backup, you can’t back up the piece of the log on disk. Add to this the fact that you can’t perform a point-in-time restore if you select the bulk-logged recovery model, and you see that the log doesn't contain a complete record of each row you're inserting, including data, but rather a list of which rows were affected and no data. SQL Server stores the actual imported row data only in the database.

However, compared with the full recovery model, in which SQL Server fully logs every row affected by the bulk insert—including the data—in the transaction log, the bulk-logged option's log space requirements are far lower. So, despite the confusing name, bulk-logged recovery is worth considering. (I came to terms with the name by considering it to mean that SQL Server logs the data record inserts in bulk, rather than individually, during a bulk operation. Imagine logging the arrival of a truckload of beer instead of 20,000 cans one at a time.)

Note that Microsoft wants you to use ALTER DATABASE to set database options (including the recovery options), rather than the older sp_dboption command. In fact, sp_dboption doesn't support the SQL Server 2000 recovery options; you have to use ALTER DATABASE to set them. Unfortunately, sp_dboption neither sets the recovery options nor tells you the current value of the recovery option. ALTER DATABASE also doesn't tell you the current values of any of the database options. The best way to find the current setting for the recovery options, and many other database settings, is by using the SELECT DATABASEPROPERTYEX function. For example, to check the recovery options on Northwind, you can run

SELECT DATABASEPROPERTYEX ('Northwind', 'recovery')

Also new in SQL Server 2000 is the ability to restore up to a specific point in time by using log marks in the transaction log. This functionality complements the ability to do point-in-time restores.

Reasons to Restore

• A user database is damaged because of a hardware problem or a user error.
• The entire SQL Server, including the system databases, is lost or damaged.
• You want to maintain a backup or standby server by restoring backup files or tapes from the production server to a secondary server.

For example, you might need to restore a user database because of a disk crash, in which you lose the disk on which the data, or some part of the data, resides. Or perhaps someone, intentionally or otherwise, makes a change that corrupts the database—such as issuing a delete statement without a WHERE clause and wiping out an entire table.

The first step in all user database restores in which you no longer have the database is to back up the transaction log. In "Backup Strategies," I explain how to back up the transaction log and how this process doesn't work in SQL Server 7.0 when the primary file isn't available. I then pointed you to a workaround. SQL Server 2000 corrects the problem that SQL Server 7.0 has, but if you're using SQL Server 7.0 and haven't implemented the workaround, you can restore only up to your most recent log backup.

The last step in the restore is the recovery process. In "The Transaction Log", I explained how the recovery process searches the transaction log for committed transactions that the process can write to the database and for incomplete transactions that the process must roll back. You want to run the recovery process only after you've restored all your database and log backups. Suppose you have a full backup from Sunday and log backups from Monday, Tuesday, Wednesday, and Thursday. However, Tuesday's log backup includes an open transaction, or a transaction that was still in process. If you had restored the full database and the logs from Monday and Tuesday, then ran the recovery process, you'd have problems. When the recovery process saw the partially complete transaction, it would roll back any changes made to that point. Then, when you applied the log backup from Wednesday, the rest of the transaction would appear—except that the first part of the transaction would no longer exist. The solution is to not run the recovery process until you've restored everything.

If you run the restore from Enterprise Manager, you can view the different restore options by right-clicking the database, then selecting All Tasks, Restore Database. Figure 1 shows the resulting Restore dialog box. Next, click the Options tab, as Figure 2 shows. For the last restore in the sequence, select the option Leave database operational. No additional transaction logs can be restored, which instructs SQL Server to automatically run the recovery process. If you're restoring step by step and want to apply your backups one at a time, select the Leave database nonoperational but able to restore additional transaction logs option for each restore operation after which you don't want to run recovery.

If you run the restore from scripts, add the keywords WITH RECOVERY or WITH NORECOVERY, as appropriate, in your restore sequence. Besides forcing the recovery process, the WITH RECOVERY option makes the database available to users by removing the dbo use only database option. Restoring WITH RECOVERY means the same as Enterprise Manager's Leave the database operational option.

Restore Scenarios

Scenario 1. You run just full backups nightly because your database is small and you can recreate any changes users make during the day. You have the truncate log on checkpoint option set so that your transaction log doesn't fill up and so that you don't have to truncate the log when you do a full backup. In this scenario, all you must do to restore the database is restore the last full backup, then reapply the changes you've made since that backup. You select the recovery option Leave database operational. No additional transaction logs can be restored. If you run this restore from Query Analyzer, the syntax is

RESTORE DATABASE northwind
FROM nwindback WITH RECOVERY

Scenario 2. You have a small database that you back up nightly. However, you have some changes during the day that you don't want to recreate or that—like telephone orders—might be difficult to recreate. So, you schedule a full backup nightly, and you don't set the truncate log on checkpoint option. If a failure happens during the day, you're logging the incoming changes and can reapply them, but you truncate the log every night as part of the full backup job sequence so that the log doesn't fill up.

Your restore strategy in this case is to restore the full backup WITH NORECOVERY, then restore the log WITH RECOVERY. Enterprise Manager lets you use a two-step process in which you select the Leave database nonoperational but able to restore additional transaction logs option as you restore the database, then select the Leave database operational. No additional transaction logs can be restored option as you restore the log. Or, you can perform the restore all at once by selecting both the database and the log backups at the same time and selecting the Leave database operational. No additional transaction logs can be restored option. SQL Server will run the recovery process after it restores the log. The syntax for this restore is

RESTORE DATABASE northwind
FROM nwindback WITH NORECOVERY
RESTORE LOG northwind
FROM nwindlogback WITH RECOVERY

Scenario 3. You have a larger database for which you've scheduled a full backup Sunday and a transaction log backup nightly. Your restore plan would be to restore the full database, then use the WITH NORECOVERY option to restore all the logs except the last one. You would then restore the last log by using the WITH RECOVERY option:

RESTORE DATABASE northwind
FROM nwindback WITH NORECOVERY
RESTORE LOG northwind
FROM nwindlogback WITH FILE = 1, NORECOVERY
RESTORE LOG northwind
FROM nwindlogback WITH FILE = 2,  RECOVERY

Scenario 4. You have a large database with lots of activity. You run a full backup on the weekend, differential backups every night at 9:00 P.M., and transaction log backups every 3 hours during the day. Your database crashes Thursday at 11:15 A.M. In this situation, the differential backups save you a lot of time. Instead of having to apply multiple transaction log backups, you can just apply the full backup, then the differential backup from the previous night. Next, you apply the transaction logs up to (but not including) the last one by using WITH NORECOVERY. You then apply the final transaction log by using WITH RECOVERY. The combination of differential and transaction log backups gives you flexibility and lets you resume operations relatively quickly.

Restoring to a Standby Server

First, you must make sure that when you apply the backup to the standby server, you don't use WITH RECOVERY. Suppose a transaction is in progress on the production server when the backup ends. Part of the transaction would be on the current backup and the other part would be on the next backup. So, you don’t want to recover the standby server because that server would see a partially completed transaction and roll back the transaction. Then, when you moved the next backup to the standby server, you'd find the second part of a transaction that doesn't exist anymore.

Many organizations require that their standby server earn its keep while waiting to take over from the primary server. Data analysts can query this system instead of the production server, which makes the analysts’ queries run faster and keeps the data entry staff on the production server happy because they aren't competing with complex, long-running queries. To let users run such queries, SQL Server rolls back any uncommitted transactions, placing the database in a consistent state. As SQL Server performs the rollback, it saves the changed pages. SQL Server then reapplies these changes just before the next restore. SQL Server can allow these changes by creating an undo file, which is really a little transaction log. So, when you ship the next backup to the standby server, SQL Server can undo the changes and apply the backup.

If you implement a standby server and use Enterprise Manager for restores, you'll run into the third option on the Restore dialog box, which Figure 2 shows. This option—Leave database read-only and able to restore additional transaction logs—lets you specify the location and name of the undo file. If you're using T-SQL to run your restores, your code will look like this:

RESTORE DATABASE northwindtest FROM NwindBack
WITH MOVE 'Northwind' TO 'd:\mssql\data\Northwindtest.mdf',
MOVE 'Northwind_log' TO 'd:\mssql\data\Northwindtest.ldf',
STANDBY = 'd:\mssql\backup\Undo_nwind.dat'

This example creates a copy of the Northwind database full backup on device Nwindback. I call the copy Northwindtest. The MOVE option instructs SQL Server to create the copy from the Northwind backup. After SQL Server makes the copy, I can apply future backups to Northwindtest without using the MOVE option.

The standby server configuration sounds great, and as long as your primary server doesn't crash, users can use this read-only server for data analysis. But when you lose the database on the main server and want to switch to the standby server, you have to jump through hoops. Here’s what you have to do to switch to the standby server:

• Back up the log on the primary server (if you can).
• Take the primary server offline.
• Apply the log backup to the standby server, along with any other backups that SQL Server hasn't applied yet, using the WITH RECOVERY option on the last log restore. The standby server is now your primary server.
• Change the name of the standby server to that of the primary server that you just took offline. This name change is necessary so that user applications can still connect to the database. You may also have to change the IP address, depending on whether your network uses fixed IP addresses for its servers.

However, this official plan has a big drawback. At installation, SQL Server stores a checksum, which includes the name of the server. On startup, SQL Server checks this value against a newly computed checksum to make sure critical files are present and not corrupted. After you give the standby server the primary server's name, however, SQL Server won't start. The workaround is to do a quick reinstall of SQL Server, which doesn't really reinstall the database system but does fix the checksum. You also have to consider the server name as stored in the sysservers system table. Processes such as replication that depend on this server name will fail if you use a new server name because the old server name is still registered in sysservers. To register the new server, after you start the new server, run sp_dropserver , sp_addserver , local, and restart your MSSQLServer service.

Suppose you just get the standby server up and running as the production server when the technicians call to say they've fixed the primary server. You have to swap the servers again; here's how:

• Stop the users from making changes to the standby server that is currently acting as the production server (make the server dbo use only).
• Back up the transaction log, assuming that you performed regular log backups on the standby server while it was the production server. If you didn't perform regular log backups, you need to perform a full backup.
• Take the standby server, which is acting as the production server, offline and change its name and possibly its IP address back to the original values.
• Apply all your backups to the new production server—that is, your repaired primary server—and place that server online. With the original production server back online, you can reapply your backups in STANDBY mode to the standby server to reestablish it as the standby server.

This complicated switching process is enough to make you seriously consider clustering or at least at look at using replication instead of a backup and restore sequence to maintain your standby servers.

Filegroup and Point-in-Time Restores

You can restore a filegroup from a filegroup backup or from a full backup, as Figure 3 shows. After restoring the filegroup WITH NORECOVERY, you apply the transaction log backups and use WITH RECOVERY on the last one. SQL Server searches the log and applies only the transactions for this filegroup. The T-SQL commands to restore the secondary filegroup from a full backup set called nwindback would look like this:

RESTORE DATABASE Northwind 
   FILE = 'Northwind2',
   FILEGROUP = 'secondary'
   FROM nwindback
   WITH NORECOVERY
GO
RESTORE LOG Northwind
   FROM Nwindlogback
   WITH RECOVERY

If you know exactly when the problem hit your data, you can restore to just before that point. From Enterprise Manager, you simply select the check box for point-in-time restore, then specify the time you want to restore to. From Query Analyzer, you would specify the time at which to stop the restore:

RESTORE DATABASE northwind
FROM nwindback WITH NORECOVERY
RESTORE LOG northwind
FROM nwindlogback WITH RECOVERY  ,
STOPAT = 'Sep 9, 2000 10:15 AM'

Practice Makes Perfect