How to recover “Recover Pending” database in SQL Server

One of my colleague has reached out to me to recover a database which has a state as “Recover Pending”. He tried few methods explained in another blog post, however it was not a successful effort.

While I was trying to get the database with restore…with recovery, ended up with the below error message.

Msg 3148, Level 16, State 3, Line 2 This RESTORE statement is invalid in the current context. The ‘Recover Data Only’ option is only defined for secondary filegroups when the database is in an online state. When the database is in an offline stafte filegroups cannot be specified. Msg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally.

Finally, we used the below method to get the database online. Since its a testing environment and a small amount of data loss is not an issue for us, we used DBCC command to Repair_allow_data_loss. Caveat: We would not recommend to use this method for Production environment, probably, we need to restore the database from a valid backup until the point in time recovery.

ALTER DATABASE dbname SET EMERGENCY;
GO
ALTER DATABASE dbname set single_user
GO
DBCC CHECKDB (dbname, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
GO
ALTER DATABASE dbname set multi_user

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

One thought on “How to recover “Recover Pending” database in SQL Server”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s