Recently I have had to restore a number of databases into our training environment.   Looking up each backup individually would be time consuming and I prefer to save time whenever possible.  So, instead of lookup up the backup for each database I needed, I thought a quick script would be handy.  Of course, there are multiple ways to gather this information, but this is my way.

I like scripts that generate commands for me, I find it makes things easier.  Therefore this query will generate a generic restore command with some options already supplied.  Feel free to modify the query to include whatever options you need.

Note: this query will only find the last full backup of the database.

Disclaimer:  Do not execute code you find on the internet blindly on Production servers.  Test the script first and then decide if it is appropriate to execute in Production.  

/***************************************************************
  Author: John Morehouse
  Date: 2016/09/19
  Summary: This script will find the most recent backup as well as generate the RESTORE command for the given database. 
 
  You may alter this code for your own purposes. You may republish altered code as long as you give due credit. 
 
  THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
***************************************************************/;WITH mycte AS(
SELECT
b.database_name
, MAX(backup_start_date) 'start_date'
, MAX(media_set_id) AS 'Media_set_id'
FROM msdb.dbo.backupset b
WHERE
b.type = 'D'
AND b.is_copy_only = 0
AND b.database_name NOT IN ('tempdb')
GROUP BY database_name
)
SELECT
mycte.database_name
, myCTE.start_date
, b.physical_device_name
, 'RESTORE DATABASE ' + mycte.database_name + ' FROM DISK = ''' + b.physical_device_name + ''' WITH RECOVERY, REPLACE, STATS=5'
FROM myCTE
INNER JOIN msdb.dbo.backupmediafamily b ON b.media_set_id = mycte.Media_set_id
ORDER BY mycte.database_name asc
GO

As you can see, the script will give you the last backup and restore command for all of the databases on the instance.  You could easily modify the WHERE clause to only select a single database or multiple if you desire.

I have also used this against linked servers (I know, I know.  Linked servers) to get backup information on databases that reside on other servers that need to be restored.

Enjoy!

© 2016, John Morehouse. All rights reserved.