759
When it comes to managing SQL Server environments, having quick access to past restore operations is essential for database professionals. Whether you’re trying to troubleshoot issues, audit your databases, or ensure business continuity, having a reliable and straightforward solution is essential. In this blog post, you’ll learn how to quickly retrieve SQL Server database restore history using T-SQL in no time. Keep this guide handy and share it with your colleagues to save time and streamline your database management tasks. Let’s dive in and learn how to access your SQL Server database restore history right away!
T-Sql
------------------------------------------------------------
-- This script retrieves SQL Server database restore history
------------------------------------------------------------
SELECT
rh.destination_database_name AS [database_name]
, rh.restore_date AS [restore_date]
, rh.restore_type AS [restore_type]
, rh.user_name AS [restored_by]
, bmf.physical_device_name AS [backup_location]
-- Uncomment the following lines to select all columns from the restorehistory and backupmediafamily tables
--, rh.*
--, bmf.*
FROM msdb.dbo.restorehistory rh
JOIN msdb.dbo.backupset bs
ON rh.backup_set_id = bs.backup_set_id -- Join the restorehistory and backupset tables using backup_set_id
JOIN msdb.dbo.backupmediafamily bmf
ON bs.media_set_id = bmf.media_set_id -- Join the backupset and backupmediafamily tables using media_set_id
WHERE
-- Modidy this line to search for databases with names containing a specific pattern using the LIKE operator
rh.destination_database_name LIKE '%dbnamehere%'
--AND rh.restore_date >= DATEADD(YEAR, -2, GETDATE())
ORDER BY
rh.restore_date
/* The script above retrieves SQL Server database restore history.
The query selects relevant information such as database name, restore date, restore type,
the user who performed the restore, and the location of the backup file used for
the restore operation. The results are ordered by the restore date. */
/* To use this script, simply copy and paste it into a new query window in SQL Server Management Studio, then execute the script to copy the data. */
/* You can also uncomment the WHERE clause line to filter within a specific date range. */