July 18, 2014

Datebase-Unavailable database:testdb is unavailable(suspect,offline,recovering etc) in SQL

SQL Error: Database-Unavailable database  :testdb is unavailable(suspect,offline,recovering etc)

1.If the database is offline, the approach should be to bring the database online by either from GUI or from query : ALTER DATABASE <database_name> SET ONLINE and if it still doesn't come up..we should check the logs for the reasons..and based on the outcome we have to proceed accordingly.

2.If the Database is offline, then try to make the database online using the script ALTER DATABASE <database name> SET ONLINE.
If it is not recover the database to online, then check in event viewer for possible error, if error is 17204--then data file has damaged,if error is 17207--- then log file has damaged,If you see this error then the database is there in suspect state.
To Recover the suspect state database u need to follow steps as below:
If data file damaged:
1. Take the tile log back up.
2.Restore Full back up with no recovery.
3.Restore Differential & Log Backups with no recovery(If you have).
4.Restore tail log backup with recovery, then database will become online.
If Log file damaged:
1.Put the database in single user mode.Using the following command.ALTER DATABASE <database_name> SET SINGLE_USER
2.Put the data base in emergency mode using ALTER DATABASE <database_name> SET EMERGENCY
3.Run the DBCC CHECKDB Command with require repair level as follows
4.Then put the data base into multi user mode.
ALTER Database <database_name> SET MULTI_USER
Then database will become online.