DB Restore Status Remains

noob2013

Using SQL Backup 7.2.1.4

Restored database from a backup file from another with "Overwrite existing database" option and "Kill any existing connections to the database" checked.

Anyway, it's a big database, but the restore has been running for 2 days now. And the status is still in Restoring ...

When I look at Activity History tab below, it says Successful; and when I double click the history, it says "There are no errors or warnings associated with this restore".

This is the final line of the Activity Log
1/28/2013 9:21:42 AM: SQL Backup process ended.

But the status is still in Restoring... and I cannot connect to it using SSMS2008. I am getting the following error

Msg 927, Level 14, State 2, Line 5
Database 'abc' cannot be opened. It is in the middle of a restore.

Edit: Have since restarted SQL Backup Agent service on the server. Didn't help ...

How do I fix this issue?

chriskelly

Can you post the restore script?

noob2013

Used GUI - but, this is in the Activity Log - db name changed for forum posts.


SQL Backup log file 7.0.5.1

-SQL "RESTORE DATABASE [ABC_ARCHIVE]
FROM DISK = '\\servername\foldername\foldername\ABCPROD_FULL_20130109.bak'
WITH NORECOVERY, DISCONNECT_EXISTING,
MOVE 'ABCPROD_Data.MDF' TO 'F:\Datafiles\ABC_ARCHIVE_Data.MDF',
MOVE 'ABCPROD_Log.LDF' TO 'F:\Logfiles\ABC_ARCHIVE_Log.LDF',
REPLACE "

ChrisAVWood

Did you check this message out? Look for Error trying to Restore DB & Move files with NORECOVERY in this forum. It looks pretty similar.

HTH

Chris

noob2013

If you meant this thread
http://www.red-gate.com/messageboard/vi ... norecovery

the thread OP got an error. I do not have an error. In fact, the Activity Log reports restore process completed successfully. However, on both SQL Backup and SSMS, that particular database has the status "restoring ...". And it has been there for 2 days; but the SQL Backup suggests the restore completed successfully in about 1 hour and 27 mins.

ChrisAVWood

Have you looked at the actual size of the files after the 'successfull' restore? In this case the files will be allocated and then filled by the backed up data.

I am sure something will be in the logs that show what has happened.

Chris

ChrisAVWood

NORECOVERY means it is not available for use. You need to use RECOVERY.

Chris

noob2013

Yes I did. The ABC_ARCHIVE database data file is 771GB and log file is 18 GB - which is the same size as the ABCPROD database on the day the backup was taken.

To rule out the hard disk space (at first I thought it was the hard disk space issue failure); I still have 2.8 Terabytes on that drive where the data & log files are located.

noob2013

ChrisAVWood wrote:

NORECOVERY means it is not available for use. You need to use RECOVERY.

Chris

I'm going to try restore with recovery.

ChrisAVWood

Did the RECOVERY option work?
I am curious.

Chris

noob2013

Sorry for the late reply.
Yes it did.