Sql-server – How to restore SQL Server database with mirroring

sql server

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)

Best Answer

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.