Sql-server – how to restore

restoresql-server-2008-r2

I am not sure if this has been addressed already. I have been a long time reader but this is my fifth post. I need assistance with a database restore solution. I receive a full BAK and the transaction logs 30 minutes from a supplier. I would like to restore the DB and process the TRN files nightly. The database has to be in a mode where I can query and insert the new information into a reporting database after the TRN files have been processed and then repeat the process the next night. It seems no matter what I try I get the "Different Recovery Path" error or I can process everything only for that day. I am running SQL Server 2008 R2 standard and have been banging my head on this one for about a week now.

Any help or pointers would be appreciated.

Best Answer

If you RECOVER the database at any point between the Database restore and a transaction log, the transaction logs that follow are no longer in the same recovery path. Recovery of the database starts a new Recovery Path.

If you are using Enterprise Edition (likely what Shanky was wondering) instead of recovering the database in order to read from it, you can create a SNAPSHOT DATABASE that you can read from.

Or, you could RESTORE DATABASE ... WITH STANDBY = 'StandByFile' and can also use RESTORE LOG ... WITH STANDBY = 'StandByFile' to allow you to read from a database still in RECOVERY.

Both of these approaches should permit you to restore further LOG backups in order to reach a new point of recovery.

There are instructions for a warm standby at: http://technet.microsoft.com/en-us/library/ms178034(v=sql.105).aspx

From your description I am unclear on exactly when you get the backup files and what your recovery points are intended to be. But either a SNAPSHOT database or STANDBY mode may help you.

Related Solutions

Sql-server – How to restore multiple backups

Using SSMS, you cannot chain a restore of the backups in one operation. You would have to do multiple restores. You'll want to use T-SQL in order to be more efficient.

--Restore the most recent full backup
RESTORE DATABASE <mydb>
FROM DISK = 'Path to full backup'
WITH NORECOVERY, STATS=10 --If only restoring the full, change to RECOVERY

--Restore the most recent diff backup
RESTORE DATABASE <mydb>
FROM DISK = 'Path to diff backup'
WITH RECOVERY, STATS=10

More info on RESTORE: http://msdn.microsoft.com/en-us/library/ms186858.aspx

SQL Server – Restore BAK File to Smaller MDF and LDF Database Files

The backup contains two pieces of information:

the database files layout and metadata, ie. RESTORE FILELISTONLY ...
the actual data

The actual data in the backup is those 1.8 GB. But the metadata describes the layout of files as on the original production server. While it looks like compression, is not actual compression. Is just metadata vs. content. the problem is that during RESTORE the reporting machine creates a layout as described in the metadata, therefore 114.6GB + 24.8GB. Now for the real question: can you restore it w/o recreating the original DB file layout? No. However, there is another solutions to your problem: bring the original DB back in check. Shrinking the Transaction Log covers the steps you need to do, since the log is your major pain point. Next I would deploy log shipping instead of backup/restore.

Best Answer

Related Solutions

Sql-server – How to restore multiple backups

SQL Server – Restore BAK File to Smaller MDF and LDF Database Files

Related Question