How to recover restoring database in SQL Server

At times, we end up with situations like databases suddenly goes into restoring state and becomes not accessible. Few cases those are valid like mirror database in a database mirroring or removed database from AlwaysOn etc. Today, we are not discussing about those valid cases, but as outcome of an incident or unfortunate db state change.

If you happened to see a database in restoring and you want to get it online, you can simply restore with recovery as below.

use master
restore database << dbname >> with recovery

In most cases, the db would get online immediately. Sometimes, it might give you an error message as below:

Msg 4333, Level 16, State 1, Line 3 The database cannot be recovered because the log was not restored. Msg 3013, Level 16, State 1, Line 3 RESTORE DATABASE is terminating abnormally.

If you result with the above error message, then we should understand that the particular database is not restored in a proper way. May be. the restore process would have terminated due to network issues or cancelled the restore part way through etc. Such database cannot be brought online. The best way would be to drop the database and restore from the backup.

restore database << dbname >> from disk ='<filepath>\<backup filename>'

It is important to have a valid database available for us to quickly get this db online. So ensuring the backup is important as always, hope we all will have a good backup plan in place by default, if not, that should be the first priority item.

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

3 thoughts on “How to recover restoring 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