Restore SQL Server database is hanging

restoresql-server-2008

I've made a Backup from a SQL Server 9.0.4053, copied the bak file to a different computer running SQL Server 10.0.2531 and tried to restore using

RESTORE DATABASE [MyDatabase] FROM  DISK = N'C:\Temp\MyDatabase.bak' WITH  FILE = 1,  NORECOVERY, REPLACE,  STATS = 10

This finishes quite quickly (the database is rather small), shortened output is something like this:

11 Prozent verarbeitet.
[...]
100 Prozent verarbeitet.
208 Seiten wurden für die 'MyDatabase'-Datenbank, Datei 'MyDatabase' für Datei 1, verarbeitet.
4 Seiten wurden für die 'MyDatabase'-Datenbank, Datei 'MyDatabase_log' für Datei 1, verarbeitet.
RESTORE DATABASE hat erfolgreich 212 Seiten in 1.014 Sekunden verarbeitet (1.631 MB/s).

After this is finished, the database shows up in SQL Server Management Studio as "being restored". And it keeps showing up like this for hours, without any progress.

The event log contains three information messages (roughly translated here):
1) "MyDatabase" is getting started.
2) "MyDatabase" is marked as RESTORING and has a status in which execution of restoration is not possible.
3) "MyDatabase" was restored successfully.

What is happening here? From all messages it seems the database was restored, but I can't access it, it keeps being marked as "Restoring".

Best Answer

You used the "NORECOVERY" flag which indicates to SQL server that you want to restore additional T-log backups. You should have used the "RECOVERY" flag instead (or nothing as this is the default).

To recover, either recover the missing T-logs or, if you don't want to restore any more logs, issue the following statement:

RESTORE DATABASE [MyDatabase] WITH RECOVERY

It should bring the DB online.

Read the integrated help on the restore command. It really contains information you cannot ignore.

Related Topic