SQL Server – Restore Log File Backups to Mirror Database

disaster recoverymirroringrestoresql server

Purely a theoretical question as I've been considering possible DR scenarios…

In this scenario:

The link between a principle and a mirror server becomes unavailable for a period of time (e.g. network outage)
Incremental log backups are still being taken during this time
Then the principle database fails and is unrecoverable

Is it possible to restore the incremental log backups onto the mirror to bring it up to date and back online, or would you need to start from scratch with a restore of a full backup?

Depending on the size of the database the difference could be hours of downtime so it would be nice to know. I'm predominantly interested in SQL Server 2008 R2 and 2012 but other people may be interested in other versions if there's a difference.

Best Answer

Short answer, yes.

Longer answer, you'd need to remove database mirroring without bringing the database online. Then it's just a database in NORECOVERY so you can then restore transaction logs to it. Then bring it online and use it as normal.

Related Solutions

Sql Anywhere 11: Restoring incremental backup failure

Summarizing the information linked to in the post above....

The command you have used is for a live backup but what you want is an incremental backup. The approach you want to use (per this blog) uses somewhat different switches. You want to use the -n -t -x switches, and the -o to specify a log file. So the command ends up something like:

dbbackup -n -t -x -c "eng=ServerName.DbName;uid=dba;pwd=sql;links=tcpip(host=ServerName)" -o "c:\backup\backup_log.txt"

SQL Server – Troubleshooting Mirror Failure After Windows Updates

We had to bring MS in on this one via a paid support call. After a week of dumps, xperf analysis, driver updates, playig with Virtual infrastructure we ruled out all the basics (or so we thought) - over a week later we found the problem - there was over 500,000 sys.server_event_notifications defined - these all looked as follows:

name    object_id   parent_class    parent_class_desc   parent_id   create_date modify_date service_name    broker_instance creator_sid principal_id
SQLWEP_E0B9EA66_1E73_45B7_B528_75166DC7A15D 337640  100 SERVER  0   2013-06-14 01:56:27.077 2013-06-14 01:56:27.077 SQL/Notifications/ProcessWMIEventProviderNotification/v1.0  706DF30B-6BD5-458B-85A4-F1C174263ADE    0x010100000000000512000000  259
SQLWEP_6492D14E_99D2_49F2_9CD9_43F4B7DC7F63 338711  100 SERVER  0   2013-02-12 17:50:55.897 2013-02-12 17:50:55.897 SQL/Notifications/ProcessWMIEventProviderNotification/v1.0  706DF30B-6BD5-458B-85A4-F1C174263ADE    0x010100000000000512000000  259
SQLWEP_C9871B51_F866_48A6_862E_08EC4F92DAFA 351457  100 SERVER  0   2013-03-06 02:04:18.070 2013-03-06 02:04:18.070 SQL/Notifications/ProcessWMIEventProviderNotification/v1.0  706DF30B-6BD5-458B-85A4-F1C174263ADE    0x010100000000000512000000  259
SQLWEP_0E5D8FAF_1A9C_43FA_8EDF_A5713A8C7825 362061  100 SERVER  0   2014-02-08 04:57:17.217 2014-02-08 04:57:17.217 SQL/Notifications/ProcessWMIEventProviderNotification/v1.0  706DF30B-6BD5-458B-85A4-F1C174263ADE    0x010100000000000512000000  259
SQLWEP_712ED252_E529_4022_9EA3_C785BAEB8147 364203  100 SERVER  0   2013-04-05 15:52:11.190 2013-04-05 15:52:11.190 SQL/Notifications/ProcessWMIEventProviderNotification/v1.0  706DF30B-6BD5-458B-85A4-F1C174263ADE    0x010100000000000512000000  259
SQLWEP_E2D31BC2_E416_4D12_8B4A_88F4183FE021 374807  100 SERVER  0   2014-03-15 13:31:25.390 2014-03-15 13:31:25.390 SQL/Notifications/ProcessWMIEventProviderNotification/v1.0  706DF30B-6BD5-458B-85A4-F1C174263ADE    0x010100000000000512000000  259
SQLWEP_7B389ACB_EA1F_434C_8B2A_7B93D4D0F098 376949  100 SERVER  0   2013-05-12 05:02:12.450 2013-05-12 05:02:12.450 SQL/Notifications/ProcessWMIEventProviderNotification/v1.0  706DF30B-6BD5-458B-85A4-F1C174263ADE    0x010100000000000512000000  259
SQLWEP_4A828CDE_D72D_4277_97C0_030686F86377 378020  100 SERVER  0   2013-01-21 16:49:04.780 2013-01-21 16:49:04.780 SQL/Notifications/ProcessWMIEventProviderNotification/v1.0  706DF30B-6BD5-458B-85A4-F1C174263ADE    0x010100000000000512000000  259
SQLWEP_4EA3C2AD_ACCE_4A66_83E9_0676D2250C34 387553  100 SERVER  0   2014-04-24 20:26:31.053 2014-04-24 20:26:31.053 SQL/Notifications/ProcessWMIEventProviderNotification/v1.0  706DF30B-6BD5-458B-85A4-F1C174263ADE    0x010100000000000512000000  259
SQLWEP_6EA3C0FB_6EDA_4B9D_A3FE_D2B07799212C 389695  100 SERVER  0   2013-06-16 18:07:06.043 2013-06-16 18:07:06.043 SQL/Notifications/ProcessWMIEventProviderNotification/v1.0  706DF30B-6BD5-458B-85A4-F1C174263ADE    0x010100000000000512000000  259

These had all been created gradually over 5 years, what they are, where they come from, no one could say & MS couldn't find any related alerts to justify them either. Dropping 1 notification took 4 seconds so we would have been looking at 22 days to drop them all - suffice to say we ended up nuking the SQL instance & re-installing SQL from scratch.

Best Answer

Related Solutions

Sql Anywhere 11: Restoring incremental backup failure

SQL Server – Troubleshooting Mirror Failure After Windows Updates

Related Question