0

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.

  • Do you do a point in time recovery with these logs? If not, can the supplier not send you a single bak file at the end of the day (before your restore). Then you can run a scheduled restore script with recovery changing any filename/path details to suit if necessary. If you only restore overnight then receiving individual trn files rather than a end of day bak (that would contain the log entries that the trn files would have had) seems overkill? – Penfold Sep 5 '14 at 7:56
  • Can you edit your question and paste the error. What is version and edition of SQL Server involved here ? – Shanky Sep 5 '14 at 8:23
1

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.

|improve this answer|||||

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.