Tip: Recover Missing Data in SQL Server 2008 Using a Partial Restore

If you suspect part of a 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 in Transact-SQL. 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:
1. Perform a partial database restore. Give the database a new name and location in the RESTORE DATABASE statement and use MOVE/TO to move the original database source files to new locations. For example:

RESTORE DATABASE new_custdb_partial
     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

2. Extract any needed data from the partial restore, and insert it into the database from which it was deleted.

From the Microsoft Press book Microsoft SQL Server 2008 Administrator's Pocket Consultant.

Looking for More Tips?

For more SQL Server Tips, visit the TechNet Magazine SQL Server Tips page.

For more Tips on other products, visit the TechNet Magazine Tips index.