Switch Disaster Recovery from Log Shipping to Mirroring

I recently inherited a system on SQL 2005 that was using Log Shipping to sync data to a secondary data center. I am not sure why they decided to use Logs rather than Mirroring, but after a bit of digging I found no reason not to update the system to use mirroring, which is a much easier system to manage and in my experience much more efficient.

It occurred to me though, that I wasn’t sure if I would have to completely re-initialize the database on the secondary once I turned Log Shipping off, after all, it would be up to date from a log backup point of time, but the database would be in Stand By / Read only mode once I switched the log shipping off. Happily I found that I did not have to ship a full backup down and start from scratch, following the steps below I could switch from Log shipping to Mirroring in a couple of minutes:

  1. Disable Backup Job in Log Shipping configuration.
  2. Make sure the latest logs had been copied and applied to our secondary to bring it up to date.
  3. Turn off Log Shipping at the principal under Database Properties.
  4. Restore the database on the secondary using the command: RESTORE DATABASE [DBName] WITH NORECOVERY;
  5. Enable Mirroring at the Principal using the wizard or scripts.
  6. Configure New Log Backup schedule.

The last part is important, previous to the configuration change the logs were backed up by your Log Shipping Configuration. Once this was deleted you are without log backups.

This entry was posted in SQL Server and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s