Friday, September 8, 2017

How to troubleshoot database in recovery mode?

When you have a database in recovery mode (because of a system or corruption error, or a failed mirrored instance) and you want to put it online (or offline) again to do you work, if you issue the following command:

ALTER DATABASE <DB> SET ONLINE

You have encountered this error:

Msg 5052, Level 16, State 1, Line 1
ALTER DATABASE is not permitted while a database is in the Restoring state.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.


To solve this error, you just have to simply issue the following command:

RESTORE DATABASE <DB> WITH RECOVERY;

Done! right? if you have a mirror database... NO.
Since the DB is involved in mirroring, this does not work, and you obtain the error:

Msg 3104, Level 16, State 1, Line 1
RESTORE cannot operate on database '<DB>' 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 1
RESTORE DATABASE is terminating abnormally.


Ok, don't panic, there is a solution, first you must remove the DB from mirroring with the following command:

ALTER DATABASE <DB> SET PARTNER OFF  

After that, you are good to go! you can run the restore database command without issues!

Sources:
https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql
https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/remove-database-mirroring-sql-server


No comments:

Post a Comment