By: Jeremy Kadlec | Comments (3) | Related: > 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.
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
