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

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.
LVL 9
Senior IT System EngineerIT ProfessionalAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

yo_beeDirector of Information TechnologyCommented:
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.
1
pcelbaCommented:
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/
1
Senior IT System EngineerIT ProfessionalAuthor 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.
0
Defend Against the Q2 Top Security Threats

Were you aware that overall malware worldwide was down a surprising 42% from Q1'18? Every quarter, the WatchGuard Threat Lab releases an Internet Security Report that analyzes the top threat trends impacting companies worldwide. Learn more by viewing our on-demand webinar today!

pcelbaCommented:
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.
1
Senior IT System EngineerIT ProfessionalAuthor 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 ?
0
pcelbaCommented:
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?
1
Senior IT System EngineerIT ProfessionalAuthor Commented:
Hi PCElba,

The SQL Script result does not show any Device_Type column ?
0
pcelbaCommented:
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

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Senior IT System EngineerIT ProfessionalAuthor 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?
0
pcelbaCommented:
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.
1
Senior IT System EngineerIT ProfessionalAuthor 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.
0
pcelbaCommented:
Yes, virtual backup devices are created by the Veeam backup.
1
Senior IT System EngineerIT ProfessionalAuthor Commented:
Thanks guys !
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.