March 10, 2016 at 5:12 am
Hi..
Requirement: I want SQL server database backup status in single reports from all remote DB servers.
As below script is working in local server and does not working remote server via linked server, please suggest if any alternative solutions.
I have created two linked server successfully and working in target server.
SELECT DB.name AS DatabaseName
,MAX(DB.recovery_model_desc) AS RecModel
,MAX(BS.backup_start_date) AS LastBackup_date
,MAX(CASE WHEN BS.type = 'D'
THEN BS.backup_start_date END)
AS LastFullBackup_Date
,SUM(CASE WHEN BS.type = 'D'
THEN 1 END)
AS SinceLast_fullBackup
,MAX(CASE WHEN BS.type = 'L'
THEN BS.backup_start_date END)
AS LastTLog_Date
,SUM(CASE WHEN BS.type = 'L'
THEN 1 END)
AS SinceLast_TlogBackup
,MAX(CASE WHEN BS.type = 'I'
THEN BS.backup_start_date END)
AS LastDiffBackup_Date
,SUM(CASE WHEN BS.type = 'I'
THEN 1 END)
AS SinceLast_DiffBackup
FROM sys.databases AS DB
LEFT JOIN
msdb.dbo.backupset AS BS
ON BS.database_name = DB.name
inner join [Linkedservername].[msdb].[dbo].[backupset] as SB
ON SB.database_name = DB.name
WHERE ISNULL(BS.is_damaged, 0) = 0 -- exclude damaged backups
GROUP BY DB.name
ORDER BY DB.name;
Error Messages:
Msg 7202, Level 11, State 2, Line 2
Could not find server 'Linkedservername' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
Thanks
March 10, 2016 at 5:32 am
As below code working and fetching data from remote server but Remote server database name not display..
SELECT DB.name AS DatabaseName
,MAX(DB.recovery_model_desc) AS RecModel
,MAX(BS.backup_start_date) AS LastBackup_date
,MAX(CASE WHEN BS.type = 'D'
THEN BS.backup_start_date END)
AS LastFullBackup_Date
,SUM(CASE WHEN BS.type = 'D'
THEN 1 END)
AS SinceLast_fullBackup
,MAX(CASE WHEN BS.type = 'L'
THEN BS.backup_start_date END)
AS LastTLog_Date
,SUM(CASE WHEN BS.type = 'L'
THEN 1 END)
AS SinceLast_TlogBackup
,MAX(CASE WHEN BS.type = 'I'
THEN BS.backup_start_date END)
AS LastDiffBackup_Date
,SUM(CASE WHEN BS.type = 'I'
THEN 1 END)
AS SinceLast_DiffBackup
FROM sys.databases AS DB
LEFT JOIN
[Linkedservername]. msdb.dbo.backupset AS BS
ON BS.database_name = DB.name
WHERE ISNULL(BS.is_damaged, 0) = 0 -- exclude damaged backups
GROUP BY DB.name
ORDER BY DB.name;
March 10, 2016 at 6:39 am
Basically, you have something like this:
SELECT a bunch of columns
FROM sys.databases AS DB -- This is the LOCAL list of databases
LEFT JOIN [Linkedservername]. msdb.dbo.backupset AS BS -- This is the remote backups
It won't work.
You have to submit the same exact query to all your servers. Powershell is the best way to do this. Linked servers are not meant for this kind of things.
-- Gianluca Sartori
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply