Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question

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

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

2 Answers 2

up vote 4 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.

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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