Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Database Restore Verification Script


By:   |   Last Updated: 2007-07-24   |   Comments (3)   |   Related Tips: More > Restore

Problem
I have over night processes on a few different SQL Servers in my environment that are backup and restore related.  I have used the scripts on MSSQLTips.com to check if the SQL Server Agent Jobs\Job Steps have run as well as the backup verification code.  Some of the backup and restore processes I have written and others I have inherited.  Some of the processes seem to be very reliable and others are not.  I need a way to validate the restore processes have completed properly just like the SQL Server Agent Job and backup code.  Can you provide this script so I can include it in my daily verification process?

Solution
Validating daily processes such as SQL Server Agent Jobs, backups, restores, etc. should be an daily task for all DBAs to ensure the core business processes execute as expected.  When thinking about restore related processes the other thought that comes to mind is log shipping.  Whether you have to support daily backup and restore processes or full fledged log shipping, validating the full, differential and transaction log restores have worked properly is critical especially when these process are considered your disaster recovery or high availability solution.  It is better to be safe than sorry.  As such, here is code to validate the database restoration processes in the last 24 hours:

All Backups - SQL Server 2005 and 2000 Restore Verification

SELECT restore_date,
destination_database_name,
restore_type = CASE WHEN restore_type = 'D' THEN 'Database'
WHEN restore_type = 'F' THEN 'File'
WHEN restore_type = 'G' THEN 'Filegroup'
WHEN restore_type = 'L' THEN 'Log'
WHEN restore_type = 'I' THEN 'Differential'
WHEN restore_type = 'R' THEN 'Revert'
ELSE 'Unknown'
END
FROM MSDB.dbo.restorehistory
WHERE restore_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
AND restore_type <> 'V'
ORDER BY restore_history_id

Full Backups - SQL Server 2005 and 2000 Restore Verification

SELECT restore_date,
destination_database_name
FROM MSDB.dbo.restorehistory
WHERE restore_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
AND restore_type = 'D'
ORDER BY restore_history_id

Differential Backups - SQL Server 2005 and 2000 Restore Verification

SELECT restore_date,
destination_database_name
FROM MSDB.dbo.restorehistory
WHERE restore_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
AND restore_type = 'I'
ORDER BY restore_history_id

Transaction Log Backups - SQL Server 2005 and 2000 Restore Verification

SELECT restore_date,
destination_database_name
FROM MSDB.dbo.restorehistory
WHERE restore_date BETWEEN DATEADD(hh, -24, GETDATE()) AND GETDATE()
AND restore_type = 'L'
ORDER BY restore_history_id

 

Next Steps

  • Validating your processes on a daily basis is important and should be considered a portion of your daily DBA verification.
  • Consider including this restore verification script with the backup and SQL Server Agent Job verification scripts.
  • As you build new processes to support the organization consider not only the functional requirements, but also the operational needs.  Keeping in mind that the process must be monitored, it may need to be restarted and parameters might change over time are all items that should be considered as the process is developed.
  • For more information about the system table referenced in this tip, review - restorehistory (Transact-SQL).
  • For more information about backup and recovery strategies, check out the Backup and Recovery category on MSSQLTips.com.


Last Updated: 2007-07-24


get scripts

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Friday, April 11, 2014 - 11:08:31 AM - KAPIL BHASIN Back To Top

Ahh Yes Greg,

 

I did made a contact and found there are no licensing issues.

I am still looking into it why we are not able to restore that particular db?

Anyone , any idea which i may try, as i checked for possible solutions and they seems not to work..


Friday, April 11, 2014 - 9:59:42 AM - Greg Robidoux Back To Top

Kahil, did you contact Idera.   It looks like you might have an expired license or some other licensing issue.


Friday, April 11, 2014 - 9:31:28 AM - KAPIL BHASIN Back To Top

Team, 

Please help on below error which I am getting while trying to restore a full back up of db using a third party software idera. 

Please find below the query and the error which we are getting while restoring OrbitGuidMapping DB. Please check and let us know. 

 

DECLARE @Returncode int

EXEC @Returncode = [master].[dbo].[xp_ss_restore] @database ='XYZ',

@filename ='D:\ABC Backups\XYZ_FULL.bak',

@replace=1

 

xp_ss_restore did not complete.  An error(0,1) was encountered.  Please check the parameters and their values. [v7.2.1.26]

 

SQLsafeCmd output:

------------------

SQLsafe Backup and Recovery CLI Version 7.2.1.26 (x64)

Copyright (c) 2004-2012 Idera, Inc., All Rights Reserved.

<http://www.idera.com/>

 

Connecting to Backup Agent on SERVER01...success.  (7.2.1.26)

 

RESTORE XYZ...FAILED.

Server instance: SERVER01, Database: XYZ

The backup service could not save licensing information to the registry.

 

--- End of SQLsafeCmd output ---

 

 

(0 row(s) affected)


Learn more about SQL Server tools