Section 19.6. Restore and Recovery | Backup & Recovery: Inexpensive Backup Solutions for Open Systems

19.6. Restore and Recovery

In SQL Server terminology, a database restore and a recovery represent two different operations. A restore is the act of copying data from a backup, applying the logged (committed) transactions to roll the database forward to the specified recovery point desired, and rolling back any uncommitted transactions that were found in the transaction log. Recovery happens when the database starts. It rolls back any uncommitted transactions so that the database starts in a consistent state. If the recovery process is unable to return the database to a consistent state, a restore may be required.

Just as there were a great many choices in deciding what and how to back up, equal consideration must be given as to how you will perform your restore. Your restore will usually be dictated by the last backup you did. For instance, the best-case scenario is that you just completed a full backup and now need to do a full recovery. This simply involves the last full backup without the need to apply differential or transaction log backups. It is easy to see how the number of choices can quickly increase. You could do a full, a full with a differential, a full with transaction logs, or a full with a differential and transaction logs. Which type of restore you need to do involves careful consideration of how much data you need to restore and what current backups are available.

19.6.1. Components of a Restore

Various components make up the restore. These include the rollforward set, restore sequences, and backup recovery.

Rollforward set: The rollforward set consists of all the data that is to be restored, regardless of whether the data is from a full, partial, or other type of backup.
Restore sequence: A restore sequence is defined as the group of backups that is restored to bring the database online and to a consistent state. These backups are restored, in the proper sequence, to accomplish this.
Backup recovery: The actual restore process has three phases, including data copy, redo, and undo. The data copy phase copies all the data, logs and index pages from the backup to the database file. In the redo phase, also called the rollforward phase, the logged transactions are applied to roll forward the data to the specified recovery point. After this phase, the database is generally in an inconsistent state, needing the undo phase in order to complete. In the undo phase, which is where the backup recovery process starts, any uncommitted transactions are rolled back, bringing the database to a consistent, usable state. Note that the undo phase isn't always necessary if the rollforward operation returns the database to a usable state. During this process, different safety checks are performed by the database engine. These checks prevent overwriting existing databases or existing datafiles.

19.6.2. Recovery Roadmap

The process of restoring or recovering your server can be difficult and confusing. Trying to determine where to begin is probably the biggest hurdle to overcome. This roadmap walks you through the basic steps involved in trying to get your server running again. The restore and recovery process makes no assumptions about what the failure was or the current state of the database.

Before jumping right in and trying to recover the database from the last backup you have, it is always a good idea to ensure that the database is really in a corrupt or unusable state. There are several ways to confirm this. After these first steps have been completed, move on to the actual restore and recovery.

19.6.2.1. Step 1: Check for obvious hardware errors or server problems

While this category is too broad to cover in depth, it is always best to ensure that the server is in proper working order. This means all hardware is working and, if you need to reinstall the OS, that all patches are applied.

19.6.2.2. Step 2: Can you connect to the instance using a GUI or T-SQL?

Your first step in locating the problem should be the basic sanity test of connectivity. This will help you narrow down any major problems by allowing you to do the additional tests in the following steps. If you can't connect, see if the instance is running, and if it isn't, start it. If there are no obvious reasons why the instance won't start, begin your troubleshooting with the event log and any logfiles. After you can connect to the instance, proceed to Step 3.

19.6.2.3. Step 3: Can you connect to the master database?

After you're sure the instance is running, the next step is to verify that you can connect to the master database. Remember that no other databases can run without the master! If you can't connect to the master, follow the steps outlined in the master database repair section. After you can connect to the master, proceed to Step 4.

19.6.2.4. Step 4: Can you connect to a specific, nonsystem database?

Now we are getting to the meat of the problem. If you're at this step, you know your hardware and OS are good, your instance is running, and the master database is operating normally. If you can connect to a nonsystem database, you're done! However, it probably won't be this easy. The first step is to check to see the status of the database. This procedure is accomplished with the following command:

select databasepropertyex('Inventory', 'Status');

This returns a value that represents the current status of the database. In this example, the possible values are online, offline, restoring, recovering, suspect, and emergency. These values provide a quick glimpse of the status of the database. The level of effort involved in getting your database running will be directly related to this result. For instance, if the status is offline, it might be as simple of starting the database. However, if the level is suspect or emergency, it could take a lot more effort to determine the cause of the problem. After you've determined the status, continue to Step 5 to start the troubleshooting process.

19.6.2.5. Step 5: Initial checks

The first steps in locating the problem are to look for obvious errors in the event log and any other error logs. Browse the event log for specific errors that would lead you to the root of the problem. Also be sure to check the logs generated by the agent process, especially the error logs and the alerts.

Another option here is to run dbcc with the checkdb command or one of the other check commands:

dbcc checkdb ('Inventory');

If the dbcc utility reports any errors, start with this as a base to getting your database running. There are many different options for checking objects with dbcc. Entire databases or even tables can be checked, as was just demonstrated, with checktable. Other objects can be checked with checkalloc and checkcatalog, which check the consistency of disk space allocation and catalog consistency, respectively. These commands should hopefully lead you to one of the other steps that will allow you to repair or restore your database. After this step, proceed to Step 6 if your database is still not working.

19.6.2.6. Step 6: Are any datafiles missing?

This is a fairly straightforward test. Try to start the database using your preferred method. If you see an error such as the one in Figure 19-8, which can be seen when you're trying to start the database from the GUI, you'll know that there is a missing file.

Figure 19-8. Missing datafile

From T-SQL, you would receive a similar error after issuing the following command:

alter database Inventory set online; Msg 5120, Level 16, State 101, Line 1 Unable to open the physical file "C:\Program Files\Microsoft  SQL Server\MSSQL.1\MSSQL\DATA\Inventory.mdf".  Operating system error 2: "2(The system cannot find the file specified.)". Msg 945, Level 14, State 2, Line 1 Database 'Inventory' cannot be opened due to inaccessible files  or insufficient memory or disk space.  See the SQL Server errorlog for details. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.

You can try to restore this datafile to bring the database back online. However, keep in mind that this may or may not bring your database to the most recent point. You may still need to perform additional restores of incremental or transaction logs. If restoring the datafile does not render your database operable, proceed to Step 7.

19.6.2.7. Step 7: Is the transaction log full?

If you have a database with a high level of activity, the transaction log might be full. It is also possible that the log is full because either your disk is full or you don't have auto-growth enabled on that logfile. Make sure before you begin any of these fixes that you make a backup. This will ensure that, no matter how wrong the restore goes, you'll always be able to revert back to the last known state. Yes, this state is the failed state, but at least you'll be able to begin again.

If the disk has filled up, you can try to gain a temporary reprieve by truncating the transaction log, doing a backup, or freeing space on the drive by removing unnecessary space abusers. However, this will likely buy you only a short amount of time. The long-term solution is to move the transaction log to a new disk.

If the disk hasn't filled up, but the transaction log has filled up, you can try to enable auto-growth if it is not enabled. If it is enabled, it may be necessary to add another transaction logfile. Steps to see if the transaction log is full are outlined in the section "Transaction Log," earlier in this chapter. If this isn't the reason for your database failure, continue your quest at Step 8.

19.6.2.8. Step 8: Is it possible to repair the DB?

One possible repair alternative is to use dbcc with the repair option. While I mention it here, I won't go into detail because this method often results in data loss (though not always). Consult the dbcc reference page for specifics on this method. Proceed to Step 9.

19.6.2.9. Step 9: Before you begin the restore process

The most important thing before you begin the restore process it to determine what recovery model is in use. This setting directly impacts the type of restore you'll do. Consult the section "Recovery Models" earlier in this chapter for directions on how to determine the current recovery model.

No matter which recovery model is in place, always take a backup of your existing system. This ensures that if something goes wrong with the restore, you can at least get back to the point where you began.

If the recovery model is simple, proceed to Step 10.

If the recovery model is full or bulk logged, proceed to Step 11.

19.6.2.10. Step 10: Restore under the simple recovery model

A restore under the simple recovery model is quite, how shall we say, simplehence the name. Since it doesn't deal with transaction logs, it is straightforward. Restore the last full backup and any differential backups, and you should be finished!

19.6.2.11. Step 11: Restore under the full or bulk logged recovery model

Under the full or bulk logged recovery model, restores can be slightly more complex than in the simple recovery model due to the fact that you may also need to apply transaction logs. Here are the steps:

Restore the last full backup.
If you have differential database backups, restore them too.
Restore and replay the transaction logs.

Back up both the database and the transaction logs before your restore!

19.6.3. Database Restore

This section covers how to do a restore of the full backup created in the section "Backup" earlier in this chapter. This procedure outlines the steps necessary for a restore in 2005 Management Studio:

Open the SQL Server Management Studio, connect to the instance where the database is located, and expand Databases.
Select the database to restore from, right-click, expand Tasks, select Restore, and then select Database.
On the General page, select the database you wish to restore to. If the database does not exist, type in a name for the new database.
Select the point in time you wish to use. Unless you need a specific previous date, Most Recent Possible (the default) should suffice.
Specify the source location of the restore, specifying either a database or a device.
Select the backup sets to restore, and click OK.
Specify options on the Options page:
1. Specify whether the restore should overwrite the existing database.
2. Specify the recovery state desired after the restore. The recovery state you select depends on any operations that need to be performed on the restored database after the restore is complete. If your backup contains all the information you need to do a full restore, select Leave The Database Ready To Use. If, however, you need to do more operations on the database before bringing it online for others to use, select one of the other two options: Leave Database Non-Operational or Leave Database In Read-Only Mode. The first, Leave Database Non-Operational, is akin to a manual restore. It restores any backups selectedfull, differential, or transaction logsand then leaves itself nonoperational so that more work can be done. This can include applying more transactions or other related restores. Leave Database In Read-Only Mode is similar to nonoperational mode except that after the restore is complete, the database is brought up in read-only mode to allow additional transaction logs to be applied. In this mode, you can run checks on the database and verify the data

The Restore Database window is shown in Figure 19-9. The procedure is essentially the same between 2005 and 2000 with only a few of the options having different labels.

Figure 19-9. Restore Database window

This example was an illustration of the most basic of full restores. Depending on your backup plan, it may, of course, be necessary to restore differential backups and transaction logs.

19.6.3.1. Command-line restore with Transact-SQL

As with backup, you can do a restore from Transact-SQL using the restore database command. The following Transact-SQL command restores the Inventory database:

restore database Inventory from disk = 'E:\Backups\cmd_Inventory.bak'

It is also possible to restore individual files and filegroups that you have backed up. If you have designated objects to span more than one filegroup, you must restore every file that that object touches.

19.6.4. Master Database Restore

Because the master database contains the configuration information for all the other databases, as well as the configuration information for the server, it is the most important database to back up and be able to restore. Unfortunately, it is not always easy to determine whether the master database is the problem. After all, if you can't connect to any instance, how can you check to see whether the database is OK? There are two ways to recover from this type of problem.

If SQL Server can start, you can connect and restore the master database. This restore process is just like the restore for any other database. Keep in mind that this can only be a full restore because that is the only method supported by the master database. Also note that since differential or transaction logs are not able to be restored, the master database may not be in the exact state as when the database failed. To remedy this, manually apply any changes made since the last full backup.

If SQL Server can't start, you can rerun the SQL Server Setup program and have it rebuild the master database. Afterward, you can restore from your latest backup. As in the previous example, you may need to do some manual updates to get the database to the state before the failure. The Rebuild Master option in the Setup program also rebuilds the msdb and model databases.

BackupCentral.com has a wiki page for every chapter in this book. Read or contribute updated information about this chapter at http://www.backupcentral.com.