| Author |
determine tables that are Simple recovery
|
|
| rcamarda 2005-10-27, 9:24 am |
| 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 2005-10-27, 9:24 am |
| "rcamarda" <rcamarda@cablespeed.com> wrote in message
news:1129311952.833612.171530@g14g2000cwa.googlegroups.com...
>I need a sql statment to return a list of tables for a given database
> where the Recovery Model option is Simple.
> TIA
> Rob
>
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 DATABASEPROPERTYEX('
database_name', 'RECOVERY')
--
David Portas
SQL Server MVP
--
| |
| rcamarda 2005-10-27, 9:24 am |
| Erp! Yes, replace(question,'Ta
bles','Database')
Thanks!
| |
| rcamarda 2005-10-27, 9:24 am |
| 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 2005-10-27, 9:24 am |
| "rcamarda" <rcamarda@cablespeed.com> wrote in message
news:1129328233.934256.231310@g49g2000cwa.googlegroups.com...
> 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
>
SELECT catalog_name
FROM information_schema.schemata
WHERE DATABASEPROPERTYEX(c
atalog_name,'RECOVER
Y') = 'SIMPLE' ;
--
David Portas
SQL Server MVP
--
| |
| rcamarda 2005-10-27, 9:24 am |
| 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.
|
|
|
|