determine tables that are Simple recovery

[rcamarda]

I need a sql statment to return a list of tables for a given database
where the Recovery Model option is Simple.
TIA
Rob

[David Portas]

Re: determine tables that are Simple recovery

"rcamarda" <rcamarda@cable speed.com> wrote in message
news:1129311952 .833612.171530@ g14g2000cwa.goo glegroups.com.. .[color=blue]
>I need a sql statment to return a list of tables for a given database
> where the Recovery Model option is Simple.
> TIA
> Rob
>[/color]

The recovery model is a database-wide setting. There is no recovery model
option for individual tables.

You can determine the recovery model like this:

SELECT DATABASEPROPERT YEX('database_n ame', 'RECOVERY')

--
David Portas
SQL Server MVP
--

[rcamarda]

Re: determine tables that are Simple recovery

Erp! Yes, replace(questio n,'Tables','Dat abase')
Thanks!

[rcamarda]

Re: determine tables that are Simple recovery

Better Question. How can it tell the databases in my sql instance that
have a recovery model of 'Simple'?
Hope this makes sense now
Thanks

[David Portas]

Re: determine tables that are Simple recovery

"rcamarda" <rcamarda@cable speed.com> wrote in message
news:1129328233 .934256.231310@ g49g2000cwa.goo glegroups.com.. .[color=blue]
> Better Question. How can it tell the databases in my sql instance that
> have a recovery model of 'Simple'?
> Hope this makes sense now
> Thanks
>[/color]

SELECT catalog_name
FROM information_sch ema.schemata
WHERE DATABASEPROPERT YEX(catalog_nam e,'RECOVERY') = 'SIMPLE' ;

--
David Portas
SQL Server MVP
--

[rcamarda]

Re: determine tables that are Simple recovery

Thank you very much! Ill be able to use this in my backup using SQLsafe
when I perform a log backup. I kept getting errors when it tried to
backup databases with simple model. Now I can skip them.