2

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

Improve this question

1 Answer 1

Reset to default
6

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.

Improve this answer
4
  • I tried to read the doc, but it was way too much to take in to find the source of my problem. Thanks! Commented Sep 28, 2010 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. Commented Sep 28, 2010 at 12:27
  • you are right, can you point me to some docs which explain the intricacies well? Commented Sep 29, 2010 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. Commented Sep 29, 2010 at 13:20

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.