Recovering a Database

In the previous section, you performed different kinds of backups but considered the recovery process only in theory. Now you will see how to restore a database in different recovery scenarios.

Retrieving Backup Information

Before you can start restoring databases, you need to know which backups you need to restore. SQL Server stores backup history about every single backup performed on databases in the msdb database. The msdb database can be queried to find the backups to restore.

Creating Simple Backup Information

1.	From the Start Menu, select All Programs | Microsoft SQL Server 2005 | SQL Server Management Studio.
2.	In the Connect to Server dialog box, click the Connect button.
3.	From the Standard toolbar, click the New Query button to open a New Query window.
4.	Write and execute the following BACKUP statements to perform a full and a differential backup of AdventureWorks database. ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE; --Perform Full Database Backup BACKUP DATABASE AdventureWorks TO DISK = 'T:\BACKUPS\ADVFULL.BAK' WITH INIT; --Perform a differential Backup BACKUP DATABASE AdventureWorks TO DISK = 'T:\BACKUPS\ADVDIFF.BAK' WITH INIT,Differential;

Retrieving Simple Backup Information

1.	To get information about which backups have occurred on the AdventureWorks database, execute the following SELECT statement: USE msdb GO SELECT backup_start_date,type, physical_device_name,backup_set_id FROM backupset bs inner join backupmediafamily bm ON bs.media_set_id = bm.media_set_id WHERE database_name ='AdventureWorks' ORDER BY backup_start_date desc The results pane shown below indicates that the most recent backup was of backup type I, a differential backup. As you know, to restore a differential backup, you first need to restore the most recent full database backup. This backup can be found on line 2, with type D indicating a full database backup.
2.	Every backup gets a unique id called a backup set id that can be seen in the results window, as shown below: Using the above information, you can find out which data and log files were affected by the backup and what their original locations were. The following query gets this information for backup set id 62. When running this query, be sure to change the backup_set_id from 62 to the backup set id of the full database backup found in the previous step. SELECT filegroup_name,logical_name,physical_name FROM msdb..backupfile WHERE backup_set_id = 62 --change to your backup_set_id
3.	In some situations, the msdb database doesn't have the backup history information you need. This can happen if the msdb database was destroyed by a disaster or if the backup was taken on another system. In these cases, it is only possible to get the information directly from the backup device. To get information about backups that reside on 'T:\BACKUPS\ADVFULL.BAK', type and execute the following statement. RESTORE HEADERONLY FROM DISK='T:\BACKUPS\ADVFULL.BAK'
4.	To get information about the data and log files affected by the backups stored on the device, execute the RESTORE FILELISTONLY statement. RESTORE FILELISTONLY FROM DISK ='T:\BACKUPS\ADVFULL.BAK'

Restoring a Database Using SQL Server Management Studio

In many cases, the easiest way to restore a database is to use SQL Server Management Studio. SQL Server Management Studio uses the backup history stored in the msdb database to show us the best way to restore a database.

Performing a Restore Using SQL Server Management Studio

1.	Execute the following statements in the query window of SQL Server Management Studio to simulate a scenario in which you have the AdventureWorks database in a simple recovery strategy using full database and differential backups. Change the paths of the backup devices as needed. ALTER DATABASE AdventureWorks SET RECOVERY SIMPLE; --Perform Full Database Backup BACKUP DATABASE AdventureWorks TO DISK = 'T:\BACKUPS\ADVFULL.BAK' WITH INIT; --Simulate a transaction UPDATE AdventureWorks.Person.Contact SET EmailAddress = '[e-mail]' WHERE ContactID=3; --Perform a differential Backup BACKUP DATABASE AdventureWorks TO DISK = 'T:\BACKUPS\ADVDIFF.BAK' WITH INIT,Differential;
2.	To restore the database, open Object Explorer by selecting Object Explorer in the View menu or by pressing the F8 key.
3.	In the tree view shown, expand your SQL Server instance, open the Databases folder and right-click the AdventureWorks database. In the context menu, select Tasks | Restore | Database.
4.	The Restore Database dialog box opens and you will see that the most recent backup sets are already selected for restoration, as shown in Figure 3-4. To complete the restore, simply click the OK button. Figure 3-4. The Restore Database dialog box. Note Make sure no connections are open to AdventureWorks because no connections to the database are allowed while a restore is in progress.
5.	A message box should appear to inform you that the restore was successful.
6.	Open a New Query window and check that both backups are restored successfully by typing and executing the following code. USE AdventureWorks GO SELECT EmailAddress FROM Person.Contact WHERE ContactID =3; The results should show [e-mail].

Note

As you can see, it is not nessecary to drop the database you want to restore first. The restore process automatically drops the database as a first step.

Restoring a Database from a Simple Backup Strategy Using T-SQL

Imagine you have performed the same backups as in the previous example. To restore the database using T-SQL, you use the RESTORE DATABASE statement. This statement has a similar syntax to the BACKUP statement. You will need to provide the database name and the location of the backup device.

Restoring a Full Database Backup Using T-SQL

1.	Within SQL Server Management Studio, open a New Query window.
2.	Type and execute the following RESTORE DATABASE statement to restore the AdventureWorks database. Again, be sure you have no other connections open to the database when executing this query. USE MASTER GO RESTORE DATABASE AdventureWorks FROM DISK='T:\BACKUPS\ADVFULL.BAK'
3.	Execute the following query. The result should now show [e-mail] because the update occurred after the full database backup. SELECT EmailAddress FROM AdventureWorks.Person.Contact WHERE ContactID = 3

In this procedure, you only restored the full database backup. After the restore, the database was brought online automatically by performing a recovery. If you want to apply the differential backup as well, you need to tell SQL Server not to bring the database online after the full database restore because that would make it impossible to restore the differential backup. This is done by using the NORECOVERY option. The NORECOVERY option must be used with every RESTORE statement except the last to be performed. In the following example, you will make use of the NORECOVERY option to restore the full and the differential backups you have previously performed.

Restoring Differential Backups Using T-SQL

1.	In SQL Server Management Studio, open a New Query window.
2.	Type and execute the following RESTORE DATABASE statement to restore the AdventureWorks database with the NORECOVERY option. USE MASTER GO RESTORE DATABASE AdventureWorks FROM DISK='T:\BACKUPS\ADVFULL.BAK' WITH NORECOVERY
3.	Type and execute the following RESTORE DATABASE statement to restore the differential backup. USE MASTER GO RESTORE DATABASE AdventureWorks FROM DISK='T:\BACKUPS\ADVDIFF.BAK'
4.	Execute the following query. The result should be [e-mail]. Because this update occurred between full and differential backups, you can be sure that the differential backup was applied succesfully. SELECT EmailAddress FROM AdventureWorks.Person.Contact WHERE ContactID = 3

Restoring a Database from a Full Backup Strategy Using T-SQL

In a full backup strategy, you have a combination of full and transaction log backups. Now you will see how to restore them using T-SQL queries.

Restoring a Combination of Full and Transaction Log Backups

1.	Execute the following code to create backups of the AdventureWorks database. This code will also update some data, which you can use to check that your restore was effective: ALTER DATABASE AdventureWorks SET RECOVERY FULL; --Perform Full Database Backup BACKUP DATABASE AdventureWorks TO DISK = 'T:\BACKUPS\ADVFULL.BAK' WITH INIT; --Simulate a transaction UPDATE AdventureWorks.Person.Contact SET EmailAddress = '[e-mail]' WHERE ContactID=3; --Perform a Transaction Log Backup BACKUP LOG AdventureWorks TO DISK = 'T:\BACKUPS\ADVLOG1.BAK' WITH INIT; --Simulate a transaction UPDATE AdventureWorks.Person.Contact SET EmailAddress = '[e-mail]' WHERE ContactID=3;
2.	Now imagine that the data file of the AdventureWorks database gets corrupted. As discussed before, it is still possible to perform a transaction log backup to capture the tail of the log that contains the transactions completed after our most recent transaction log backup. This must be done using a special option called NO_TRUNCATE. --Perform a Transaction Log Backup of the tail of the log BACKUP LOG AdventureWorks TO DISK = 'T:\BACKUPS\ADVLOG2.BAK' WITH INIT, NO_TRUNCATE; Note When the transaction log gets corrupted, it is no longer possible to perform this sort of backup. In this case, only the backups already performed can be restored.
3.	After the backup of the log has been performed, the RESTORE statement can be used to start restoring first the full database backup and then the two transaction log backups. As with differential backups, the NORECOVERY option has to be used for all restores except the last. The transaction logs are restored using the RESTORE LOG statement. -- Switch to the master db USE master GO -- Restore the full database backup RESTORE DATABASE AdventureWorks FROM DISK = 'T:\BACKUPS\ADVFULL.BAK' WITH REPLACE, NORECOVERY; --Restore the first Transaction Log Backup RESTORE LOG AdventureWorks FROM DISK = 'T:\BACKUPS\ADVLOG1.BAK' WITH NORECOVERY; --Restore the second Transaction Log Backup RESTORE LOG AdventureWorks FROM DISK = 'T:\BACKUPS\ADVLOG2.BAK'; The REPLACE option in the RESTORE DATABASE statement directs SQL Server to skip its safety checks and replace the database without any questions.
4.	Execute the following query. The result should be [e-mail], which shows that all transactions were successfully applied. SELECT EmailAddress FROM AdventureWorks.Person.Contact WHERE ContactID = 3

Restoring System Databases

The system databases master, msdb, and model are the heart of a SQL Server installation. Without the system databases, SQL Server will not function properly or at all if the master database is corrupt. Therefore, it is extremely important to have backups of these databases in order to be prepared for a system failure. System databases are normally backed up using a simple model with full database backups performed on a regular schedule. This is an effective strategy because the informations inside these tables doesn't change often. Nevertheless, after making major changes to your system, including creating databases, logins, or changing configuration options, additional backups of the system databases should be made.

Database backups and restores in SQL Server are performed online. Therefore, SQL Server has to be in a running state before the system databases can be restored. There are two possibles ways of getting SQL Server up and running:

• If the databases are corrupt but the binaries (compiled computer programs, or executables) are not affected, the system databases can be rebuilt using the SQL Server Setup program. Instructions for this task can be found in the SQL Server Books Online topic "How to: Install SQL Server 2005 from Command Prompt."

• If the whole system is corrupt, install the system from scratch with the Setup program. Also, all Service Packs and Patches that were on the system prior to its failure need to be reapplied.

Now SQL Server should be up and running, but it still lacks information about the user databases, logins, jobs, alerts, and configurations that the system had before. To rectify this situation, the system databases must be restored. The process of restoring the system databases always starts with restoring the master database. To restore the master database, use the special procedure detailed below.

Important

Practice this procedure only on a test system. If anything goes wrong, your data can be lost! This example assumes that SQL Server is installed as the default instance.

Restoring the Master Database

1.	Perform backups of master, msdb, and model databases. --MASTER DATABASE BACKUP DATABASE MASTER TO DISK = 'T:\BACKUPS\master.bak' WITH INIT --MSDB DATABASE BACKUP DATABASE MSDB TO DISK = 'T:\BACKUPS\msdb.bak' WITH INIT --MODEL DATABASE BACKUP DATABASE MODEL TO DISK = 'T:\BACKUPS\model.bak' WITH INIT
2.	Close SQL Server Management Studio and any other programs that have a connection to your SQL Server.
3.	From the Start menu, select All Programs | Microsoft SQL Server 2005 | Configuration Tools | SQL Server Configuration Manager.
4.	Within SQL Server Configuration Manager, click on SQL Server 2005 Services in the lefthand pane. This will display all current services in the righthand pane. Stop any services that are running by right-clicking on them individually and clicking the Stop item on the context menu. Note which services you have stopped; you will want to start them again later. Once all services are stopped, your screen should look like this:
5.	Start a Command Prompt by clicking Start | Run, typing cmd, and clicking the OK button.
6.	Change the directory to the binn folder of your SQL Server installation by using the following statement. This statement provides the default path used by SQL Server setup. Type the statement on a single line (it is shown broken here to fit on the printed page) and press the Enter key. C:\Documents and Settings\Administrator>cd "\Program Files\ Microsoft SQL Server\MSSQL.1\MSSQL\binn"
7.	Start SQL Server in single user admin mode by providing the <;$MI>m option as demonstrated by the following statement. Enter the statement and press the Enter key. C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn>sqlservr -m
8.	Leave this Command Prompt open and start a second one in the same manner as step 5 above.
9.	Type sqlcmd E into this Command prompt to connect to SQL Server.
10.	Perform the restore of the master database with sqlcmd by typing the following T-SQL statement. When entering the statement, press the Enter key to add each new line. Once you type in the line GO and press the Enter key, the full statement will be executed. RESTORE DATABASE master FROM DISK ='T:\BACKUPS\master.bak'; GO The results will look like this:
11.	Switch to the first Command Prompt. SQL Server issued a shutdown automatically. Both command prompts can now be closed.
12.	Switch to the SQL Server Configuration Manager and start the SQL Server (MSSQLSERVER) service only.

Restoring the Msdb and Model Databases

1.	Open SQL Server Management Studio. The msdb and model databases can be restored like any other database. Because no connection to the databases is allowed while restoring, you did not start the other services in the previous procedure.
2.	Open a New Query window and execute the following RESTORE DATABASE statements to recover the msdb and model databases. --MSDB DATABASE RESTORE DATABASE MSDB FROM DISK = 'T:\BACKUPS\msdb.bak' --MODEL DATABASE RESTORE DATABASE MODEL FROM DISK = 'T:\BACKUPS\model.bak' WITH REPLACE
3.	Switch to the SQL Server Configuration Manager and start all the other services that you shut down in step 4 of the previous section. These probably include SQL Server Intergration Services, SQL Server FullText Search (MSSQLSERVER), and SQL Server (SQLEXPRESS).