How to set SQL Server Database 'Recovery pending state' in to 'Online state'

In case database is showing in 'Recovery Pending state, it means that recovery cannot be started. Until the cause is get fixed, recovery cannot run and the database cannot come online. You can check the state of the database by using sys.databases catalog view. Here are many reasons of this SQL Server behavior, the most common one is the log file is missing or corrupt. The other ones are:

• MDF file get corrupted.
• No enough space available for the SQL Server to recover the database during startup.
• Database cannot be opened due to inaccessible files or insufficient memory or disk space.
• Database files are being held by operating system. 
• Sudden SQL Server Shutdown, Power failure or a Hardware failure.

First you should check following two points:

1.First you should check that MDF & LDF file is exist or not? 

2.Is Drive is available? 

If so then you should drop the DB and restore from backup. If you don't have backup then use following steps to resolve the issue:

• you should try stopping SQL Service
• Free up some space, make sure there's enough space there for the recovery to complete
• Now Restart the SQL service to get back SQL database online again. 

After using all following steps if you are unable to resolve this error, it means that the error occurs due to log file corruption. In this case, you have last resort that is Switch the database to emergency mode, then to single user mode, run DBCC CHECKDB with the repair_allow_data_loss option. This command will lose data & have chances that may not work. If it doesn't work, there are no further alternatives, so try this command when no other options are available. 

Hope the article will help you to get back your database in online state!!

»»  Read More...

How to resolve error: “mdf is not a primary database file”

I want to share my personal experience on SQL Server Database. One day I was trying to set my SQL database online with command:

ALTER DATABASE mydb SET online

& suddenly I got an error message:

Msg 5171, Level 16, State 1, Line 1
E:\Data\mydb_log.ldf is not a primary database file.
Msg 5171, Level 16, State 2, Line 1
E:\Data\mydb.mdf is not a primary database file.
File activation failure. The physical file name "E:\Data\mydb.mdf" may be incorrect.
Msg 945, Level 14, State 2, Line 1
Database 'mydb' cannot be opened due to inaccessible files or insufficient memory or disk space.  See the SQL Server errorlog for details.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.

After that I do research on this error & I found this error occurs in many situations like :

1) When you are trying to attach your SQL database files with windows then also you get same error message. Mostly this error occurs in SQL Server 2005.

2) When you are trying to set your database online then also you get same error message.

Cause: 

The main cause of this error is that your mdf file get corrupt.

Solution: 

There is four solutions to resolve this error:

1. First you should check the database by this command “DBCC CheckDB”. It shows the details of the database then you can find the problem & easily resolve it.

2. You can tried to add the file using the command sp_attach_single_file_db. 

3. Try to restore database from a bak file.

4. It is possible that the database may have been corrupted when it was downloaded & “DBCC CheckDB” reflects damages in SQL server MDF file. You also don't have updated bak file. In this case you should try SQL Recovery Software. 

Note: If data get overwrite no one can repair it. 

• When you find that your mdf file has been corrupt, don't perform any activity on the file. If there is 80% chances to resolve this error it will becomes 10%. because may be your data get overwrite from your activities & you will never get back your data. 
• Before purchasing any SQL recovery software, first download its demo version & check the preview of your recovered database after that purchase it.

»»  Read More...