SSIS Package to Centrally Monitor SQL Server Backups

Problem

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.

Solution

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.

Next Steps

• 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.

Atul Gaikwad

Atul Gaikwad has over 14+ years of experience with SQL Server. He currently works for one of the leading MNCs in Pune as a Delivery Manager handling small to large SQL Server production support projects. He is technology savvy and believes in automation and innovation. He likes to automate anything and everything which can help save time and make a DBAs life effortless and smooth, to focus on larger projects. He has extensive hands on experience in SQL Server administration and high availability solutions like clustering, replication, log shipping, mirroring, etc. He also possesses strong knowledge of SSRS and SSIS.

• MSSQLTips Awards: Rookie of the Year Contender – 2016