Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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".

share|improve this question

1 Answer 1

up vote 5 down vote accepted

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.

share|improve this answer
    
I tried to read the doc, but it was way too much to take in to find the source of my problem. Thanks! –  Sam Sep 28 '10 at 10:26
    
@Sam I know it's a lot to digest but MSSQL backup and restore mechanism and crucial to the proper working of the DB and they are quite tricky, even if you limit yourself to the simplest scenarii. It's really up to you, but understanding MSSQL backups isn't something you can ignore, IMNSHO. –  Stephane Sep 28 '10 at 12:27
    
you are right, can you point me to some docs which explain the intricacies well? –  Sam Sep 29 '10 at 9:35
1  
@Sam Start with the integrated help. It isn't perfect but it does covers the basics. There are a few article on the net as well you can read to understand the basics (for instance, this one: simple-talk.com/sql/backup-and-recovery/sql-server-2005-backups). Finally, you can grab one of the many book on SQL server management: they all include a chapter on backup/restore. Personally, I have "Microsoft server 2008 management and administration" loaded on my kindle, while not stellar, it does contains most of the necessary explanations. –  Stephane Sep 29 '10 at 13:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.