January 27, 2006 at 1:10 pm
I am writing a procedure that is going to backup transaction logs for my databases. One of the things I want to check before I backup the transaction log is whether or not the database is currently in the process of a database backup.
Does anyone know how I can make this determination in a stored procedure?
Thanks,
hawg
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
January 27, 2006 at 1:23 pm
This should give you what you need
select DATABASEPROPERTY(name, N'IsInRecovery') end)
January 27, 2006 at 1:28 pm
This will only let me know if the database is recovering.
I have tried the DATABASEPROPERTY and DATABASEPROPERTYEX. I checked the status in DATABASEPROPERTYEX while a backup was running and all it told me was the database was ONLINE.
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
January 27, 2006 at 1:33 pm
This is the sql EM fires when you do a refresh and a base is being restored:
select name, DATABASEPROPERTY(name, N'IsDetached'),
(case when DATABASEPROPERTY(name, N'IsShutdown') is null then -1
else DATABASEPROPERTY(name, N'IsShutdown') end),
DATABASEPROPERTY(name, N'IsSuspect'), DATABASEPROPERTY(name, N'IsOffline'),
DATABASEPROPERTY(name, N'IsInLoad'),
(case when DATABASEPROPERTY(name, N'IsInRecovery') is null then -1
else DATABASEPROPERTY(name, N'IsInRecovery') end),
(case when DATABASEPROPERTY(name, N'IsNotRecovered')
is null then -1 else DATABASEPROPERTY(name, N'IsNotRecovered') end),
DATABASEPROPERTY(name, N'IsEmergencyMode'), DATABASEPROPERTY(name, N'IsInStandBy'),
has_dbaccess(name), status, category, status2 from master.dbo.sysdatabases
Thats where I looked, I am doing a restore at the moment
January 28, 2006 at 9:40 pm
Well, the backup in most cases will be executing because there was a job that fires the backup. Then you should check the state of that job to actually know if you db is being backup up. Check for scripts in the site that you will find many that will help you check the state of a job.
Also, by querying the sysprocesses table, check for the command '%BACKUP%'. You can see the db wich is being backup up with the dbid field.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply