How to recover Missing Data in SQL Server Using a Partial Restore

Search FAQs

Issue
Data is missing from a database or the database is corrupted.

Product Line
StruxureWare Power Monitoring 7.0.x
Power Monitoring Expert 7.2.x 8.x
ION Enterprise 6.0.x
ION EEM

Environment
SQL Server 2005, SQL Server 2008 R2, SQL Server 2012, SQL Server 2014, SQL Server 2016

Cause
It may be necessary to troubleshoot the cause of missing data and implement data recovery within a particular database.

Resolution
*Warning: Irreparable database damage can occur. This procedure should only be performed by users familiar with SQL Server Management Studio. Databases should be backed up prior to performing this procedure.*

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 perform 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_partial_data
FILEGROUP = 'DataFiles'
FROM DISK='g:\mydata.dmp'
WITH FILE=1,NORECOVERY,PARTIAL,
MOVE 'mydata' TO 'g:\mydata2.pri',
MOVE 'mydata_log' TO 'g:\mydata2_log.log',
MOVE 'mydata_data_2' TO 'g:\mydata_data_2.dat2'

2. You may then examine and/or extract any needed data from the partial restore, and insert it into the database from which it was deleted.