April 29, 2014

I was working on a Sql server database and since there were few issues, I copied the backup of the database to run some queries. Since I don't use it often and the database is huge, I saved the mdf file in an external hard drive but then I notice that every now then when I open Sql Server  Management Studio, the database fails to open with the message "Recovery Pending".

To solve this issue, I can simply reload the database from the backup but then I will need access to the backup file every time. Alternatively, I can write a few queries to solve this issue.

To start with, I will set the database to Emergency state and change it to a Single User mode.

  1. USE master
  2. GO
  3.  
  4. --run 1
  5. ALTER DATABASE [db_name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  6. GO
  7. ALTER DATABASE [db_name] SET EMERGENCY
  8. GO

 

Next, check if the database has errors using CHECKDB command.

  1. --run 2
  2. DBCC CHECKDB WITH NO_INFOMSGS


This is the step where you need to decide what to do with the errors that you have received. Depending on your scenario, you can repair the database with or without any data loss.

  1. --run 3
  2. --no data loss
  3. DBCC CHECKDB ('db_name', REPAIR_REBUILD);
  4.  
  5. --potential data loss
  6. DBCC CHECKDB ('db_name', REPAIR_ALLOW_DATA_LOSS);

 

Finally, change the database mode to multi user mode.

  1. --run 4
  2. ALTER DATABASE [db_name] SET MULTI_USER
  3. GO

 

Your database should be running again now.

0 comments:

Reference: Shahed Kazi at AspNetify.com