page contents

About the Post

Author Information

HOW TO FIX: RESTORE cannot operate on database because it is configured for database mirroring.

Recently a friend of mine asked me for help when he was not able to get a database out of restoring mode. When he tried to restore the database to the latest backup, he got this error message below:

Msg 3104, Level 16, State 1, Line 1

RESTORE cannot operate on database ‘VirtualManagerDB’ because it is configured for database mirroring. Use ALTER DATABASE to remove mirroring if you intend to restore the database.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

He even took a screenshot of this error when he tried using the GUI to recover from this error message. (This is the same error message as the text above)

As you can tell here, he was in a bit of a trouble here. He had a database that was not sync and therefore was not available to fulfill requests for data, since it is offline. To make matters worse, he decided to break the mirror and re-setup mirror, but that did not help either. Again, this issue remains, because the database was in need of recovering data from a backup and you need to resolve that issue before moving on to the next problem.

Side note: I recently learned this lesson and wanted to pass it along.

Before we start, I want to digress for a little vocabulary lesson. “Backup” is a noun. “Back up” is a verb. You don’t “backup a database.” You “back up a database”. When you “back up” a database, it creates a “backup” of the database –Robert L. Davis (Blog | Twitter)

So, I advised my co-worker to perform the following steps to resolve this issue on the principal partner:

  • Break the mirror by typing:
    ALTER DATABASE [VirtualManagerDB] SET PARTNER OFF
  • Restore your Database from your latest full backup by typing: 
RESTORE DATABASE [VirtualManagerDB] FROM
DISK = N'E:\MSSQL10.MSSQLSERVER\MSSQL\BAK\virtualmanagerdb.bak'

WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

GO
  • Restore your Database from your latest differential backup by typing:
    RESTORE DATABASE [VirtualManagerDB] FROM DISK = N'E:\MSSQL10.MSSQLSERVER\MSSQL\BAK\virtualmanagerdb.dif'
    
    WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
    
    GO
  • Restore your transaction logs from your latest backups using norecovery by typing: (More than likely you will be playing back a multiple set of logs)
    RESTORE LOG [VirtualManagerDB] FROM DISK = N'E:\MSSQL10.MSSQLSERVER\MSSQL\BAK\virtman_2012-08-28-23TO70001.trn'
    
    WITH FILE = 1, NORECOVERY, NOUNLOAD, STATSÂ = 10
    
    GO
  • Recover the database to bring it online by typing: 
    RESTORE DATABASE [VirtualManagerDB] WITH RECOVERY;
    
    GO

At this point, the database should be fully restored and back online, but you will still need to re-create the mirror to get your high availability back. You will need to perform these tasks on the mirror partner:

  • Copy all of the backups (you just restored, full, dif, logs, etc) from the principal server to the mirror server
  • Open SQL Server Management Studio (SSMS) and you will see mirror database is disconnected.
  • Verify your current backup does restore your database properly by testing it on another system. (Very important to NOT assume it is working)
  • Before proceeding to the next step, I would take a minute to review this blog from Robert L. Davis (Blog | Twitter) on SQL DBA Week Recovering Lost Data
  • Robert talks about the ability to verify that your database restores properly and how it can be queried before you enable it for mirroring.
  • Restore your Database from your latest full backup by typing:
RESTORE DATABASE [VirtualManagerDB] FROM DISK = N'E:\MSSQL10.MSSQLSERVER\MSSQL\BAK\virtualmanagerdb.bak'

WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

GO
  • Restore your Database from your latest differential backup by typing: 
    RESTORE DATABASE [VirtualManagerDB] FROM DISK = N'E:\MSSQL10.MSSQLSERVER\MSSQL\BAK\virtualmanagerdb.dif'
    
    WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
    
    GO
  • Restore your transaction logs from your latest backups using norecovery by typing: (More than likely you will be playing back a multiple set of logs) 
    RESTORE LOG [VirtualManagerDB] FROM DISK = N'E:\MSSQL10.MSSQLSERVER\MSSQL\BAK\virtman_2012-08-28-23TO70001.trn'
    
    WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
    
    GO
  • Restore the final log file in “STANDBY mode” instead of specifying RECOVERY or NORECOVERY. (Example below)
    RESTORE LOG [VirtualManagerDB] FROM DISK = N'E:\MSSQL10.MSSQLSERVER\MSSQL\BAK\virtman_2012-08-28-23TO70001.trn'
    
    WITH FILE = 1, STANDBY, NOUNLOAD, STATS = 10
    
    GO
  • This makes the database go into a read-only mode and you can query against it for testing.
  • If all went well with your testing, then re-run that final log restore again with the NORECOVERY flag instead of the STANDBY flag to configure it for mirroring. 
    RESTORE LOG [VirtualManagerDB] FROM DISK = N'E:\MSSQL10.MSSQLSERVER\MSSQL\BAK\virtman_2012-08-28-23TO70001.trn'
    
    WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
    
    GO

Now you are ready to re-create the mirror and get your system back online.

Tags: , , , , ,

Comments are closed.

Copy Protected by Chetan's WP-Copyprotect.