How to check if SQL server database is being backed up by SQL management studio ?

Senior IT System Engineer
Senior IT System Engineer used Ask the Experts™
on
Hi All SQL Server & scripting experts,

I've got multiple different version of SQL Server Database running from 2008 R2 up to 2014 edition.

Can anyone here please share the script (T-SQL or PowerShell) to list all SQL Backup job that is currently setup or configured to backup the Database on each database server?

I need to know if there is any backup job has been set and where it is dumping it's database into ?
Because sometimes I've found out that the D:\ drive is full due to the SQL backup dumping large.BAK file into the same drive as the SQL database .MDF running.

Thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
yo_beeDirector of Information Technology
Commented:
How many SQL servers are you dealing with?  
Open SSMS (SQL Server Management Studio) > SQL Agent

You will see all the jobs that are listed  and whether they are enabled or disabled. There also a Job Activity Monitor.

This will show what is scheduled to run. Here you wi see if there are any backup jobs scheduled.
You may execute following query:
;WITH sqltmp (DatabaseName, LastBackupId) AS
(
SELECT sdb.Name ,MAX(bus.backup_set_id)
FROM sys.sysdatabases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON (bus.database_name = sdb.name AND bus.type = 'D' )
GROUP BY sdb.name
)
SELECT @@SERVERNAME 'Server', a.DatabaseName, b.backup_start_date , b.backup_finish_date,m.physical_device_name,
Convert( numeric (8,2),Round(b.backup_size/ (1024*1024),2) ) [Size_MB]
FROM sqltmp a INNER JOIN msdb.dbo.backupset b ON
( a.LastBackupId = b.backup_set_id)
LEFT OUTER JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id
--WHERE a.DatabaseName NOT IN (‘tempdb’,’model’,’msdb’,’master’)
ORDER BY DatabaseName

Open in new window

More info: https://blog.sqlauthority.com/2010/11/04/sql-server-finding-last-backup-time-for-all-database/

Author

Commented:
Hi All,

There are about 13 SQL servers in total 4x of them have Active/Passive replication or log shipping.

So if I run the script:

;WITH sqltmp (databasename, lastbackupid) 
     AS (SELECT sdb.NAME, 
                Max(bus.backup_set_id) 
         FROM   sys.sysdatabases sdb 
                LEFT OUTER JOIN msdb.dbo.backupset bus 
                             ON ( bus.database_name = sdb.NAME 
                                  AND bus.type = 'D' ) 
         GROUP  BY sdb.NAME) 
SELECT @@SERVERNAME 
       'Server', 
       a.databasename, 
       b.backup_start_date, 
       b.backup_finish_date, 
       m.physical_device_name, 
       CONVERT(NUMERIC (8, 2), Round(b.backup_size / ( 1024 * 1024 ), 2)) 
       [Size_MB] 
FROM   sqltmp a 
       INNER JOIN msdb.dbo.backupset b 
               ON ( a.lastbackupid = b.backup_set_id ) 
       LEFT OUTER JOIN msdb.dbo.backupmediafamily m 
                    ON b.media_set_id = m.media_set_id 
--WHERE a.DatabaseName NOT IN (‘tempdb’,’model’,’msdb’,’master’) 
ORDER  BY databasename 

Open in new window


Does it have any impact on the current production environment ?

I need to know where the backup destination for that SQL backup job if any.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

You have to execute the query on each server separately or link all the servers at one location.
Impact to the production environment is almost none because the query is running on the very small table/view.

Remember: Even when the query provides the target file name it is highly possible this file is zipped and moved to some other location which is not traceable by any query... Outstanding case is the file deletion so you should use some additional code which will tell the current backup file status.

The best you can do is to incorporate appropriate logging into the backup process which will store more info than provided by above query. Highly paid backup software can do it for you.

Author

Commented:
Hi PCElba,

Why is some of the Physical Device Name in the result showing as GUID like {89D5B396-ED14-492C-8742-287A72E5D6C8}117?
Instead of \\UNC path or windows drive letters ?
You wrote you have replication instances available so these GUIDs can be from these instances.
Look at the msdb.dbo.backupmediafamily description here: https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/backupmediafamily-transact-sql

What is the devide_type for records having GUID as the physical name? Does it have logical name?

Author

Commented:
Hi PCElba,

The SQL Script result does not show any Device_Type column ?
The device_type is a column in msdb.dbo.backupmediafamily.

Did you look at the msdb.dbo.backupmediafamily description as proposed in post ID: 42105438?  Did you find the device_type column?

Do you know how to add this column to the query? If not then there it is:
;WITH sqltmp (databasename, lastbackupid) 
     AS (SELECT sdb.NAME, 
                Max(bus.backup_set_id) 
         FROM   sys.sysdatabases sdb 
                LEFT OUTER JOIN msdb.dbo.backupset bus 
                             ON ( bus.database_name = sdb.NAME 
                                  AND bus.type = 'D' ) 
         GROUP  BY sdb.NAME) 
SELECT @@SERVERNAME 
       'Server', 
       a.databasename, 
       b.backup_start_date, 
       b.backup_finish_date, 
       m.device_type, 
       m.physical_device_name, 
       CONVERT(NUMERIC (8, 2), Round(b.backup_size / ( 1024 * 1024 ), 2)) 
       [Size_MB] 
FROM   sqltmp a 
       INNER JOIN msdb.dbo.backupset b 
               ON ( a.lastbackupid = b.backup_set_id ) 
       LEFT OUTER JOIN msdb.dbo.backupmediafamily m 
                    ON b.media_set_id = m.media_set_id 
--WHERE a.DatabaseName NOT IN (‘tempdb’,’model’,’msdb’,’master’) 
ORDER  BY databasename

Open in new window

Author

Commented:
Hi PCElba,

Sorry for the late reply, this is the result I got from the script that you share above:
Backup
I'm not familiar with SQL script hence I do not know what does 7 = Virtual device as: https://docs.microsoft.com/en-us/sql/relational-databases/system-tables/backupmediafamily-transact-sql

So I assume the script is safe to be executed during the business hours and does not leave any trace behind on any Database?
Virtual device is used when the SQL database backup is done by some 3rd party backup software. So if your server uses some backup software then you know it is backing your databases up.

The script itself is safe because it just reads the data. But it can leave some trace or audit trail on SQL Server when the settings are defined to do so. You have to ask your SQL Server admins what logs are created by ordinary queries.

Author

Commented:
OK,

We are using Veeam Backup to backup the SQL Server VM which is also Truncate the transaction logs. Is that the case ?

If it is just audit log, then I do not need to worry about leaving any table or issue which can cause any outage or capacity issue later on.
Yes, virtual backup devices are created by the Veeam backup.

Author

Commented:
Thanks guys !

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial