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.
Jeremy Kadlec is a Founder, Editor and Author at MSSQLTips.com with more than 300 contributions and 25+ years of SQL Server experience. Jeremy leads a team of more than 300 authors helping millions of SQL Server professionals around the globe every second of the day for the last 20 years. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP based on his community contributions. Jeremy brings 25+ years of SQL Server DBA and Developer knowledge to the community and holds a bachelor’s degree from SSU and master’s degree from UMBC.
