Partial Database Restore Operations

Application or user errors often affect an isolated portion of the database, such as a table. To support recovery from these events, Microsoft® SQL Server™ provides a mechanism to restore part of the database to another location so that the damaged or missing data can be copied back to the original database. For example, if an application erroneously dropped a table, you may want to restore only the part of the database that contained the table. Restoring log or differential backups can bring the table to a point prior to when the table was dropped. Then the content of the table can be extracted and reloaded into the original database.

Performing a partial restore operation is also useful when you are:

• Creating a subset of a database on another server for development or reporting purposes.
  
  
• Restoring archived data.

Partial restore operations work with database filegroups. The primary filegroup is always restored, along with the files that you specify and their corresponding filegroups. The result is a subset of the database. Filegroups that are not restored are marked as offline and are not accessible.

Note  Because the primary file is restored, all catalogs (except full-text catalogs) are restored, even those associated with files that are not included in the restore operation.

Partial restore operations are accomplished with the PARTIAL clause of the RESTORE statement. You can also use the PARTIAL option when restoring a full database backup. Partial database restore of file backups is not supported.

To perform a partial restore operation

1. Execute the RESTORE DATABASE statement using a full database backup, specifying:
   • The name of the database to restore. Specify a new name for the database, unless you are planning to overwrite the original database or are restoring the database on a different server.
     
     
   • The backup device from which the database backup will be restored.
     
     
   • The FILEGROUP clause for each file or filegroup to restore.

     Note  If a file is specified, all of the files in its filegroup are also restored.

   • The MOVE clause if you are restoring the files in a new location.
     
     
   • The PARTIAL clause.
     
     
   • The NORECOVERY clause, if there are transaction log or differential backups to be applied. Otherwise, specify RECOVERY.
2. Optionally, execute the RESTORE DATABASE statement to restore a differential database backup, specifying:
   • The name of the database to which the differential database backup will be applied.
     
     
   • The backup device where the differential database backup will be restored from.
     
     
   • The NORECOVERY clause, if you have transaction log backups to apply after the differential database backup is restored; otherwise specify the RECOVERY clause.
3. Execute the RESTORE LOG statement to apply each transaction log backup, specifying:
   • The name of the database to which the log is to be applied.
     
     
   • The backup device from which the log backup will be restored.
     
     
   • The NORECOVERY clause, if there are other log backups to be applied. Otherwise, specify RECOVERY.

Examples

This example performs a partial restore operation in a database, named mywind. mywind is using the Full Recovery model. The database is created on two filegroups, new_customers, which contains the file mywind_data_1, and sales, which contains the file mywind_data_2:

CREATE DATABASE mywind
GO

ALTER DATABASE mywind ADD FILEGROUP new_customers
ALTER DATABASE mywind ADD FILEGROUP sales
GO

ALTER DATABASE mywind ADD FILE 
   (NAME='mywind_data_1',
   FILENAME='g:\mw.dat1') 
   TO FILEGROUP new_customers
ALTER DATABASE mywind 
   ADD FILE 
   (NAME='mywind_data_2',
   FILENAME='g:\mw.dat2') 
   TO FILEGROUP sales
GO

A full database backup is performed. Then the t1 table is created on new_customers and the t2 table is created on sales. The transaction log is backed up:

BACKUP DATABASE mywind
   TO DISK ='g:\mywind.dmp'
   WITH INIT
GO

USE mywind
GO

CREATE TABLE t1 (id int) ON new_customers
CREATE TABLE t2 (id int) ON sales
GO

BACKUP LOG mywind TO DISK='g:\mywind.dmp'
WITH NOINIT
GO

At some point, it becomes necessary to restore the t2 table on the sales filegroup. RESTORE FILELISTONLY lists the database files and the filegroups in which they reside. RESTORE HEADERONLY lists the contents of the backup medium:

RESTORE FILELISTONLY FROM DISK='g:\mywind.dmp'
GO
RESTORE HEADERONLY FROM DISK='g:\mywind.dmp'
GO

The RESTORE DATABASE statement restores the database under a different name and the sales filegroup using the WITH PARTIAL and NORECOVERY options. In addition, the primary file and filegroup (mywind), the log (mywind_log), and all files in the restored filegroup (in this example, mywind_data_2 is the only file in sales) are moved to a new location. The log is then recovered:

RESTORE DATABASE mywind_part 
   FILEGROUP = 'sales'
   FROM DISK='g:\mywind.dmp' 
   WITH FILE=1,NORECOVERY,PARTIAL,
   MOVE 'mywind' TO 'g:\mw2.pri',
   MOVE 'mywind_log' TO 'g:\mw2.log',
   MOVE 'mywind_data_2' TO 'g:\mw2.dat2'
GO

RESTORE LOG mywind_part 
   FROM DISK = 'g:\mywind.dmp' 
   WITH FILE = 2,RECOVERY
GO

Notice that t2 is accessible after the partial restore operation.
SELECT COUNT(*) FROM mywind_part..t2

Here is the result:

---------------
0

Notice that t1 is not accessible after the partial restore operation.

SELECT COUNT(*) FROM mywind_part..t1

Here is the resulting message:

The query processor is unable to produce a plan because 
the table 'mywind_part..t1' is marked OFFLINE.

See Also

Recovering to a Point In Time

RESTORE