SSIS Package to Centrally Monitor SQL Server Backups
In a previous tip, SSIS Package to Centrally Monitor All SQL Servers, we looked at how to create an SSIS package that could connect to several servers and return information from each server. In this tip we take a look at how to use this approach to capture the last time databases were backed up on all of our servers.
In part 1, we saw how to create a SSIS package which connects to all your production server one by one and then performs some data collection and stores the data in a central server. Please read part 1 first.
SSIS Package Flow
This is what the package looks like:
Now, let's see how we can use this package to pull information from all your production servers for the last time databases were backed up.
Create Data Collection Table
On our central server, we need to create the following table. This will be the table that is used to store the missing backup information.
CREATE TABLE [dbo].[DBBackupinfo] ( [Servername] [nvarchar](128) NULL, [DatabaseName] [nvarchar](50) NULL, [LastDbBackupDate] [datetime] NULL, [LastBackupDays] [int] NULL, [Status] [int] NULL ) ON [PRIMARY] GO
Modify the SSIS Package
Open the SSIS package and in the connection manager, right click and add new connection with the name "DynamicConn" as shown below:
In the Connection Manager, right click the newly created connection and select Properties.
In the Property Expressions Editor, set the Property and Expression as shown below. In the package Foreach Loop the server name will get updated each time to connect to the appropriate server in the list of servers and pass the value to this variable.
In the package, double click the Data Flow Task. In the Data Flow Task we have a Source and Destination. Open the OLE DB Source and paste the below SQL code to pull information from each server for last full backups. Also, make sure the connection uses the DynamicConn we created above.
DECLARE @dbname AS VARCHAR(80) DECLARE @msgdb AS VARCHAR(100) DECLARE @dbbkpname AS VARCHAR(80) DECLARE @dypart1 AS VARCHAR(2) DECLARE @dypart2 AS VARCHAR(3) DECLARE @dypart3 AS VARCHAR(4) DECLARE @currentdate AS VARCHAR(10) DECLARE @server_name AS VARCHAR(30) SELECT @server_name = @@servername SELECT @dypart1 = DATEPART(dd,GETDATE()) SELECT @dypart2 = DATENAME(mm,GETDATE()) SELECT @dypart3 = DATEPART(yy,GETDATE()) SELECT @currentdate= @dypart1 + @dypart2 + @dypart3 SELECT @@servername [Servername], SUBSTRING(s.name,1,50) AS 'DatabaseName', b.backup_start_date AS 'LastDbBackupDate', DATEDIFF(day,b.backup_start_date,getdate()) AS 'LastBackupDays', s.Status FROM master..sysdatabases s LEFT OUTER JOIN msdb..backupset b ON s.name = b.database_name AND b.backup_start_date = (SELECT MAX(backup_start_date)AS 'Full DB Backup Status' FROM msdb..backupset WHERE database_name = b.database_name AND type = 'D') -- full database backups only, not log backups WHERE s.name <>'tempdb' ORDER BY s.name
Here is a screen shot:
Open the OLE DB Destination and point it to the DBBackupInfo table created above. Also, the connection manager should point to your central server where the data will be stored.
Run the Package
Now the package is ready. Run the package and you will have backup details from all of the servers.
This is what the data looks like in the centralized table.
Now you know how to pull the backup information from all the production servers. You can share this information via email to all the stakeholders or create SSRS reports and create a subscription to deliver the report. Try this in your environment and let me know if you have any issues or suggestions.
- You can also add another step to the package to truncate the DBBackupInfo table every time the package runs.
- Now you know how to pull backup information from all servers and save the data to a centralized location.
- You can create similar packages or add more steps to this package to get index, statistics, jobs information, etc.
Last Updated: 2017-10-19
About the author
View all my tips