Thursday, October 27, 2011

Tips: How to use Partial Restore to recover the missing data in SQLServer 2008

In order to recover the missing or corrupted data, you can run a partial restore process to a new location. This will be one of the best options if you suspect that a part of a database is missing or corrupted. If you wish perform the partial restore, you can use the PARTIAL option with the RESTORE DATABASE statement in Transact-SQL. Moreover, you can restore the partial databases only at the filegroup level as the primary file and filegroup are always restored along with the files that you specify and their corresponding filegroups. Make sure that the files and the filegroups that aren’t restored will be marked as offline and they’ll not be accessible.

 How to carry out the restore and recovery process?
1. You can perform the partial database restore by giving 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 an instance:

RESTORE DATABASE new_custdb_partial

        FILEGROUP = 'Customers2'

        FROM DISK='g:\cust.dmp'


        MOVE 'cust' TO 'g:\cu2.pri',

        MOVE 'cust_log' TO 'g:\cu2.log',

        MOVE 'cust_data_2' TO 'g:\cu2.dat2'


2. Now, you can extract any needed data from the partial restore.

3. Then, you can re-insert it into the database where it is deleted from.

B  y