Home > DeveloperSection > Forums > SQL Server: Database stuck in “Restoring” state
Manoj Bhatt
Manoj Bhatt

Total Post:153

Points:1079
Posted on    May-15-2013 7:58 AM

 MSSQL Server MSSQL Server 
Ratings:


 1 Reply(s)
 8432  View(s)
Rate this:
Hi Expert!

I backed up a data as following

BACKUP DATABASE MyDatabase
TO DISK = 'MyDatabase.bak'
WITH INIT --overwrite existing

And then tried to restore it:

RESTORE DATABASE MyDatabase
   FROM DISK = 'MyDatabase.bak'
   WITH REPLACE --force restore over specified database
And now the database is stuck in the restoring state.

Some people have theorized that it's because there was no log file in the backup, and it needed to be rolled forward using:

RESTORE DATABASE MyDatabase
WITH RECOVERY
Except that, of course, fails:

Msg 4333, Level 16, State 1, Line 1
The database cannot be recovered because the log was not restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
And exactly what you want in a catastrophic situation is a restore that won't work.

The backup contains both a data and log file:

RESTORE FILELISTONLY 
FROM DISK = 'MyDatabase.bak'

Logical Name    PhysicalName
=============   ===============
MyDatabase    C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDatabase.mdf
MyDatabase_log  C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\MyDatabase_log.LDF

Please help me!
Thanks in advance! 


AVADHESH PATEL

Total Post:604

Points:4228
Posted on    May-15-2013 8:46 AM

Hi Manoj!

You need to use the WITH RECOVERY option, with your database RESTORE command, to bring your database online as part of the restore process.

This is of course only if you do not intend to restore any transaction log backups, i.e. you only wish to restore a database backup and then be able to access the database.

Your command should look like this,

RESTORE DATABASE MyDatabase
   FROM DISK = 'MyDatabase.bak'
   WITH REPLACE,RECOVERY

You may have more success using the restore database wizard in SQL Server Management Studio. This way you can select the specific file locations, the overwrite option, and the WITH Recovery option.

I hope it helpful to you!


Don't want to miss updates? Please click the below button!

Follow MindStick