I have SQL Server with mirroring.

I am trying to restore the database using the command:

USE [master]
ALTER DATABASE [CentralStorage] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [CentralStorage] FROM  DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\XXX.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5
ALTER DATABASE [XXX] SET MULTI_USER

I tried the followings:

  • I set the "remove tail log"
  • I set the "close connections"
  • override existing database.

I get the error:

Msg 1468, Level 16, State 1, Line 2
The operation cannot be performed on database "XXX" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
Msg 5069, Level 16, State 1, Line 2
ALTER DATABASE statement failed.
Msg 3104, Level 16, State 1, Line 3
RESTORE cannot operate on database 'XXX' because it is configured for database mirroring or has joined an availability group. If you intend to restore the database, use ALTER DATABASE to remove mirroring or to remove the database from its availability group.
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.

Additional info:

  • Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)

migrated from stackoverflow.com May 27 '13 at 23:19

This question came from our site for professional and enthusiast programmers.

up vote 6 down vote accepted

You cannot restore a database that is involved in Mirroring without breaking/removing Mirroring.

To remove mirroring

ALTER DATABASE <database_name> SET PARTNER OFF

ON the mirror database

RESTORE DATABASE <database_name> WITH RECOVERY;

Some useful links for mirroring can be found here.

  • Be nice to quickly go the reverse way as well. Example some of us want some peace of mind in a maintenance windows and want to recovery and verify the data, but then quickly put the mirror db back to with no recovery along with that re-enable mirror on the principal. I believe have to detach or take offline then attach or put back online with no recovery? – Tom Stickel Apr 20 '17 at 21:22
  • You cannot do that ... once you put the db online, it breaks the log chain. You can create a snapshot on mirror and verify the data and drop the snapshot. This way you dont touch mirroring, just query the snapshot. – Kin Apr 21 '17 at 13:28
  • ok, i need to look at that, any chance you could edit your answer to show that , or have a link to an article ? thx – Tom Stickel Apr 21 '17 at 16:50
  • @TomStickel refer docs.microsoft.com/en-us/sql/database-engine/database-mirroring/… . HTH. – Kin Apr 22 '17 at 20:47

Restore on the principle isn't allowed. Like the error message says, remove mirroring prior to the restore.

reference: http://technet.microsoft.com/en-us/library/ms191235(v=SQL.105).aspx

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.