0

I have a customer with a gigantic SQL Server 2005 database (almost 400GB including the log file) and a table has been unintentionally dropped. We are trying to restore to a temporary database then we will copy the data across from the missing table.

The disk had around 450GB free space. As soon as I started the restore, SQL allocated the 400GB for the new temporary database leaving 50GB free. The restore then ran continuously without error for around 23 hours. At some point near the end (I don't know when) it failed with an error along the lines of "error not found", which I assume is related to disk space because the disk only had 8GB free due to another file being created in the meantime.

The database is now sitting in state of "Restoring...".

I guess I can delete this database and start over again, but my client is jumping up and down because they have no database and can't wait another 23 hours - and even then, will it work again!?

My question is - can I get the database out of the "Restoring..." state at least just to see if it has the data I need - as I only need the data out of one of the tables. I have already tried:

RESTORE DATABASE xxx WITH RECOVERY

But that came back with the error

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.

Any help much appreciated.

voretaq7
  • 79,879
  • 17
  • 130
  • 214
Nick Thompson
  • 1
  • 1

2 Answers2

2

If you need to restore a table with its data from a backup file, you might try

  1. Devart Schema Compare for SQL Server to restore the table structure itself

  2. Devart Data Compare for SQL Server to copy table rows.

You DON'T HAVE TO RESTORE a dabase from a backup file - those products directly read databases from backup files.

Alternatively you might try Red Gate's SQL Virtual Restore to attach a backup file to SQL Server as a real databae without its restoring!

Optillect Team
  • 121
  • 2
0

Free up additional disk space on the server and try:

RESTORE DATABASE xxx FROM DISK = 'zzz' WITH RESTART

This should attempt to start the restore from the point of failure, and may result in a complete restore if there is sufficient disk space.

When restoring a database, SQL Server will first create and allocate space for the data file, which includes writing empty pages to disk for that file to claim the space (the file is created 'sparse' and then populated). Once allocated, space is then allocated in the same way for the log file. Only once both of these operations have completed does the server begin to restore the data into these files. For very large databases, this can take a significant amount of time - within the Management Studio UI, the restore will show 0% complete until after this process has completed and the first data is restored.

It sounds like the allocation of space for the log file failed, and the restore aborted. Usually this would result in both the log and data files being deleted from disk and the space being reclaimed - it's worth looking in your system log files to see if you can determine why the restore did not abort normally.

Mike Insch
  • 1,254
  • 8
  • 10