March 11, 2011 at 1:17 pm
Hello,
The restore gets to 100%, but never actually finishes. I've used RESTORE VERIFYONLY to check the backup. It reports "The backup set on file 1 is valid." I don't see anything in the log files that indicates an error. Also, Ive tried deleting the .mdf and .ldf files before the restore and have the same experience.
The restore code I am using: RESTORE DATABASE [mydatabase] FROM DISK = g:\backups\mydatabase.bak' WITH FILE = 1, MOVE N'mydatabase_Data' TO N'E:\mydatabase.mdf', MOVE N'mydatabase_Log' TO N'F:\mydatabase_1.ldf', NOUNLOAD, REPLACE, STATS = 10
GO
Thanks for any help!
March 11, 2011 at 2:37 pm
What is the error message u r getting ?
How many files does the backup set has?
Does the database has any secondary data files ?
if possible give the screenshots
March 11, 2011 at 3:47 pm
I don't get an error message. If I run the restore from the GUI it quickly gets to 100% and then the green thing spins for hours. If I execute from management studio I see it quickly get to 100% on the messages tab, but it still never finishes Executing query... . There is 1 file in the backup and no secondary data file.
March 11, 2011 at 4:13 pm
Are you upgrading versions by any chance?
Try running the restore from t-sql and check the messages tab to see what it's doing. Most of the time the hanging after 100% is upgrading to the current db server version, or an integrity check.
March 11, 2011 at 7:23 pm
If you run the following:
DBCC TRACEON (3004, 3605, -1)
SQL Server logs additional information in its log file re. the restore. Might give some clues as to what its doing.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
March 11, 2011 at 7:31 pm
When i run that i get:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I don't see any errors.
March 11, 2011 at 9:38 pm
Once you run that, SQL Server will log additional details in its log about any restores that you now run.
Try restoring that database now, and check the details in the SQL Server log to see at which point the restore has stopped.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
March 12, 2011 at 1:15 am
In your code - you are not specifying RECOVERY which tells SQL Server to finish the backup and recover the database.
From the GUI - you need to select the option to recover the database.
Either way, without specifying recovery the restore *will never finish* 😀
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 12, 2011 at 3:53 am
Jeffrey Williams-493691 (3/12/2011)
In your code - you are not specifying RECOVERY which tells SQL Server to finish the backup and recover the database.
With RECOVERY is the default if nothing is specified. I was also looking to see if WITH NORECOVERY had been added
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2011 at 12:50 pm
Sql Smith (3/11/2011)
Hello,The restore gets to 100%, but never actually finishes. I've used RESTORE VERIFYONLY to check the backup. It reports "The backup set on file 1 is valid." I don't see anything in the log files that indicates an error. Also, Ive tried deleting the .mdf and .ldf files before the restore and have the same experience.
The restore code I am using:
RESTORE DATABASE [mydatabase] FROM DISK = g:\backups\mydatabase.bak' WITH FILE = 1, MOVE N'mydatabase_Data' TO N'E:\mydatabase.mdf', MOVE N'mydatabase_Log' TO N'F:\mydatabase_1.ldf', NOUNLOAD, REPLACE, STATS = 10GO
Thanks for any help!
does the database already exist on the server?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 12, 2011 at 1:23 pm
What do the stats show you? Does it hang at the beginning? the end?
March 12, 2011 at 7:50 pm
Hey Guys. Thanks for all the replies.
to answer some of your questions:
The restore did the same thing if RECOVERY was explicitly specified or not. The restore would get to 100%, but the database would never come out of restoring status. I let it go for hours. Also, this database was already on the server. I did try deleting it an recovering, but i didn't make a difference.
Now, I did manage to get the database back. I used Redgate's Object level recovery to restore each object individually from the same backup that wouldn't work from SQL Server. This took several hours, but I was desperate. I would like to get to the bottom of this though. Luckily this was only an 8gb database, i wouldn't have been able to to this on our large databases.
Thanks again.
March 13, 2011 at 1:22 am
So this is a backup that was taken with Redgate SQLBackup is that correct?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 13, 2011 at 3:57 am
Perry Whittle (3/13/2011)
So this is a backup that was taken with Redgate SQLBackup is that correct?
No, I don't think so. In his first post, he mentioned that the backup command he was using was
RESTORE DATABASE [mydatabase] FROM DISK = g:\backups\mydatabase.bak' WITH FILE = 1, MOVE N'mydatabase_Data' TO N'E:\mydatabase.mdf', MOVE N'mydatabase_Log' TO N'F:\mydatabase_1.ldf', NOUNLOAD, REPLACE, STATS = 10
GO
'bak' extension, with 2 keywords (NOUNLOAD, STATS) that's not supported by SQL Backup, impossible it's a SQL Backup backup.
Red Gate's Object Level Recovery tool has a 'native'[/url] version allows you to extract database objects from native SQL Server backups.
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017.
March 13, 2011 at 11:46 am
Old Hand is correct. This is a native SQL backup.
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply