All the knowledge you have accumulated as a database administration is most important in one defining moment. That is the moment when you attempt to restore a database. The techniques you use to restore a database depend on the backup options you have used and the state of the database. As you know, the backup techniques available are full, differential, transaction log, and file/filegroups. What you may not know is how to restore a database by combining these techniques.

Table 14-2 lists some suggested recovery strategies for corrupted databases. These strategies show how to recover a database with various combinations of the available backup operations. If you use SQL Server Management Studio for backup and restore, these procedures are done for you automatically in most cases. The actual step-by-step process is covered later in this chapter.

Now you know how to restore a database in theory. But before you begin, you should make sure the database is really corrupt and cannot be recovered by other means. To troubleshoot database problems and potential corruption, complete the following steps:

You can use the databaseproperty function as shown in Example 14-5.

databaseproperty('database','property')

select databaseproperty('Customer','IsEmergencyMode')

SQL Server Management Studio tracks all the backups you create for each database; when you need to restore a database, SQL Server Management Studio automatically configures the restore. You can restore a database using these default settings or fine-tune the restore operation as necessary.

To restore a database, complete the following steps:

You can restore files and filegroups from database backups or file backups either individually, in combination with each other, or all together. If any changes were made to the files or filegroups, you must also restore all transaction log backups that were created after the files or filegroups were backed up.

Although you can usually recover individual files or filegroups, there are exceptions. If tables and indexes are created that span multiple filegroups, all the related filegroups must be restored together. Do not worry, for SQL Server generates an error prior to starting the restore if a needed filegroup is missing. Further, if the entire database is corrupted, you must restore all files and filegroups in the database. In both cases, you must also apply transaction log backups created after the file or filegroup backups you are restoring.

To restore files or filegroups, complete the following steps:

When you restore a database to a different location, you are essentially copying the database from backups. If you use this procedure to copy a database to a new location on the same computer, you create a copy of the database that can have separate files and a different database name. Restoring a database to a different location is similar to the process of restoring files and filegroups discussed previously. The main differences are as follows:

If you use this procedure to copy a database to a different computer, you can create a working copy of the database on another server. You do not need to create a new database or perform any preliminary work, with one exception—if you want to use backup devices on the destination server, you should set them up beforehand. Also, before you begin the restore, you should ensure that the destination computer is using the same code page, sort order, Unicode collation, and Unicode locale as the source server. If these configuration settings are not identical, you will not be able to run the database on the destination server.

If you suspect part of the database is missing or corrupted, you can perform a partial restore to a new location so that you can recover the missing or corrupted data. To do this, use the PARTIAL option with the RESTORE DATABASE statement as discussed in the subsection titled "Using Transact-SQL Restore Commands" later in this chapter. You can restore partial databases only at the filegroup level. The primary file and filegroup are always restored along with the files that you specify and their corresponding filegroups. Files and filegroups that are not restored are marked as offline and you cannot access them.

To carry out the restore and recovery process, complete the following steps:

The notion of restoring a backup to a different computer can be extended to create a standby backup server that you can bring online if the primary server fails. When you create a standby server, you have two options:

Creating a Cold Standby

To create a standby that you synchronize manually, complete the following steps:

1. Install SQL Server on a new server system using an identical configuration. This means that the destination server should use the same code page, sort order, Unicode collation, and Unicode locale as the source server.

2. Copy all of the databases on the primary server to this new system by specifying a different restore location in the Restore Database dialog box.

3. Maintain the copies of the databases by periodically applying the transaction log backups from the primary to the standby server.

4. You may want to leave the standby server in Standby mode so that the database is read-only. This allows users to access the database but not make changes.

If one or more databases on the primary server fail for any reason, you can make the corresponding databases on the standby server available to users. However, before you do this, you should synchronize the primary and the standby servers by completing the following steps:

1. On the standby server, apply any transaction log backups created on the primary server that have not been applied yet. You must apply these backups in the proper time sequence.

2. Create a backup of the active transaction log on the primary server and apply this backup to the database on the standby server. This ensures up-to-the-minute synchronization. Be sure to recover the database or specify that the database should be put in operational mode after this backup is applied.

   Tip

   

   If you need to make the standby server appear to be the primary server, you may need to take the primary off the network and rename it. Then rename the standby so that it appears to be the primary.

3. After you restore the primary server to working condition, any changes to the standby’s databases need to be restored to the primary server. Otherwise, those changes are lost when you start using the primary server again.

Note

Standby servers are not the same as a SQL Server failover cluster, which is created using the SQL Server Failover Cluster Wizard and Microsoft Cluster Service. Standby servers store a second copy of databases on their hard disk drives. Virtual servers use a single copy of databases that is accessed from a shared storage device.

You can also restore databases using Transact-SQL. The commands you will use are RESTORE DATABASE and RESTORE LOG. You can use RESTORE DATABASE to restore an entire database, specific files and filegroups, or part of a corrupted database. Example 14-6 shows the syntax and usage for a complete restore. The option WITH RECOVERY is the default mode.

RESTORE DATABASE { database_name | @database_name_var } 
[ FROM <backup_device> [ ,...n ] ]
[ WITH 
    [ { CHECKSUM | NO_CHECKSUM } ]
    [ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]
    [ [ , ] FILE = { file_number | @file_number } ] 
    [ [ , ] KEEP_REPLICATION ] 
    [ [ , ] MEDIANAME = { media_name | @media_name_variable } ] 
    [ [ , ] MEDIAPASSWORD = { mediapassword |
                    @mediapassword_variable } ] 
    [ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ] 
                 [ ,...n ] 
    [ [ , ] PASSWORD = { password | @password_variable } ] 

    [ [ , ] { RECOVERY | NORECOVERY | STANDBY = 
          {standby_file_name | @standby_file_name_var } }
    ] 
    [ [ , ] REPLACE ] 
    [ [ , ] RESTART ] 
    [ [ , ] RESTRICTED_USER ] 
    [ [ , ] { REWIND | NOREWIND } ] 
    [ [ , ] STATS [ =percentage ] ] 
       [ [ , ] STOPAT = { date_time | @date_time_var } |
     [ , ] STOPATMARK = { 'mark_name' | 'lsn:lsn_number' }
             [ AFTER datetime ] |
     [ , ] STOPBEFOREMARK = { 'mark_name' | 'lsn:lsn_number' }
             [ AFTER datetime ] 
    ] 
  [ [ , ] { UNLOAD | NOUNLOAD } ] 
]
[;]
<backup_device> ::=
{ { 'logical_backup_device_name' |
            @logical_backup_device_name_var }
   | { DISK | TAPE } = { 'physical_backup_device_name' |
              @physical_backup_device_name_var } }

RESTORE DATABASE Customer
   FROM TAPE = '\.	ape0'

RESTORE DATABASE Customer
   FROM Customer_1
   WITH NORECOVERY,
         MOVE 'CustomerData1' TO 'F:mssql7dataNewCust.mdf',
         MOVE 'CustomerLog1' TO 'F:mssql7dataNewCust.ldf'
RESTORE LOG Customer
   FROM CustomerLog1
   WITH RECOVERY

Using RESTORE DATABASE, you also can restore files and filegroups. Example 14-7 shows the related syntax and usage.

RESTORE DATABASE { database_name | @database_name_var } 
     <file_or_filegroup_or_pages> [ ,...f ]
[ FROM <backup_device> [ ,...n ] ]
[ WITH 
   [ { CHECKSUM | NO_CHECKSUM } ]
   [ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]
   [ [ , ] FILE = { file_number | @file_number } ] 

   [ [ , ] MEDIANAME = { media_name | @media_name_variable } ] 
   [ [ , ] MEDIAPASSWORD = { mediapassword |
                      @mediapassword_variable } ]
   [ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ] 
                [ ,...n ] 
   [ [ , ] PASSWORD = { password | @password_variable } ] 
   [ [ , ] NORECOVERY ] 
   [ [ , ] REPLACE ] 
   [ [ , ] RESTART ] 
   [ [ , ] RESTRICTED_USER ]
   [ [ , ] { REWIND | NOREWIND } ] 
   [ [ , ] STATS [ =percentage ] ] 
   [ [ , ] { UNLOAD | NOUNLOAD } ] 
]
[;]
<backup_device> ::=
{  { logical_backup_device_name|
            @logical_backup_device_name_var }
   | { DISK | TAPE } = { 'physical_backup_device_name' |
              @physical_backup_device_name_var } } 
<file_or_filegroup_or_pages> ::=
{  FILE = { logical_file_name | @logical_file_name_var }
   | FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var 
}}
   | PAGE = 'file:page [ ,...p ]'  }

RESTORE DATABASE Customer
   FILE = 'Customerdata_1',
   FILE = 'Customerdata_2',
   FILEGROUP = 'Primary'
   FROM Customer_1
   WITH NORECOVERY
RESTORE LOG Customer
   FROM CustomerLog1

Example 14-8 shows the syntax for performing a partial restore. This command creates a new database that is based on a partial copy of the backup data. When you use this procedure, the database_name represents the new name for the database, and the MOVE/TO command is used to move the original database source files to new locations.

RESTORE DATABASE { database_name | @database_name_var } 
     <files_or_filegroups> 
[ FROM <backup_device> [ ,...n ] ] 
[ WITH 
     { PARTIAL } 

     [ [ , ] { CHECKSUM | NO_CHECKSUM } ]
     [ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]
     [ [ , ] FILE = { file_number | @file_number } ] 
     [ [ , ] MEDIANAME = { media_name | @media_name_variable } ]g 
     [ [ , ] MEDIAPASSWORD = { mediapassword |
                        @mediapassword_variable } ] 
     [ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ] 
                  [ ,...n ] 
     [ [ , ] PASSWORD = { password | @password_variable } ] 
     [ [ , ] NORECOVERY ] 
     [ [ , ] REPLACE ] 
     [ [ , ] RESTART ] 
     [ [ , ] RESTRICTED_USER ]
     [ [ , ] { REWIND | NOREWIND } ] 
     [ [ , ] STATS [=percentage ] ] 
     [ [ , ] STOPAT = { date_time | @date_time_var } |
       [ , ] STOPATMARK = { 'mark_name' | 'lsn:lsn_number' }
               [ AFTER datetime ] |
               [ , ] STOPBEFOREMARK = { 'mark_name' | 'lsn:lsn_number' }
               [ AFTER datetime ] 
     ] 
     [ [ , ] { UNLOAD | NOUNLOAD } ] 
]
[;]
<backup_device> ::=
{ { logical_backup_device_name |
            @logical_backup_device_name_var }
  | { DISK | TAPE } = { 'physical_backup_device_name' |
             @physical_backup_device_name_var } } 

<files_or_filegroups> ::=
        { FILE = { logical_file_name | @logical_file_name_var } 
       |
      FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var }} 
    [ ,...f ]

RESTORE DATABASE cust_part
   FILEGROUP = 'Customers2'
   FROM DISK='g:cust.dmp'
   WITH FILE=1,NORECOVERY,PARTIAL,
   MOVE 'cust' TO 'g:cu2.pri',
   MOVE 'cust_log' TO 'g:cu2.log',
   MOVE 'cust_data_2' TO 'g:cu2.dat2'
GO
RESTORE LOG cust_part
   FROM DISK = 'g:cust.dmp'
   WITH FILE = 2,RECOVERY
GO

Example 14-9 shows how you can use RESTORE LOG.

RESTORE LOG { database_name | @database_name_var } 
     <file_or_filegroup_or_pages> [ ,...f ]
[ FROM <backup_device> [ ,...n ] ] 
[ WITH 
   [ { CHECKSUM | NO_CHECKSUM } ]
   [ [ , ] { CONTINUE_AFTER_ERROR | STOP_ON_ERROR } ]
   [ [ , ] FILE = { file_number | @file_number } ] 
   [ [ , ] KEEP_REPLICATION ] 
   [ [ , ] MEDIANAME = { media_name | @media_name_variable } ] 
   [ [ , ] MEDIAPASSWORD = { mediapassword | @mediapassword_variable }      
]
   [ [ , ] MOVE 'logical_file_name' TO 'operating_system_file_name' ] 
                [ ,...n ] 
   [ [ , ] PASSWORD = { password | @password_variable } ] 
   [ [ , ] { RECOVERY | NORECOVERY | STANDBY = 
          {standby_file_name | @standby_file_name_var } }
   ] 
   [ [ , ] REPLACE ] 
   [ [ , ] RESTART ]
   [ [ , ] RESTRICTED_USER ]
   [ [ , ] { REWIND | NOREWIND } ] 
   [ [ , ] STATS [=percentage ] ] 
   [ [ , ] STOPAT = { date_time | @date_time_var } |
     [ , ] STOPATMARK = { 'mark_name' | 'lsn:lsn_number' }
             [ AFTER datetime ] |
     [ , ] STOPBEFOREMARK = { 'mark_name' | 'lsn:lsn_number' }
             [ AFTER datetime ] 
     ] 
   [ [ , ] { UNLOAD | NOUNLOAD } ] 
]
[;]
<backup_device> ::=
{ { logical_backup_device_name |
            @logical_backup_device_name_var }
   | { DISK | TAPE } = { 'physical_backup_device_name' |
              @physical_backup_device_name_var } } 
<file_or_filegroup_or_pages> ::=
{  FILE = { logical_file_name | @logical_file_name_var }
  | FILEGROUP = { logical_filegroup_name | @logical_filegroup_name_var } 
}
   | PAGE = 'file:page [ ,...p ]'  }

RESTORE DATABASE Customer
   FROM Customer_1, Customer_2
   WITH NORECOVERY

RESTORE LOG Customer
   FROM CustomerLog1
   WITH NORECOVERY
RESTORE LOG Customer
   FROM CustomerLog2
   WITH RECOVERY, STOPAT = 'Dec 11, 2006 3:30 PM'

In SQL Server 2005, full-text catalogs are treated as files and are included in the database file set for the purposes of backup and restore. When you restore a database, the full-text catalogs are restored automatically. When you restore the filegroup associated with one or more full-text catalogs, the catalogs are restored as well. Changes to full-text catalogs are backed up with standard differential backups of a database or the associated filegroup. Although full-text catalogs are backed up and restored in association with database files, there are also ways to back up and restore only your full-text catalogs.

You can restore a full-text catalog by itself using T-SQL. To do this, you specify the logical file name of the full-text catalog using the FILE clause of the RESTORE statement. Consider the following example:

RESTORE DATABASE Customer

FILE = 'customerdb_cat'

FROM Customer_Catalog

WITH NORECOVERY

RESTORE DATABASE Customer

FILE = 'customerdb_cat'

FROM Customer_Catalog2

In this example, you specify the backup device to use for the restore as Customer_Catalog. You restore the last full backup of the full-text catalog named customerdb_cat. Next, you restore the differential backup of the full-text catalog. It is important to note that the first restore uses the NORECOVERY option to ensure that the database is offline, and the Full-Text Search service is stopped during the restore. After the second restore, however, the database is brought back online and the Full-Text Search service is started.

You can restore multiple catalogs as part of a filegroup as well. To do this, use the FILEGROUP option to specify the logical name of the filegroup to restore, such as:

RESTORE DATABASE Customer

FILEGROUP = 'Catalogs_Primary'

FROM Customer_Catalog

You can restore catalogs to an alternate location using the WITH MOVE option. This option lets you set the root folder location for catalogs being restored. Consider the following example:

RESTORE DATABASE Customer

FROM Customer_Catalog

WITH MOVE 'customerdb_cat' TO 'C:DataCatalogs'

Here, you specify the backup device to use for the restore as Customer_Catalog. You restore the last full backup of the full-text catalog named customerdb_cat to the C:DataCatalogs folders.