SSIS Package to Centrally Monitor SQL Server Backups


By:   |   Updated: 2017-10-19   |   Comments   |   Related: More > Monitoring


Is the Database the Culprit of Your Application Issues?

Free MSSQLTips Webinar: Is the Database the Culprit of Your Application Issues?

When you're troubleshooting application performance issues, have you ever found the problem residing a few layers deep in the database or not at all? How long did it take you to find and fix the issue? Don't worry if you said "a while". Learn how to solve performance problems fast.


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.


Last Updated: 2017-10-19


get scripts

next tip button



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.

View all my tips
Related Resources





Comments For This Article





download





Recommended Reading

How to Read Log File in SQL Server using TSQL

How to setup SQL Server alerts and email operator notifications

SQL Server Wait Stats Monitoring with PowerShell

Posting SQL Server Notifications to Slack

SQL Server High CPU Query Use Monitoring with PowerShell








get free sql tips
agree to terms


Learn more about SQL Server tools