June 2, 2011 at 12:53 pm
I have a script that I wrote that takes the latest Full backup of every DB in my shop, copies it to my admin box (Win2k8r2, SQL2k8), does a restore using Redgate Hyperbac as a virtual DB, runs DBCC on the newly restore DB, records the results, restore time, and dbcc time, drops the virtual db, deletes the backup files, and e-mails out any errors to the rest of the DBA team.
This script works flawlessly for every database on every instance I have, with the exception of two simple DBs. On each of these DBs (which both live on SQL2k5), when it gets to the restore step and has to upgrade, the restore fails with the following error: [font="Courier New"]Error Number: 3013, Error Message: RESTORE DATABASE is terminating abnormally.[/font]
In the error log, I see the following:
[font="Courier New"]Starting up database 'DB_ToBeTested'.
The database 'DB_ToBeTested' is marked RESTORING and is in a state that does not allow recovery to be run.
Database was restored: Database: BIS_Virtual, creation date(time): 2009/12/17(10:33:58), first LSN: 887977:3014:61, last LSN: 887977:3038:1, number of dump devices: 5, device information: (FILE=1, TYPE=DISK: {'K:\Backups_LocalCopy\FULL_(local)_DB_ToBeTested_20110527_034755_01.sqb', 'K:\Backups_LocalCopy\FULL_(local)_DB_ToBeTested_20110527_034755_02.sqb', 'K:\Backups_LocalCopy\FULL_(local)_DB_ToBeTested_20110527_034755_03.sqb', 'K:\Backups_LocalCopy\FULL_(local)_DB_ToBeTested_20110527_034755_04.sqb', 'K:\Backups_LocalCopy\FULL_(local)_DB_ToBeTested_20110527_034755_05.sqb'}). Informational message. No user action required.
Starting up database 'DB_ToBeTested'.
Error: 928, Severity: 20, State: 1.
During upgrade, database raised exception 155, severity 25, state 1, address 0000000001071AC4. Use the exception number to determine the cause.[/font]
Does anyone have any insight into exception 155, sev 25? I have been searching Google all morning with no luck as of yet.
June 2, 2011 at 1:15 pm
You might want to ask RedGate about that. Are all of the backups that you're restoring using multiple files? Have you tried just restoring that database using the Virtual Restore wizard?
June 2, 2011 at 1:19 pm
Yes, almost every database in my shop backs up to multiple files, some more than others, some less. In any regard, I will try doing the restore using the wizard, sorry I didn't think of that earlier.
June 2, 2011 at 1:25 pm
Well, the virtual restore worked just fine using the wizard, I am going to look at my generated restore script as compared to the script generated by the wizard and see if I can find any clues. Thanks for your help.
June 2, 2011 at 6:51 pm
Have you tried restoring the database using SQL Server natively, without SQL Virtual Restore, and see if you get the same error?
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.
June 3, 2011 at 11:23 am
OK, so I picked this back up this morning and compared the two methods: A) Using the VR Wizard, which works, and B) Using my method, which generates a restore script, which does not work. Below, I have posted the scripts generated by each of these methods. They look functionally identical to me, please let me know if you can see something I am missing.
Wizard Generated Script:
RESTORE DATABASE [DB_ToBeTested] FROM
DISK=N'K:\Backups_LocalCopy\FULL_(local)_DB_ToBeTested_20110527_034755_01.sqb',
DISK=N'K:\Backups_LocalCopy\FULL_(local)_DB_ToBeTested_20110527_034755_02.sqb',
DISK=N'K:\Backups_LocalCopy\FULL_(local)_DB_ToBeTested_20110527_034755_03.sqb',
DISK=N'K:\Backups_LocalCopy\FULL_(local)_DB_ToBeTested_20110527_034755_04.sqb',
DISK=N'K:\Backups_LocalCopy\FULL_(local)_DB_ToBeTested_20110527_034755_05.sqb'
WITH MOVE N'DB_ToBeTested_Data' TO N'K:\DBCCTestArea\DB_ToBeTested_DB_ToBeTested.vmdf',
MOVE N'DB_ToBeTested_Data2' TO N'K:\DBCCTestArea\DB_ToBeTested_Data2_Data_DB_ToBeTested.vndf',
MOVE N'DB_ToBeTested_Data3' TO N'K:\DBCCTestArea\DB_ToBeTested_Data3_Data_DB_ToBeTested.vndf',
MOVE N'DB_ToBeTested_Indexes' TO N'K:\DBCCTestArea\DB_ToBeTested_Indexes_Data_DB_ToBeTested.vndf',
MOVE N'DB_ToBeTested_Indexes2' TO N'K:\DBCCTestArea\DB_ToBeTested_Indexes2_Data_DB_ToBeTested.vndf',
MOVE N'DB_ToBeTested_Indexes3' TO N'K:\DBCCTestArea\DB_ToBeTested_Indexes3_Data_DB_ToBeTested.vndf',
MOVE N'DB_ToBeTested_Log' TO N'K:\DBCCTestArea\DB_ToBeTested_log_DB_ToBeTested.vldf',
NORECOVERY, STATS=1
My Generated Script:
RESTORE DATABASE [DB_ToBeTested] FROM
DISK=N'K:\Backups_LocalCopy\FULL_(local)_DB_ToBeTested_20110527_034755_01.sqb',
DISK=N'K:\Backups_LocalCopy\FULL_(local)_DB_ToBeTested_20110527_034755_02.sqb',
DISK=N'K:\Backups_LocalCopy\FULL_(local)_DB_ToBeTested_20110527_034755_03.sqb',
DISK=N'K:\Backups_LocalCopy\FULL_(local)_DB_ToBeTested_20110527_034755_04.sqb',
DISK=N'K:\Backups_LocalCopy\FULL_(local)_DB_ToBeTested_20110527_034755_05.sqb'
WITH MOVE N'DB_ToBeTested_Data' TO N'K:\DBCCTestArea\DB_ToBeTested_DB_ToBeTested_DatasZS9YTVl.vmdf',
MOVE N'DB_ToBeTested_Log' TO N'K:\DBCCTestArea\DB_ToBeTested_DB_ToBeTested_LogPL7HGTwc.vmdf',
MOVE N'DB_ToBeTested_Indexes' TO N'K:\DBCCTestArea\DB_ToBeTested_DB_ToBeTested_IndexesX7oLxSjf.vmdf',
MOVE N'DB_ToBeTested_Indexes2' TO N'K:\DBCCTestArea\DB_ToBeTested_DB_ToBeTested_Indexes2UyDgQZlj.vmdf',
MOVE N'DB_ToBeTested_Indexes3' TO N'K:\DBCCTestArea\DB_ToBeTested_DB_ToBeTested_Indexes3LYaylbmJ.vmdf',
MOVE N'DB_ToBeTested_Data2' TO N'K:\DBCCTestArea\DB_ToBeTested_DB_ToBeTested_Data2IPr1YG7X.vmdf',
MOVE N'DB_ToBeTested_Data3' TO N'K:\DBCCTestArea\DB_ToBeTested_DB_ToBeTested_Data3L8YaMOJg.vmdf',
NORECOVERY, STATS=1, REPLACE
June 3, 2011 at 11:26 am
only difference i see, beside the REPLACE command at the end, is the wizard script uses file extensions of .vndf and yours is .vmdf
Lowell
June 3, 2011 at 11:30 am
yeah, I was just thinking about that. The configurations for each are identical in the "HyperBac Configuration Manager", so I don't see how it could matter
June 3, 2011 at 11:37 am
bhoff (6/3/2011)
yeah, I was just thinking about that. The configurations for each are identical in the "HyperBac Configuration Manager", so I don't see how it could matter
i don't either, and afaik the order of the move commands shouldn't matter either...
Lowell
June 3, 2011 at 11:41 am
Agreed, I cannot see how any of that would matter. Maybe RedGate will have some better insight. I will package all this up and send it off to them. Thanks for your input.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply