creating a backup report via linked server

Question

i have 50 linked servers. i want to extract the backup report from all of them with a one script. i need your help :)

Answer 1

Here is a script for you. I rearranged your query, so that you will get all databases, even those that has never been backed up - that could be sort of interesting.

The TRY-CATCH is there to prevent that an error with an occasional server does not abort the entire script.

   DECLARE @cur           CURSOR,   
           @server        sysname,  
           @sp_executesql nvarchar(200),  
           @query         nvarchar(MAX)   
     
   DECLARE @rettable TABLE (servername   sysname  NOT NULL,  
                            dbname       sysname  NOT NULL,  
                            lastbackdate datetime NULL,  
                            backuptype   varchar(30) NULL,  
                            PRIMARY KEY (servername, dbname)  
   )  
     
     
   SELECT @query =   
       'SELECT @@servername, databases.name,   
        MAX(backup_finish_date) AS last_db_backup_date,  
        case backupset.type  
           when ''D'' then ''Database''  
           when ''I'' then ''Differential database''  
           when ''L'' then ''Log''  
           when ''F'' then ''File or filegroup''  
           when ''G'' then ''Differential file''  
           when ''P'' then ''Partial''  
           when ''Q'' then ''Differential partial'' end as [Type]  
     FROM  master.sys.databases   
     LEFT  JOIN (msdb.dbo.backupmediafamily  
                 JOIN msdb.dbo.backupset ON backupmediafamily.media_set_id = backupset.media_set_id)  
           ON databases.name = backupset.database_name  
     WHERE databases.state_desc = ''ONLINE''  
     GROUP BY databases.name, backupset.type  
     ORDER BY databases.name, backupset.type'  
     
   SET @cur = CURSOR STATIC FOR  
      SELECT name   
      FROM   sys.servers   
      WHERE  provider LIKE N'SQLNCLI%' OR  
             provider LIKE N'MSOLEDBSQL%' OR  
             provider = N'SQLOLEDB'  
     
   OPEN @cur  
     
   WHILE 1 = 1  
   BEGIN  
      FETCH @cur INTO @server  
      IF @@fetch_status <> 0  
         BREAK  
     
      SELECT @sp_executesql = quotename(@server) + '.master.sys.sp_executesql'  
     
      BEGIN TRY  
         INSERT @rettable(servername, dbname, lastbackdate, backuptype)  
            EXEC @sp_executesql @query  
      END TRY  
      BEGIN CATCH  
         PRINT 'Call to server ' + @server + ' failed with: ' + error_message()  
      END CATCH  
   END  
     
   SELECT * FROM @rettable  

Answer 2

Or use CMS = Central Management Server.
https://learn.microsoft.com/en-us/sql/ssms/register-servers/create-a-central-management-server-and-server-group?view=sql-server-ver16
https://learn.microsoft.com/en-us/sql/relational-databases/administer-multiple-servers-using-central-management-servers?view=sql-server-ver16

Answer 3

Recall that I told you that linked server often means hassle? Linked servers are great when all stars are aligned. And when they are not - you often find yourself deep in the rabbit hole.

Getting distributed transactions to work over linked servers can be smooth, and you don't even notice. But it can also be an uphill battle that you never win. There has to be trust among the domains etc.

You can work around it with

   EXEC sp_serveroption 'SERVER', 'remote proc transaction promotion', 'false'  

`But I strongly recommend against this. Some application may want to use the linked server for real and have a true need of a distributed transaction. In that case, it is better that they get the error message than false promises.

A possible variation is to create a temporary linked server for each actual server, and configure this temporary server with the setting above set to false. But I am not sure how much I like this.

No, I think you should do this in PowerShell, or whichever language you choose. Just as it is something that runs outside SQL Server can connect to each instance directly. Believe me, this will save you more than one grey hair.

Answer 4

Hi @ RabiaKurnaz-0115 ,

Central repository can keep information for all servers

You can try to change this command to meet your repuirement, it can loop thru all your servers and collect backup history

-- Get Backup History for required database  
SELECT   
s.database_name,  
m.physical_device_name,  
CAST(CAST(s.backup_size / 1048576 AS INT) AS VARCHAR(14))  AS bkSizeMB,  
CAST(CAST(s.compressed_backup_size / 1048576 AS INT) AS VARCHAR(14))  AS Compressed_bkSizeMB,  
CAST(DATEDIFF(second, s.backup_start_date,  
s.backup_finish_date) AS VARCHAR(12)) + ' ' + 'Seconds' TimeTaken,  
s.backup_start_date,  
CAST(s.first_lsn AS VARCHAR(50)) AS first_lsn,  
CAST(s.last_lsn AS VARCHAR(50)) AS last_lsn,  
CASE s.[type]  
WHEN 'D' THEN 'Full'  
WHEN 'I' THEN 'Differential'  
WHEN 'L' THEN 'Transaction Log'  
END AS BackupType,  
s.server_name,  
s.recovery_model  
FROM msdb.dbo.backupset s  
INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id  
WHERE s.backup_start_date>'2017-04-01'  --adjust your date  
--Uncomment below lines if you want a one or more type of backup  
--AND (s.type='D' OR s.type ='I')  
--AND (s.type='L' )  
--Uncomment below line if you want to filter by database name  
--AND database_name ='security'  
ORDER BY backup_start_date DESC, backup_finish_date  

https://dba.stackexchange.com/questions/188050/centralized-backup-report-for-sql-server-databases

For powershell function, you can try to use:
Get-DbaBackupHistory
Get-DbaLastBackup

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.