2

I plan to migrate existing SQL Server 2008 DB to new server (has 2017 engine) with minimum down time. I will do full restore, then log restores (all with norecovery) on the new server up until the time of migration. I have tested the restore process using my plan which is this:

For the final log backup, my plan now is to set the database into single user mode, then do a "regular" log backup. Do I need to do the "tail" log backup (add NORECOVERY option) while taking this last log backup? if so, then why? If the DB is set into single user mode which means that no one except me can access database, can I omit using NORECOVERY? I use Ola's scripts to do log backups, and would rather just run my "regular" log backup job after the DB is in single user mode, copy that backup to the new server, and do the final restore.

Bottom line - is there a difference between log backup in single user mode and a tail log backup?

  • 1
    I did something similar once. But I created a "log shipping" task, then after the last log was moved, we just turned off the software for some seconds, changed connection string and then we turned on the system again. It took like 10 seconds to migrate a 4TB database. It was aewsome.( damm I hate english past tenses). – Racer SQL Oct 8 at 13:21
  • Have you considered setting the database to READ_ONLY before your final log backup - then setting the restored database back to read_write? I will say that setting the database to single-user-mode can sometimes be problematic because some other process steals that single connection and you're scrambling to get your task done with a minimum of downtime. – Scott Hodgin Oct 8 at 13:24
  • You could set up mirroring for the database and then just perform manual failover to the new instance. – Denis Rubashkin Oct 8 at 13:33
0

All that NORECOVERY does is to make it impossible to generate any log records after this backup was produced. If you feel that you have sufficient control to handle that you don't miss any work done on the "old" side, then by all means - you can skip this option.

This is from a technical standpoint. You might consider it being a bonus that it is impossible to do any further modifications, or that the old database is in restoring state. But that is more of a management thing.

  • 1
    >>>All that NORECOVERY does is to make it impossible to generate any log records after this backup was produced.<<< This sounds like NORECOVERY permits any readonly access to the database (no log generation in this case), but it's not true: database in restoring state cannot be accessed at all – sepupic Oct 8 at 13:52
  • @sepupic True, I should have been more specific. I was referring to what type of backup it performs. There is nothing special about the type of log backup that you produce when using NORECOVERY compared to not using that option. It is just a guarantee, that it is impossible to produce any more log records. – Tibor Karaszi Oct 9 at 6:49
0

If the DB is set into single user mode which means that no one except me can access database, can I omit using NORECOVERY?

If you are connected to a different database, or when you eventually disconnect any other user can access the SINGLE_USER database. A Tail Log backup leaves the database in the RESTORING state. This guarantees that no users can accidentally read or write data to the old database after the cut-over.

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.