Restoring

The whole point of backing up your databases is to be able to restore them and be a hero when disaster strikes. At that point, you can say, 'This is why you pay me.' You may need to restore your database due to any of the following causes:

• Hardware failure

• Corrupt master database

• User enters bad data that needs to be rolled back

• Keeping a standby server

Caution 

You cannot restore a database from a SQL Server 2000 machine on a SQL Server 7.0 machine. You can, on the other hand, restore a SQL Server 7.0 database on a SQL Server 2000 machine.

Once you're ready to restore your database, you need to ensure that all the users except you are disconnected from the database. If SQL Server can't obtain exclusive access to the database, you'll receive the following error when trying to restore the database:

Server: Msg 3101, Level 16, State 1, Line 1 Exclusive access could not be obtained because the database is in use. Server: Msg 3013, Level 16, State 1, Line 1 RESTORE LOG is terminating abnormally.

Tip 

In Chapter 7, I discussed how to quickly disconnect the users before a database restore. You can download the script usp_Killusers from http://www.sqlservercentral.com/experienceddba/.

Full Database Restores

The first step in any type of database restore is to restore the base database through a full database restore. This brings the database up to the point of the last full backup. To restore a database in Enterprise Manager, right-click on the database and select All Tasks | Restore Database. This opens the Restore Database screen seen in Figure 8-5.

Figure 8-5: Restore Database screen

The Restore Database screen shows the most recent database backups chronologically. Under the Restore As Database option, you can specify the database you'd like to restore or create. You can either select an existing database to restore to or type in the name of a nonexistent database to create and load. The easiest method of restoring your database is to select the Database option from the Restore radio buttons.

For the Show Backups Of Database option, select the database for which you created the backups. You can also select another database if you want to quickly migrate data from one database to another. Once you select the database and the First Backup To Restore option, you need to check the backups to restore from the Restore column. If you've performed transaction log backups, the Point In Time Restore option is also available.

In the Options tab, you have the following options available:

• Eject Tapes (if any) After Restoring Each Backup Specifies that the backup media be ejected after each restoring of the database by selecting the Eject Tapes After Restoring Each Backup option. If you don't have tape media installed on the server, this option is ignored.

• Prompt Before Restoring Each Backup Presents a dialog box as SQL Server completes each backup and moves on to the next. This is handy when you're restoring transaction log backups and need to be alerted when SQL Server reaches each backup file.

• Force Restore Over Existing Database Overwrites the existing database and log files. If you try to overwrite a database without checking this option, you receive an error.

  

• Restore Database Files As This is the equivalent of the MOVE option. This allows you to change the path or filename you're restoring to. The files do not have to exist to restore to them. If they don't exist, SQL Server creates them automatically. If, for example, you're restoring to the Northwind database and want the database files moved to a different drive, you can specify the new drive and filenames here. After the restore, the old files are deleted.

• Leave Database Operational. No Additional Transaction Logs Can Be Restored Closes the restore and does not allow you to restore any more transaction log backups.

• Leave Database Nonoperational but Able to Restore Additional Transaction Logs Leaves the database in the state of Loading until a restore is performed with the Leave Database Operational option selected.

• Leave Database Read-Only and Able to Restore Additional Transaction Logs Leaves the database in read-only mode until additional restores are made and a final restore made with the Leave Database Operational option selected.

• Undo File Specifies the path and filename of the undo file.

After you have all of your options selected, you can click OK to restore the database.

Often, you'll need to restore a database from a different system or from a file that is not on the list of recent backups. You can do this by selecting the From Device radio button in the General tab of the Restore Database screen. Once you select the option, click Add to add the devices or backup files to restore.

Full Restore in T-SQL

To restore a database through T-SQL, you can use the RESTORE DATABASE command. The syntax offers a few added features that Enterprise Manager doesn't, such as passwords. You can restore a database with the following syntax:

RESTORE DATABASE { <database name>} [ FROM < backup device | DISK <filename>> [ ,...n ] ] [ WITH      [ RESTRICTED_USER ]      [ [ , ] FILE = { <file number>} ]      [ [ , ] PASSWORD = { <password>} ]      [ [ , ] MEDIANAME = { <media name>} ]      [ [ , ] MEDIAPASSWORD = { <mediapassword>} ]      [ [ , ] MOVE 'logical file name' TO 'OS path and file name' ]              [ ,...n ]      [ [ , ] KEEP_REPLICATION ]      [ [ , ] { NORECOVERY | RECOVERY | STANDBY = undo_file_name } ]      [ [ , ] { NOREWIND | REWIND } ]      [ [ , ] { NOUNLOAD | UNLOAD } ]      [ [ , ] REPLACE ]      [ [ , ] RESTART ]      [ [ , ] STATS [ = percentage ] ] ]

Most of the parameters are the same as those in the BACKUP command and require no further explanation. Here are a few of the parameters that are not the same as the BACKUP command parameters:

• RESTRICTED_USER Restricts access to the newly restored database to only members of the db_owner, dbcreator, or sysadmin role.

• MOVE Used to restore the database to a different path and filename. This is especially useful when you restore a database to a different server that may not have the same directory structure.

• NORECOVERY | RECOVERY The NORECOVERY option is used to keep the database in a restored state to allow the administrator to apply more differential or transaction log backups. The RECOVERY option specifies that there are no further backups to apply. If you don't specify one of these options, the RECOVERY option is assumed.

• REPLACE Creates the database and its files even if a database with the same name already exists.

• RESTART Restarts the restore operation at the point of failure when restoring a database from a tape.

• KEEP_REPLICATION Does not destroy your replication settings when restoring the database. Is not allowed in conjunction with the NORECOVERY option.

Let's look at a few examples. To restore the Northwind database from a file, you can use the following syntax:

RESTORE DATABASE Northwind FROM DISK =  N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Northwind.bak' WITH RECOVERY, STATS = 25

This outputs the following results to the client:

27 percent restored. 52 percent restored. 77 percent restored. 100 percent restored. Processed 352 pages for database 'Northwind', file 'Northwind' on file 1. Processed 1 pages for database 'Northwind', file 'Northwind_log' on file 1. RESTORE DATABASE successfully processed 353 pages in 3.385 seconds (0.852 MB/sec).

A database doesn't have to exist to restore it. The MOVE option lets you create a copy of the database on the same server, but to a different path. To do this, simply specify a new database name and where you'd like the files to be located after the restore. You can also use the MOVE option if you are restoring to a new server that doesn't have the same directory structure or that has another file with the same name.

RESTORE DATABASE NewNorthwind  FROM DISK =  N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Northwind.bak'    WITH MOVE 'Northwind' TO 'd:\newfiledb.mdf',    MOVE 'Northwind_log' TO 'd:\newfiledb.ldf'

In the Trenches

A common mistake is to mistype the name of the backup file. If you do this, SQL Server outputs an error that may not be obvious at first. This error complains about a device being offline, but in actuality, you've just mistyped the filename.

Server: Msg 3201, Level 16, State 2, Line 1 Cannot open backup device 'C:\MSSQL7\BACKUP\Northwind\Northwind_db_200103181046.BAK'. Device error or device off-line. See the SQL Server error log for more details. Server: Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally. 

This creates the NewNorthwind database and its associated files. SQL Server provides you with ample warning to avoid overwriting another database file. If you do try to overwrite one of the files, you receive this error:

Server: Msg 1834, Level 16, State 1, Line 1 The file 'd:\newfiledb.mdf' cannot be overwritten. It is being used by database 'NEWNORTHWIND'. Server: Msg 3156, Level 16, State 1, Line 1 File 'Northwind' cannot be restored to 'd:\newfiledb.mdf'. Use WITH MOVE to identify a valid location for the file. Server: Msg 1834, Level 16, State 1, Line 1 The file 'd:\newfiledb.ldf' cannot be overwritten. It is being used by database 'NEWNORTHWIND'. Server: Msg 3156, Level 16, State 1, Line 1 File 'Northwind_log' cannot be restored to 'd:\newfiledb.ldf'. Use WITH MOVE to identify a valid location for the file. Server: Msg 3013, Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.

Differential Database Restores

You must restore the last full backup before the differential backup. Enterprise Manager does this automatically when you check the differential backup, as shown in Figure 8-6.

Figure 8-6: Restoring a differential database backup

To perform a differential restore in T-SQL, use the same syntax as you would to perform a full restore, except you must specify the NORECOVERY parameter for the first file. This clause leaves the database nonoperational until the differential backup is applied. When the differential file is applied, you can explicitly specify the RECOVERY parameter to open the database for users. The recovery option is optional.

In the following syntax, I demonstrate how to restore a differential backup from the same file as the full backup using the FILE parameter:

RESTORE DATABASE Northwind FROM DISK =  N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Northwind.bak' WITH FILE = 1, NORECOVERY GO RESTORE DATABASE Northwind FROM DISK =  N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Northwind.bak' WITH FILE = 2, RECOVERY GO

Transaction Log Restores

Transaction log backups are just as easy to apply as differential backups. First restore the last full backup, followed by the last differential backup, and finally any transaction logs in chronological order to get you to the point in time of the disaster. Enterprise Manager does all this work for you. In Enterprise Manager, you can also specify an exact time to restore to by checking the Point In Time Restore option and specifying a time. Select the backups from the Restore column that you want to restore.

You can use T-SQL to restore a transaction log backup just as you would a differential, but you use the RESTORE LOG command rather than RESTORE DATABASE. I've again used the FILE parameter in the following syntax, but in this case it's not required since I'm using the only file in my backup file:

RESTORE DATABASE Northwind FROM DISK =  N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Northwind.bak' WITH FILE = 1, NORECOVERY GO RESTORE LOG Northwind   FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\NorthwindTran.bak'     WITH FILE = 1, 

In the Trenches

You may experience error 4305, which can be confusing. This error deals with the recovery model and complains about the transaction log entries starting too late:

Server: Msg 4305, Level 16, State 1, Line 1 The log in this backup set begins at LSN 28000000016700001, which is too late to apply to the database. An earlier log backup that includes LSN 28000000016200001 can be restored. Server: Msg 3013, Level 16, State 1, Line 1 RESTORE LOG is terminating abnormally.

This error is generally created when you change a database from the Simple recovery model to a Full or Bulk-Logged recovery model after you have a complete backup. After you change recovery models, you should perform a full backup before performing any transaction log backups. This is why recovery model decisions are such big ones and should always be made before you deploy the product. Even though you can change recovery models after deployment, it can sometimes be difficult, as you can see from this error.

Point-In-Time Scenarios

Let's take a look at a practical application for transaction log backups. Say, for example, your database has full backups performed every morning before business starts at 7:00 A.M. Your database also has transaction log backups performed every three hours. At 9:56 A.M., a user forgets to place a WHERE clause in an ad hoc query and updates everyone's salary from the employees table to $176,000.

The HR manager comes screaming into your office at 10:05 A.M., telling you to restore it to the way it was. Although it may be tempting to leave the salaries as is and wait for the check to arrive, you decide to restore the database to the point at which the error occurred. Luckily, a transaction log backup just occurred at 10:00 A.M. The RESTORE command also has an added feature of being able to restore to a given time using the STOPAT parameter followed by the date and time of the error. Since you know exactly when the error occurred, you use the following syntax to restore the database:

RESTORE DATABASE Northwind FROM DISK =  N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Northwind.bak' WITH FILE = 1, NORECOVERY GO RESTORE LOG Northwind FROM DISK =  N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\Northwind.bak'    WITH FILE = 2, STOPAT = 'Mar 18, 2001 9:56 AM' GO

Note 

Keep in mind that if you restore the transaction log at 10:05 A.M., you will also lose ten minutes of transactions. The time on the server may vary from the time on the workstation.

Fixing Broken Logins

In Chapter 3, I discussed the difference between logins and users. As you may remember, logins give you access to the server, while users give you access to the individual database objects. A login holds all the server role information, and the user holds any associated database permissions and roles.

After you restore or attach a database to a different server, some of your logins may not be able to connect to the database. This is because the link between your SQL Server's logins and users almost always gets broken. When you add a login to a SQL Server, an entry is placed into your syslogins table in the master database (among other tables). When the entry is made into this table, the login is also assigned a SID (SUID in SQL Server 7.0). Generally, you associate the login with databases when you create the login. As you do this to each database, an entry is made into each database's sysuser table and is mapped back to the syslogin table with the SID, as shown here:

Master..syslogins Database		Northwind..sysusers
SID	Login Name	SID	User Name
1	SA	1	Dbo
10	Brian	10	Brian
11	Kathy	11	Kathy

I've simplified the SIDs for brevity. In reality, a SID is a very long ID, such as 0x1AD2D68CF6330E47865C9494DEB8918A. Once you restore a database from another server, the SID information will not match. As you can see in the following table, when you restore the database onto the separate server, your logins no longer will match your users. This may in some rare cases give permissions to the tables to users who don't need access, and more often, not allow in users who should have access. The SIDs that don't match are denied access to the database objects.

Master..syslogins Database		Northwind..sysusers
SID	Login Name	SID	User Name
1	SA	1	Dbo
10	John	10	Brian
11	Mary	11	Kathy
12	Brian
13	Kathy

A symptom of this problem appears when you see the user in the sysusers table, but not in Enterprise Manager under Users in the restored database.

Using sp_change_users_login

The primary way to fix this login problem is to use the sp_change_users_login system stored procedure. This stored procedure rematches the users to the appropriate logins. This procedure should be run only from the restored database after the logins have been re-created on the new system. To determine which logins don't match, you can run the stored procedure with the Report parameter as shown here:

sp_change_users_login 'report'

This outputs the following results:

UserName                        UserSID                           ------------------------------  --------------------------------  Brian                           0xC7492F53646AD411934F0090273AC6 Kathy                           0x5C2C14C4376ED411934F0090273AC6

To correct user names, use the Auto_Fix parameter as shown here. (This syntax was changed slightly in Service Pack 3 of SQL Server; check BOL for latest update.)

sp_change_users_login 'auto_fix', Brian

This stored procedure also creates the logins on the secondary system if they don't exist yet. You have to fix the login's password and assign users back to any server roles they were placed in previously. This is why I prefer to have the logins already created on the second system before I run this stored procedure. After running the procedure, users have their original permissions re-established, as demonstrated here:

New login created. Barring a conflict, the row for user 'Brian' will be fixed by updating its link to a new login. Consider changing the new password from null. The number of orphaned users fixed by updating users was 0. The number of orphaned users fixed by adding new logins and then updating users was 1. 

Note 

The sp_change_users_login system stored procedure deals only with SQL Server Authentication logins, not with Windows Authentication logins.

Another way you can use the sp_change_users_login stored procedure is to use the Update_One parameter. With this parameter, you explicitly designate which user on the restored database should map to which login on the server. You can use the following syntax to fix a user with this parameter:

sp_change_users_login 'Update_One', <user name>, <login name>

Recovering a Corrupt Master Database

One of the most difficult situations to resolve is a corrupt or bad master database. The master database contains the vital information SQL Server needs. If your master database is corrupt, your SQL Server will not start. To restore a master database in this situation, you can follow these steps to recover from the disaster:

1. Rebuild the master database by using the rebuildm.exe file located in the \Program Files\Microsoft SQL Server\80\Tools\Binn directory. Rebuilding the master database leaves your database files intact. It is always a good idea to back up the data and log files to a separate directory, just in case.

2. Restart SQL Server in single user mode by starting SQL Server with the -m parameter.

3. Restore your master database from the last known good backup.

4. Verify that the master database was successfully restored: confirm that all the databases are up and running. Restore the msdb database from the last known good backup.

5. Stop and start SQL Server in normal mode.

6. Open your database for production users.

If this does not work, rebuild the master database and then attach the databases. This task is also one of those DBA tasks that you hope you don't have to perform often. If the master database has problems, every database on the server has problems.

Rebuilding Other Databases

In some cases you can rebuild some system databases without having to restore at all. In a worst case scenario, you may have to rebuild the msdb, Northwind, or pubs databases because your backups have failed. In these cases, you can run the appropriate scripts from the \Program Files\Microsoft SQL Server\MSSQL\Install directory to rebuild these three databases. Use the following files to rebuild these databases:

• instmsdb.sql msdb database

• instnwnd.sql Northwind database

• instpubs.sql pubs database

Caution 

When you rebuild the msdb database, jobs, DTS packages, and other vital information will be lost. Always try to restore it first. You may be able to use this method to rebuild it if it is corrupt, and then restore on top of the newly rebuilt database.