February 19, 2021 at 9:49 am
I am automating a restore procedure and i'm trying to trap errors as they happen.
But I cannot trap most errors because it only returns the last error : RESTORE DATABASE is terminating abnormally.
I need to be able to get the 1st Msg code in a try... catch. so that I can report more accurately ie: 3169
I have tried the following but no error exists in there
EXEC xp_ReadErrorLog 0, 1, N'3169', NULL,@ErrorDatetime,@ErrorDatetimePlusSecond,N'DESC'
-------------------------------------------------------------------------------------------------------------------------------------------------------
Msg 3169, Level 16, State 1, Line 1
The database was backed up on a server running version 15.00.2000. That version is incompatible with this server, which is running version 13.00.5492. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 3169, Level 16, State 1, Line 1
The database was backed up on a server running version 15.00.2000. That version is incompatible with this server, which is running version 13.00.5492. Either restore the database on a server that supports the backup, or use a backup that is compatible with this server.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Darryl Wilson
darrylw99@hotmail.com
February 19, 2021 at 10:40 am
Dunno if this is any good as a suggestion, but if you DROP the database before the restore or it doesn't exist, then if the database exists after the restore then presumably the restore was successful. The [create_date] (in sys.databases) after restore will be a "now" date, rather than the original create date of the database the BACKUP file came from, and there are some flags in sys.databases that would be worth checking - e.g. [state] will be "0", and [state_desc] will be "ONLINE" if the database has finished restoring
That won't help you with capturing an actual error message, but might be sufficient for "Success/Fail"
But if you already have a carefully built structure for the database files, and you want to reuse / overwrite that, then DROP would not be appropriate
There is also data in the Restore History
SELECT ...
FROMmsdb.dbo.restorehistory AS RH
LEFT OUTER JOIN msdb.dbo.restorefile AS RF
ON RF.restore_history_id = RH.restore_history_id
LEFT OUTER JOIN msdb.dbo.restorefilegroup AS RG
ON RG.restore_history_id = RH.restore_history_id
LEFT OUTER JOIN msdb.dbo.backupset AS BS
ON BS.backup_set_id = RH.backup_set_id
LEFT OUTER JOIN msdb.dbo.backupfile AS BF
ON BF.backup_set_id = RH.backup_set_id
LEFT OUTER JOIN msdb.dbo.backupmediaset AS BMS
ON BMS.media_set_id = BS.media_set_id
LEFT OUTER JOIN msdb.dbo.backupmediafamily AS BMF
ON BMF.media_set_id = BS.media_set_id
WHERE1=1
AND RH.destination_database_name = N'MyDatabaseName'
ORDER BY RH.restore_date DESC
, RF.file_number DESC
But that will only tell you files that were restored, not whether they were successful. I haven't tried the error you got, but maybe NOTHING would be found in the Restore History? If so that could be a useful indication that the restore failed
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply