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 im backing up my databases

Is there a way I can find out if my databases are being backed up daily on a
daily basis on a server ?
I want to be able to run a script and list all databases not being backed up
dailyHassan
If you build database maintenance plan and check all user database , you
are sure that all databases are being backed up
as well as theit log files (I assume with FULL recovery mode)
"Hassan" <Hassan@.hotmail.com> wrote in message
news:e5qgFV6AGHA.140@.TK2MSFTNGP12.phx.gbl...
> Is there a way I can find out if my databases are being backed up daily on
> a daily basis on a server ?
> I want to be able to run a script and list all databases not being backed
> up daily
>|||Something like
select d.name as [Database],
max(b.backup_finish_date) as [Last Backup],
case
when datediff(hh,isnull(max(b.backup_finish_date),'19000101'),getdate()) >
24
then 'N' else 'Y' end as [Recent Full Backup]
from master.dbo.sysdatabases d
left join msdb.dbo.backupset b
on d.name = b.database_name and b.type = 'D'
group by d.name
order by d.name
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <Hassan@.hotmail.com> wrote in message
news:e5qgFV6AGHA.140@.TK2MSFTNGP12.phx.gbl...
> Is there a way I can find out if my databases are being backed up daily on
> a daily basis on a server ?
> I want to be able to run a script and list all databases not being backed
> up daily
>|||Hi
ALTER DATABASE DataBase SET RECOVERY FULL
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:ulbMSq8AGHA.3104@.TK2MSFTNGP12.phx.gbl...
> Something like
> select d.name as [Database],
> max(b.backup_finish_date) as [Last Backup],
> case
> when datediff(hh,isnull(max(b.backup_finish_date),'19000101'),getdate()) >
> 24
> then 'N' else 'Y' end as [Recent Full Backup]
> from master.dbo.sysdatabases d
> left join msdb.dbo.backupset b
> on d.name = b.database_name and b.type = 'D'
> group by d.name
> order by d.name
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:e5qgFV6AGHA.140@.TK2MSFTNGP12.phx.gbl...
>> Is there a way I can find out if my databases are being backed up daily
>> on a daily basis on a server ?
>> I want to be able to run a script and list all databases not being backed
>> up daily
>|||Sorry, wrong post
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e1uO608AGHA.4036@.TK2MSFTNGP10.phx.gbl...
> Hi
> ALTER DATABASE DataBase SET RECOVERY FULL
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:ulbMSq8AGHA.3104@.TK2MSFTNGP12.phx.gbl...
>> Something like
>> select d.name as [Database],
>> max(b.backup_finish_date) as [Last Backup],
>> case
>> when datediff(hh,isnull(max(b.backup_finish_date),'19000101'),getdate())
>> > 24
>> then 'N' else 'Y' end as [Recent Full Backup]
>> from master.dbo.sysdatabases d
>> left join msdb.dbo.backupset b
>> on d.name = b.database_name and b.type = 'D'
>> group by d.name
>> order by d.name
>> --
>> HTH
>> Jasper Smith (SQL Server MVP)
>> http://www.sqldbatips.com
>> I support PASS - the definitive, global
>> community for SQL Server professionals -
>> http://www.sqlpass.org
>> "Hassan" <Hassan@.hotmail.com> wrote in message
>> news:e5qgFV6AGHA.140@.TK2MSFTNGP12.phx.gbl...
>> Is there a way I can find out if my databases are being backed up daily
>> on a daily basis on a server ?
>> I want to be able to run a script and list all databases not being
>> backed up daily
>>
>

Check if im backing up my databases

Is there a way I can find out if my databases are being backed up daily on a
daily basis on a server ?
I want to be able to run a script and list all databases not being backed up
daily
Hassan
If you build database maintenance plan and check all user database , you
are sure that all databases are being backed up
as well as theit log files (I assume with FULL recovery mode)
"Hassan" <Hassan@.hotmail.com> wrote in message
news:e5qgFV6AGHA.140@.TK2MSFTNGP12.phx.gbl...
> Is there a way I can find out if my databases are being backed up daily on
> a daily basis on a server ?
> I want to be able to run a script and list all databases not being backed
> up daily
>
|||Something like
select d.name as [Database],
max(b.backup_finish_date) as [Last Backup],
case
when datediff(hh,isnull(max(b.backup_finish_date),'1900 0101'),getdate()) >
24
then 'N' else 'Y' end as [Recent Full Backup]
from master.dbo.sysdatabases d
left join msdb.dbo.backupset b
on d.name = b.database_name and b.type = 'D'
group by d.name
order by d.name
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <Hassan@.hotmail.com> wrote in message
news:e5qgFV6AGHA.140@.TK2MSFTNGP12.phx.gbl...
> Is there a way I can find out if my databases are being backed up daily on
> a daily basis on a server ?
> I want to be able to run a script and list all databases not being backed
> up daily
>
|||Hi
ALTER DATABASE DataBase SET RECOVERY FULL
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:ulbMSq8AGHA.3104@.TK2MSFTNGP12.phx.gbl...
> Something like
> select d.name as [Database],
> max(b.backup_finish_date) as [Last Backup],
> case
> when datediff(hh,isnull(max(b.backup_finish_date),'1900 0101'),getdate()) >
> 24
> then 'N' else 'Y' end as [Recent Full Backup]
> from master.dbo.sysdatabases d
> left join msdb.dbo.backupset b
> on d.name = b.database_name and b.type = 'D'
> group by d.name
> order by d.name
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:e5qgFV6AGHA.140@.TK2MSFTNGP12.phx.gbl...
>
|||Sorry, wrong post
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e1uO608AGHA.4036@.TK2MSFTNGP10.phx.gbl...
> Hi
> ALTER DATABASE DataBase SET RECOVERY FULL
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:ulbMSq8AGHA.3104@.TK2MSFTNGP12.phx.gbl...
>

Check if im backing up my databases

Is there a way I can find out if my databases are being backed up daily on a
daily basis on a server ?
I want to be able to run a script and list all databases not being backed up
dailyHassan
If you build database maintenance plan and check all user database , you
are sure that all databases are being backed up
as well as theit log files (I assume with FULL recovery mode)
"Hassan" <Hassan@.hotmail.com> wrote in message
news:e5qgFV6AGHA.140@.TK2MSFTNGP12.phx.gbl...
> Is there a way I can find out if my databases are being backed up daily on
> a daily basis on a server ?
> I want to be able to run a script and list all databases not being backed
> up daily
>|||Something like
select d.name as [Database],
max(b.backup_finish_date) as [Last Backup],
case
when datediff(hh,isnull(max(b. backup_finish_date),'19000101'),getdate(
)) >
24
then 'N' else 'Y' end as [Recent Full Backup]
from master.dbo.sysdatabases d
left join msdb.dbo.backupset b
on d.name = b.database_name and b.type = 'D'
group by d.name
order by d.name
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Hassan" <Hassan@.hotmail.com> wrote in message
news:e5qgFV6AGHA.140@.TK2MSFTNGP12.phx.gbl...
> Is there a way I can find out if my databases are being backed up daily on
> a daily basis on a server ?
> I want to be able to run a script and list all databases not being backed
> up daily
>|||Hi
ALTER DATABASE DataBase SET RECOVERY FULL
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:ulbMSq8AGHA.3104@.TK2MSFTNGP12.phx.gbl...
> Something like
> select d.name as [Database],
> max(b.backup_finish_date) as [Last Backup],
> case
> when datediff(hh,isnull(max(b. backup_finish_date),'19000101'),getdate(
)) >
> 24
> then 'N' else 'Y' end as [Recent Full Backup]
> from master.dbo.sysdatabases d
> left join msdb.dbo.backupset b
> on d.name = b.database_name and b.type = 'D'
> group by d.name
> order by d.name
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:e5qgFV6AGHA.140@.TK2MSFTNGP12.phx.gbl...
>|||Sorry, wrong post
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:e1uO608AGHA.4036@.TK2MSFTNGP10.phx.gbl...
> Hi
> ALTER DATABASE DataBase SET RECOVERY FULL
>
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:ulbMSq8AGHA.3104@.TK2MSFTNGP12.phx.gbl...
>