Before I do database refreshes on different environments I often need to know the history of previous restores. That scripts helps me in this case. I usually limit the history by certain date like this "restore_date > '20180125 08:00'" Please change it with the date and time you are interested in also you can use filter based on the database name.
That script has been tested on SQL Server versions: 2005/2008/2008R2/2012/2014/2016.
select restore_date, destination_database_name, user_name, restore_type, stop_at, stop_at_mark_name, stop_before from msdb.dbo.restorehistory where restore_date > '20180125 08:00' --you may use a filter on the database name as well --AND destination_database_name like 'MyDatabase%' order by restore_date desc;
A DBA's tasks, from day-to-day, are rarely constant; with one exception: the need to ensure each and every day that any database in their charge can be restored and recovered, in the event of error of disaster. In this book, you'll discover how to perform each of these backup and restore operations using SQL Server Management Studio (SSMS), basic T-SQL scripts and Red Gate's SQL Backup tool.
2013-08-26
12,691 reads
Occasionally, a DBA may need to restore a database from a multiple backup files that originated from multiple servers. This requirement might arise, for example, in a database-mirroring configuration, where backups may be from either of the servers.
2012-06-27
2,923 reads
Backups are an everyday part of DBA life, whereas restores tend to happen on call at 3 a.m. In this article, Grant Fritchey looks at what you should be doing to make your restores as quick and seamless as possible.
2011-12-14
7,037 reads
Performing a Database Restore is a part of a DBA's daily life. A DBA may need to perform a Restore due to various reasons such as recovery, refreshing a database for testing purpose etc. Many times it can be difficult to perform a Restore due to corrupted media, low disk space on the server and so on. This articlel outlines one approach which can be used to Restore the backup of the Production Database a test environment.
2011-12-09
4,218 reads
In a recent tip I outlined a process for converting a date, stored as an integer into a datetime data type. Date and time information for run history of SQL Server Agent jobs is stored within the msdb..sysjobshistory table as an integer data type, not as a datetime as one would expect. As promised, this tip picks up where we left off. On converting the integer-typed run_time into a format that is more user friendly for presentation purposes.
2009-05-20
2,522 reads
