Script to retrieve latest database backup details from preconfigured Linked Server

On 28 May, 2015

By : Charith Silva

No Comments

Below script can be used to retrieve latest backup file datetime and physical location for all the Databases in preconfigured Linked Server.

DECLARE @SQL NVARCHAR(MAX);
DECLARE @LinkedServerName NVARCHAR(128)
SET @LinkedServerName='linked server name' -- Set your linked server name here

SELECT @SQL = 'WITH Databse_Backup AS( SELECT (SELECT * FROM OPENQUERY(['+@LinkedServerName+'], ''SELECT @@ServerName'')) as [Server_Name],'
SELECT @SQL = @SQL +' (SELECT * FROM OPENQUERY(['+@LinkedServerName+'], ''SELECT SERVERPROPERTY(''''MachineName'''')'')) as [MachineName],'
SELECT @SQL = @SQL +' (SELECT * FROM OPENQUERY(['+@LinkedServerName+'], ''SELECT SERVERPROPERTY(''''InstanceName'''')'')) AS [InstanceName],'
SELECT @SQL = @SQL +' d.Name AS [Databse Name], '
SELECT @SQL = @SQL +' b.Backup_finish_date AS [Backup date], '
SELECT @SQL = @SQL +' bf.Physical_Device_name AS [Physical Device name],'
SELECT @SQL = @SQL +' RANK() OVER(PARTITION BY d.Name ORDER by Backup_finish_date desc) as latest'

SELECT @SQL = @SQL +' FROM ['+@LinkedServerName+'].master.sys.databases d'
SELECT @SQL = @SQL +' LEFT JOIN ['+@LinkedServerName+'].msdb.dbo.backupset b ON b.database_name = d.name AND b.[type] = ''D'''
SELECT @SQL = @SQL +' LEFT JOIN ['+@LinkedServerName+'].msdb.dbo.backupmediafamily bf ON b.media_set_id = bf.media_set_id )'
SELECT @SQL = @SQL +' SELECT [Server_Name],[MachineName],[InstanceName],[Databse Name] ,[Backup date],[Physical Device name] FROM Databse_Backup WHERE latest=1'

EXEC(@SQL)

DECLARE @SQL NVARCHAR(MAX);

DECLARE @LinkedServerName NVARCHAR(128)

SET @LinkedServerName='linked server name' -- Set your linked server name here

SELECT @SQL = 'WITH Databse_Backup AS( SELECT (SELECT * FROM OPENQUERY(['+@LinkedServerName+'], ''SELECT @@ServerName'')) as [Server_Name],'

SELECT @SQL = @SQL +' (SELECT * FROM OPENQUERY(['+@LinkedServerName+'], ''SELECT SERVERPROPERTY(''''MachineName'''')'')) as [MachineName],'

SELECT @SQL = @SQL +' (SELECT * FROM OPENQUERY(['+@LinkedServerName+'], ''SELECT SERVERPROPERTY(''''InstanceName'''')'')) AS [InstanceName],'

SELECT @SQL = @SQL +' d.Name AS [Databse Name], '

SELECT @SQL = @SQL +' b.Backup_finish_date AS [Backup date], '

SELECT @SQL = @SQL +' bf.Physical_Device_name AS [Physical Device name],'

SELECT @SQL = @SQL +' RANK() OVER(PARTITION BY d.Name ORDER by Backup_finish_date desc) as latest'

SELECT @SQL = @SQL +' FROM ['+@LinkedServerName+'].master.sys.databases d'

SELECT @SQL = @SQL +' LEFT JOIN ['+@LinkedServerName+'].msdb.dbo.backupset b ON b.database_name = d.name AND b.[type] = ''D'''

SELECT @SQL = @SQL +' LEFT JOIN ['+@LinkedServerName+'].msdb.dbo.backupmediafamily bf ON b.media_set_id = bf.media_set_id )'

SELECT @SQL = @SQL +' SELECT [Server_Name],[MachineName],[InstanceName],[Databse Name] ,[Backup date],[Physical Device name] FROM Databse_Backup WHERE latest=1'

EXEC(@SQL)

About The Author

Charith Silva

Charith Silva is a Microsoft certified SQL Server developer and database administrator who was graduated at Buckinghamshire New university in the UK. His career was started in 1998, primarily into Web application development, and later diversified into database development. He has got a vast experience in SQL Server database development, Database administration and Business Intelligence development. He believes that sharing the knowledge is key to the success.

Number of Posts : 87

All Posts by : Charith Silva