T-SQL Stored Procedure to Get the Time and Backup File Location of the Last Data Restore

/*
.Synoposis
Stored procedure to list the last data restore time AND backup file location for specific online user DB's.

.Last modified
2014-03-24 by Nick Xu

.SQL Version
2005+

--Usage
EXEC sp__GetlastDataRestoreTime @dbname = 'DBADB'
GO
EXEC sp__GetlastDataRestoreTime
GO
*/

USE master
GO

/*
--For generic setting
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'sp__GetlastDataRestoreTime' AND type = 'P')
BEGIN
	DROP PROC sp__GetlastDataRestoreTime
END
GO
*/

--Specific to MLP's environment
USE DBADB
GO


IF (OBJECT_ID('sp__GetlastDataRestoreTime') IS NOT NULL)
  DROP PROCEDURE sp__GetlastDataRestoreTime
GO



CREATE proc sp__GetlastDataRestoreTime
@dbname VARCHAR(50) = NULL
AS
BEGIN


IF @dbname IS NULL
BEGIN

	WITH tmp AS
	(
		SELECT row_number() OVER (PARTITION by BS.database_name ORDER BY RH.restore_date DESC) AS rowid,
		RH.destination_database_name, 
		BMF.physical_device_name, 
		RH.restore_date, RH.user_name
		FROM msdb.dbo.backupset BS INNER JOIN msdb.dbo.backupmediafamily BMF
		ON BS.media_set_id = BMF.media_set_id
		INNER JOIN msdb.dbo.restorehistory RH
		ON BS.backup_set_id = RH.backup_set_id
		WHERE RH.destination_database_name NOT IN (N'master', N'msdb', N'tempdb', N'model')
			AND RH.restore_type = N'D'
	)
	SELECT destination_database_name, 
			restore_date AS last_data_restore_date, 
			physical_device_name AS last_data_restore_backup_file_location
	FROM tmp
	WHERE rowid = 1 AND destination_database_name IN (SELECT name FROM sys.databases WHERE state_desc = N'online' )
	ORDER BY restore_date DESC
END
ELSE
BEGIN
	WITH tmp AS
	(
		SELECT row_number() over (PARTITION by BS.database_name ORDER BY RH.restore_date DESC) AS rowid,
		RH.destination_database_name, 
		BMF.physical_device_name, 
		RH.restore_date, RH.user_name
		FROM msdb.dbo.backupset BS INNER JOIN msdb.dbo.backupmediafamily BMF
		ON BS.media_set_id = BMF.media_set_id
		INNER JOIN msdb.dbo.restorehistory RH
		ON BS.backup_set_id = RH.backup_set_id
		WHERE RH.destination_database_name not IN (N'master', N'msdb', N'tempdb', N'model')
			AND RH.restore_type = N'D'
	)
	SELECT destination_database_name, 
			restore_date AS last_data_restore_date,
			physical_device_name AS last_data_restore_backup_file_location
			FROM tmp
	WHERE rowid = 1 AND destination_database_name = @dbname
	ORDER BY restore_date DESC	
END

END
GO
Advertisements

About Nick Xu

Not a DBA anymore
This entry was posted in Backup and Restore, T-SQL Script and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s