CODEWITHSUNDEEP
sql-serverbackup
Guy
Guy

Reputation: 9826

How can I tell if a SQL Server database is being backed up

Is there a way to programmatically determine if a SQL Server backup is currently being performd on a particular database?

We have automated database backup scripts for both data and log files, where the databases are backed up nightly and log files are backed up every 15 minutes, 24 hours a day. However, we think that the log file backup job is failing if it runs the same time as the full backup is being run.

What I'd like to do is to make a change to my transaction log script to not run a transaction log backup while the full backup is being run.

If there a DMV or a system table that I can query and work this out?

Upvotes: 3

Views: 15580

Answers (2)

BradC
BradC

Reputation: 39916

Yes, it can be a problem in SQL 2000. Should not be a problem in 2005+

See this ServerFault question for the reason it conflicts.

See this Serverfault question for a more sophisticated script.

Upvotes: 1

SQLMenace
SQLMenace

Reputation: 134923

Yes there is

select * from sys.dm_exec_requests
where command = 'backup db'
and database_id = 6 --or whatever your db id is

Upvotes: 10

Related Questions