By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,968 Members | 1,593 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,968 IT Pros & Developers. It's quick & easy.

determine tables that are Simple recovery

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

Oct 14 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
"rcamarda" <rc******@cablespeed.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.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
--
Oct 14 '05 #2

P: n/a
Erp! Yes, replace(question,'Tables','Database')
Thanks!

Oct 14 '05 #3

P: n/a
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

Oct 14 '05 #4

P: n/a
"rcamarda" <rc******@cablespeed.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.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(catalog_name,'RECOVERY') = 'SIMPLE' ;

--
David Portas
SQL Server MVP
--
Oct 15 '05 #5

P: n/a
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.

Oct 15 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.