SQL Server – Is Partial Restore of Data from Backup Possible?

restoresql serversql-server-2008-r2

Apologies if I haven't used the correct terminology, I am not a DBA, but right now, I am all that is here!

Situation is last week, a database server was misbehaving (it was about 6 years old). The hosting company built a new server, and moved all the current data across to the new server, which is running well.

However, somehow, the new server is missing 36 hours of data. The hosting company has provided us with a backup that contains this data, amongst other things.

As some of the tables have auto generated keys (type integer) is there any way of restoring just a set of data from a date range back into my live database? I obviously can't just cut and paste, as all the integrity of the data will be lost.

Thoughts?

Windows Server and Microsoft SQL Server 2008.

Best Answer

Not directly, but here's a work-around

The way you do partial restores of individual tables or rows in SQL Server is to restore the backups to a different database name and then manually move your specific missing data from the restored database back to your production DB.

This involves a couple of steps depending on your environment. If the original database is named DB1 then you can do something like:

RESTORE FILELISTONLY
FROM DISK = 'PATH\To\FullBackup.bak';

This will give you the logical and physical file names to insert into the next step:

RESTORE DATABASE DB1_RESTORE
FROM DISK = 'PATH\To\Full_Backup.bak'
WITH MOVE 'Data' TO 'PATH\To\DataFiles\DB1_Restore_01.mdf',
     MOVE 'Log' TO 'PATH\To\LogFiles\DB1_Log_Restore_01.ldf', NORecovery;

You will need to adjust that for your specific environment, but basically there are two critical points to remember:

  1. Rename the database itself to something you can recognize isn't the real DB
  2. Rename EACH FILE to something that is a new name, so you don't overwrite the existing DB files.

If you have Diff backup files, then do

RESTORE DATABASE DB1_RESTORE
    FROM DISK = '\PATH\To\Diff_backup.bak'
    WITH NORECOVERY;

And then for each log file backup:

RESTORE LOG DB1_RESTORE FROM DISK = '\PATH\To\LogFile_1.trn'  WITH NORECOVERY;
RESTORE LOG DB1_RESTORE FROM DISK = '\PATH\To\LogFile_2.trn'  WITH NORECOVERY;
RESTORE LOG DB1_RESTORE FROM DISK = '\PATH\To\LogFile_3.trn'  WITH NORECOVERY;

and so on until you've restored all the log files.

Finally,

RESTORE DATABASE DB1_RESTORE WITH RECOVERY;

After that, you can then start manually copying data from the restored DB to the original production DB.

But before you copy data back into the Production DB, take a full backup of that DB. Just in case.

If you need to insert data into tables with identity (autonumber) columns, use

SET Identity_Insert DB1.dbo.tablename ON

INSERT INTO DB1.dbo.tablename ( col1, col2, col3 )
Select col1, col2, col3
From DB1_Restore.dbo.tablename Where col1 > 1234

SET Identity_Insert DB1.dbo.tablename OFF

The above is a very generic script and won't work without some effort on your part, but it should get you started. The Identity_Insert statements allow you to insert into an Identity column. Just be sure to get your WHERE clause right, so you don't insert bad data.

Related Question