4

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)
Improve this question

2 Answers 2

Reset to default
7

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.

Improve this answer
4
  • 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, 2017 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 Shah
    Apr 21, 2017 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, 2017 at 16:50
  • @TomStickel refer learn.microsoft.com/en-us/sql/database-engine/… . HTH.
    – Kin Shah
    Apr 22, 2017 at 20:47
2

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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