Get the Backup History in your Central Management Server for SQL Server

I have a setup of Central Management Servers in my SQL Server and it’s been a big help to me in my everyday database administration tasks. I want to share with you guys my CMS setup as below.

CMS Screenshot

So for across all these servers, and everytime I need to setup a new one, I only have one maintenance plans then I just scale it out to all the target servers. Neat!

One neat script that you can run in your CMS is a quick check of backup history across all your target servers. See my script below;

SELECT backupset.database_name,
backupmedia.physical_device_name,
CAST(CAST(backupset.backup_size/1000000 AS INT) AS VARCHAR(14)) + ‘ MB’ AS backupsize,
backupset.backup_start_date,
backupset.[type] AS BackupType,
backupset.recovery_model
FROM msdb.dbo.backupset backupset
INNER JOIN msdb.dbo.backupmediafamily backupmedia
ON backupset.media_set_id = backupmedia.media_set_id
GO

It will give you the results as below, then you can just do a scan to see if all your target servers had a successful backup overnight.

Backup History Script Result

If you need to automate this script, you will need to do additional scripting to store the data in a table somewhere that you can export to a report later and then you need to create a scheduled job for this.

I hope this is helpful to you also 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

SHE GOES GLOBAL

Travel the world, change your life.

My Time to Travel

The travels of an old(er), solo, woman

Blog Home for MSSQLDUDE

The life of a data geek

unfoldthecreativity

Traveller Observer

The SQL Pro

(Ayman El-Ghazali - www.thesqlpro.com)

Meels on Wheels

Meels for breakfast, lunch and dinner.

juliansherman.net/

Building A Business While Having A Life

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

TroubleshootingSQL

Explaining the bits and bytes of SQL Server and Azure

Coding Tips

We are crazy about programming and we want to share our craziness with you!!!

SQL Studies

 Live, Learn, Share

Sql And Me

My Experiments with SQLServer

Dimitrios Kalemis

I am exactly like Jesus Christ: an atheist and anarchist against society and bad people with influence and power (judges, social workers, politicians, priests, and teachers).

Clint Huijbers' Blog

Senior Certified Microsoft BI Consultant

Blog of Many Useless Wonders

Where Uselessness Abounds!

Steve Spevack's Blog

IT Recruitment