check if the database is currently being backed up

How can I check if the database is currently being backed up?

Seems like that should be a property, but it's not an attribute of the DATABASEPROPERTY function.

Thanks

You can check sp_who/spwho2 to find out if any database is being backed up on the server. This involves manual intervention to look at the output of above SP's. Are you trying to retrieve that information interactively and do some other action or what are you trying to achieve?

|||I am trying to have certain logic kick off based on database is being backed. I know I can query the sysprocesses, which is what sp_who2 does, and look for 'BACKUP' string, but I was wondering if there's a better way.|||

I am not sure if its a good idea to have separate logic if the database is being backed up. Anyway, if the backup task is coming from Sql job, you should be able to query the job status.

|||

Check the Log Reuse description when you query sys.databases, and you can tell if a backup is running.

|||

Glenn,

I am not sure if we can use Log Reuse description to find if the database is currently being backed up. I have tried it on 9.00.1399.06 build and while the db is being backed up, log_reuse_wait is 0 and desc says 'NOTHING'. Did I miss something how this can be used?

|||

I figured it now. I had to do BACKUP LOG to see the values. Paul's article on this helped me to understand it better. Thanks to KaliBaba & Glenn, I learned something new today.

|||

Glenn,

I think you're talking about log_reuse_wait_desc in sysdatabases, but that's only talking about log backup,

and sql 2005. I needed to clarify, but I need this for sql 2000 as well. Any suggestions other that sysprocesses?

Thanks

Check if Column is property type before Alter Statement

I need to upgrade a column of a table in a script but I would like to check
if the column type is correct first and if so not process the alter table
script
alter table EventVenueTransaction alter column RowGUID uniqueidentifier NOT
NULL
GO
Is there a way I can do this.
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 2/06/2004
You can use the following statement to test for the correct datatype but if
you just want to alter it anyway then there is no need to do the test first.
If the ALTER TABLE statement doesn't change the properties of the column
then it will have no effect anyway.
IF
(SELECT data_type
FROM information_schema.columns
WHERE table_name = 'EventVenueTransaction'
AND column_name = 'rowguid')
<> 'uniqueidentifier'
...
David Portas
SQL Server MVP

Check if Column is property type before Alter Statement

I need to upgrade a column of a table in a script but I would like to check
if the column type is correct first and if so not process the alter table
script
alter table EventVenueTransaction alter column RowGUID uniqueidentifier NOT
NULL
GO
Is there a way I can do this.
Regards
Jeff
--
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 2/06/2004You can use the following statement to test for the correct datatype but if
you just want to alter it anyway then there is no need to do the test first.
If the ALTER TABLE statement doesn't change the properties of the column
then it will have no effect anyway.
IF
(SELECT data_type
FROM information_schema.columns
WHERE table_name = 'EventVenueTransaction'
AND column_name = 'rowguid')
<> 'uniqueidentifier'
...
--
David Portas
SQL Server MVP
--

Check if Column is property type before Alter Statement

I need to upgrade a column of a table in a script but I would like to check
if the column type is correct first and if so not process the alter table
script
alter table EventVenueTransaction alter column RowGUID uniqueidentifier NOT
NULL
GO
Is there a way I can do this.
Regards
Jeff
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.698 / Virus Database: 455 - Release Date: 2/06/2004You can use the following statement to test for the correct datatype but if
you just want to alter it anyway then there is no need to do the test first.
If the ALTER TABLE statement doesn't change the properties of the column
then it will have no effect anyway.
IF
(SELECT data_type
FROM information_schema.columns
WHERE table_name = 'EventVenueTransaction'
AND column_name = 'rowguid')
<> 'uniqueidentifier'
..
David Portas
SQL Server MVP
--sql

Check Constraint

In our database we have an indexed field that is using unique values, so in the index we turned on the unique property. Now with some changes we made in the application this value can be Null (could not be Null in the past). When we have more as 1 value having value Null we get an exception of unique key violation.

Therefor we want to make an check constraint the checks if the value allready exists when the value is not Null.

Is this possible and how can it be done?

You're going to need to use a trigger to check the values in the way you described.|||Hi,

I'm getting the same error.
When I try to add a new user I receive this errror:

ystem.Data.ConstraintException: Column 'User ID' is constrained to be unique. Value '{37525b24-d982-470d-9d1c-e3b3c7536958}' is already present.
at System.Data.UniqueConstraint.CheckConstraint(DataRow row, DataRowAction action)
at System.Data.DataTable.RaiseRowChanging(DataRowChangeEventArgs args, DataRow eRow, DataRowAction eAction, Boolean fireEvent)

Unfortunately I have only one entry on my db with this User ID value. Moreover I have try to delete all my users with BU.
Can anybody help me?

Thanks a lot,

F.T