Friday, July 22, 2011

RESTORE cannot operate on database because it is configured for database mirroring.

Msg 3104, Level 16, State 1, Line 1
You come to know mirror database not sync while, If you decided to break the mirror and re-setup mirror.

1. ALTER DATABASE AdventureDB SET PARTNER OFF ( You remove the mirror first)
2. Take the full backup from the principal server copy over to mirror
3. You will see mirror database is disconnected

RESTORE DATABASE AdventureDB WITH RECOVERY

Msg 3104, Level 16, State 1, Line 1
RESTORE cannot operate on database 'AdventureDB' 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.

You found this above strange error.

4. You have reboot the SQL Server. Make sure sql server services are running after re-boot.
 -- Mirror Server restarted
5. Now re-run the command on mirror server
RESTORE DATABASE AdventureDB WITH RECOVERY
RESTORE DATABASE successfully processed 0 pages in 20.281 seconds (0.000 MB/sec).
-- Database is now accessible on mirror servers

6. Start restore database backup Full, Logs with norecovery mode
RESTORE DATABASE AdventureDB
FROM DISK = 'D:\MSSQL\Backup\AdventureDB.BAK' WITH NORECOVERY
 -- Restoring now
RESTORE DATABASE AdventureDB
FROM DISK = 'D:\MSSQL\Backup\AdventureDB.Log' WITH NORECOVERY, REPLACE, STATS = 5
If you have more than one log backup do the same
7. Ready resetup the mirror again.