Transaction log restore hourly

Posted on

Question :

Is it possible to restore SQL Transaction Logs to a MS SQL database every hour without having to restore the full backup first?

Can you set database to No Recovery, apply transaction Logs, then set to Recover. Do that every hour? is that possible?

Reason for question is that the vendor is sending a full database backup each morning and instead of the large backup being sent they rather send T-Logs throughout the day.

Answer :

Can you set database to No Recovery, apply transaction Logs, then set to Recover. Do that every hour? is that possible?

No .. once you recover, you cannot apply transaction logs anymore. You have to start again.

What you can do is … apply transaction log and have the db in standby using ..
restore .. with Standby (available for read queries).

RESTORE LOG MyAdvWorks
   FROM MyAdvWorks_log1
   WITH STANDBY = 'c:undo.ldf'
GO

Alternatively, you can also ask the vendor to compress the backups to reduce size.

Leave a Reply

Your email address will not be published. Required fields are marked *