SQL Server: Database stuck in "Restoring" state
Posted
by Ian Boyd
on Stack Overflow
See other posts from Stack Overflow
or by Ian Boyd
Published on 2009-02-06T16:21:45Z
Indexed on
2010/03/13
18:45 UTC
Read the original article
Hit count: 684
i backed up a data:
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
© Stack Overflow or respective owner