1

We have been given a .bak file of a database from our vendor on a completely different network. They are placing their .trn files every 15 minutes on a sftp for us to pickup and write to the .bak file they sent us. This way we will have data up-to-date every 15 minutes or so.

What I have done is restored their backup WITH NORECOVERY. Then I restored all 800 .trn files WITH NORECOVERY, setting the last restore to WITH RECOVERY. Now that it is out of NO RECOVERY we can read from the DB.

Now that we are up-to-date, what is the process for restoring the new .trn files that they send us?

I apologize for the ignorance, I am new to the DBA game.

Improve this question
3
  • 2
    Once you use with recovery, you can no longer apply transaction logs. You need to restore no recovery and use a standby file. Take a look at StandBy option while restoring database
    – Scott Hodgin - Retired
    Sep 9, 2019 at 20:29
  • Thanks for the comment @ScottHodgin. Could you help me understand: When you "RESTORE WITH STANDBY" it asks for a Standby file. What is this used for? The default file points to the .bak file I received. Does this mean that when reading from the DB, I will only see data as far as what is in the .bak file? I want to ensure that we can read all transactions post .bak file as well. Also, can normal backups be taken of the database while it is in Standby?
    – Caleb Fortner
    Sep 9, 2019 at 20:57
  • NORECOVERY only does the redo operations in the log backup. RECOVERY also perform UNDO (rollback). Because of that, you cannot "connect" one more log backup after that, since a prior potential rollback will leave "gap of data". STANDBY does UNDO, but it saves that work in the file that you specify. This means that for a subsequent restore, it can undo the precious UNDO with thanks to the undo file. When using STANDBY you will see "current" information as per the most recent log backup, minus what was rolled back, of course. No, you cannot take a backup of a database where you did STANDBY.
    – Tibor Karaszi
    Sep 10, 2019 at 9:32

1 Answer 1

Reset to default
1

You should only issue WITH RECOVERY when it's the last backup to restore or when you are sure that is the point where you want to go. After that there's no way to restore any more backups.

Now you will need to restore everything again to keep the same log chain.

If you need to "open" the db without break the log chain, you must use WITH STANDBY = 'path/file'. This will open the DB in READ ONLY mode and when you need to restore more files, SQL Server will use the 'path/file' you create to allow the DB to continue restore the log chain.

You may take a look to this option here: https://learn.microsoft.com/en-us/sql/t-sql/statements/restore-statements-arguments-transact-sql?view=sql-server-2017

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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