1

Some background: I'm migrating my SQL Server DBs to a new machine and I wrote a few Powershell scripts to send/apply full backups and transaction logs asynchronously to the new server, but my question is about tail log backups. Eventually, I need my source DB to be unavailable to users so I can back up the tail log and apply it to the new instance.

What happens to active connections if I do something like this:

Backup-SqlDatabase -ServerInstance $serverInstance -Database $db -BackupAction Log -NoRecovery -Credential $credential -BackupFile $backupFile

Or similarly this:

BACKUP LOG <db> TO DISK = <path> WITH NORECOVERY

If I use the NORECOVERY option, I know my db will be unavailable, but will uncommitted transactions get rolled back immediately or will the db continue with active transactions and refuse new connections?

I'm curious to know what happens here, but I'd greatly appreciate any other relevant advice regarding my process. Thanks in advance!

Ranic
  • 111
  • 1
  • Where does it say the db is unavailable during a backup? Even WITH NORECOVERY I would expect the db to stay available. – TomTom Jun 17 '14 at 15:20
  • It's from [this](http://msdn.microsoft.com/en-us/library/ms186865.aspx) MSDN page: NORECOVERY Backs up the tail of the log and leaves the database in the RESTORING state. NORECOVERY is useful when failing over to a secondary database or when saving the tail of the log before a RESTORE operation. – Ranic Jun 17 '14 at 15:26
  • If you're migrating and expecting downtime, why not just do a full backup/restore or detach and move? – squillman Jun 17 '14 at 15:29
  • Then logically it is a rollback for all transactions. Connections SHOULD stay active as a connection is to the server (and the application can switch to another database via USE statement). – TomTom Jun 17 '14 at 15:32
  • squillman: We're trying to minimize downtime to around a minute or two, if possible. The file transfers happen to take a while since the full backups for some of the DBs are fairly large. – Ranic Jun 17 '14 at 15:38
  • But thanks, guys! Much appreciated! – Ranic Jun 17 '14 at 15:38

1 Answers1

0

Doing tail log backups will cause the database to be either inaccessible (NORECOVERY) or read-only (STANDBY). Which is expected if I need to pull out the active portion of the log file. If you did not do this activity could still write to the log file and you would loose consistency.

If you use STANDBY the database is simply marked as read-only and you can still grab the tail of the log. Another option for migrations that is a bit more common is just setup log shipping to your remote server, if possible. Setting up log shipping will allow data to kept up-to-date on your remote instance while you are still making final preparations. Then for the last step just do one final tail log backup, moving that smaller file to your remote server and then flip everything over.