2

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.

  • 1
    In the future, in cases where bringing the new server up is not urgent, then leaving it in "Restore with norecover" or "Restore with standby"(read-only) will allow you to replay transactions from backups. Once you start accepting new transactions on an out-of-date restore, then you can't easily reapply missed transactions from backups. – AaronLS Aug 7 '17 at 17:40
4

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.

  • 1
    Thanks, I'll create a local environment here and do a test run first! – Matt Aug 7 '17 at 13:07
7

No, you cannot do what you mentioned in your question.

I am sure there are many ways to recover the data you need. One way is to:

  • Restore the backup as a different(name) database.
  • Reconcile data using this source and your current live database. How you do that? Will entirely depend upon with what you have lost and your business logic.
  • You can surely take advantage of auto generated integer keys (at least for the tables that has this). You might have to do some plumbing here depending on where is the gap in your current live database.

I know it is a very generic answer. If you need specific help with individual tables, you will need to post table schema, sample data and details of gaps.

On a side note you probably want to reconsider using this hosting company going forward.

  • 1
    WRT the hosting company, I agree. Especially as no one told us this had happened, until I had to query some missing data! – Matt Aug 7 '17 at 13:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.