SSIS Package to Centrally Monitor SQL Server Backups

By:   |   Comments (1)   |   Related: > Monitoring


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:

SSIS Package Flow

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:

SSIS Connection Manager

In the Connection Manager, right click the newly created connection and select Properties.

SSIS Connection Manager 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.

SSIS Property Expressions Editor

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:

SSIS logic to capture data

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.

SSIS OLE DB Destination Editor

Run the Package

 Now the package is ready. Run the package and you will have backup details from all of the servers.

Execute the SSIS Package

This is what the data looks like in the centralized table.

Data in the centralized SQL Server 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.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author 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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Wednesday, February 24, 2021 - 6:43:15 PM - Jyothi Back To Top (88298)
Hi Atul,

This is a great document. if I want to get dynamic db name as well how would I do it.

I create the db_name variable and called in dynamic connection manager ad two two different expressions. but since my dynamic conenction manager is running on master its giving me only results from master database. Ofcourse if I change the value to any other database in variable it would change but can you help me how I can dynamically call all databases as well along with servername

My scope:
Trying to retrieve user privileges on database level from all servers and all databases on the servers

Any help will be appreciated.

Thank you,
Jyothi














get free sql tips
agree to terms