DB Restore Status Remains

Compresses, encrypts, secures and monitors SQL Server backups.

Moderators: JonathanWatts, Chris Auckland, eddie davis, Colin Millerchip, Brian Harris, james.billings, RBA, petey

DB Restore Status Remains

Postby noob2013 » Wed Jan 30, 2013 3:19 pm

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?
noob2013
 
Posts: 19
Joined: Wed Jan 30, 2013 3:11 pm

Postby chriskelly » Wed Jan 30, 2013 7:51 pm

Can you post the restore script?
Chris Kelly
Technical Support Engineer
chriskelly
 
Posts: 330
Joined: Mon Apr 19, 2010 1:44 pm
Location: Cambridge, UK

Postby noob2013 » Wed Jan 30, 2013 8:47 pm

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 "
noob2013
 
Posts: 19
Joined: Wed Jan 30, 2013 3:11 pm

Postby ChrisAVWood » Wed Jan 30, 2013 9:51 pm

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
English DBA living in CANADA
ChrisAVWood
 
Posts: 308
Joined: Tue Dec 18, 2007 6:18 pm
Location: Edmonton, Alberta, CANADA

Postby noob2013 » Wed Jan 30, 2013 10:13 pm

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.
noob2013
 
Posts: 19
Joined: Wed Jan 30, 2013 3:11 pm

Postby ChrisAVWood » Wed Jan 30, 2013 10:17 pm

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
English DBA living in CANADA
ChrisAVWood
 
Posts: 308
Joined: Tue Dec 18, 2007 6:18 pm
Location: Edmonton, Alberta, CANADA

Postby ChrisAVWood » Wed Jan 30, 2013 10:19 pm

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

Chris
English DBA living in CANADA
ChrisAVWood
 
Posts: 308
Joined: Tue Dec 18, 2007 6:18 pm
Location: Edmonton, Alberta, CANADA

Postby noob2013 » Wed Jan 30, 2013 10:24 pm

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
 
Posts: 19
Joined: Wed Jan 30, 2013 3:11 pm

Re:

Postby noob2013 » Wed Jan 30, 2013 10:32 pm

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

Chris


I'm going to try restore with recovery.
noob2013
 
Posts: 19
Joined: Wed Jan 30, 2013 3:11 pm

Postby ChrisAVWood » Fri Feb 01, 2013 6:59 pm

Did the RECOVERY option work?
I am curious.

Chris
English DBA living in CANADA
ChrisAVWood
 
Posts: 308
Joined: Tue Dec 18, 2007 6:18 pm
Location: Edmonton, Alberta, CANADA

Postby noob2013 » Wed Apr 10, 2013 5:52 pm

Sorry for the late reply.
Yes it did.
noob2013
 
Posts: 19
Joined: Wed Jan 30, 2013 3:11 pm


Return to SQL Backup 7

Who is online

Users browsing this forum: No registered users and 1 guest