473,320 Members | 2,029 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

determine tables that are Simple recovery

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
5 2742
"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
Erp! Yes, replace(question,'Tables','Database')
Thanks!

Oct 14 '05 #3
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
"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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: joshsackett | last post by:
Hi All, I have a 2MB database set to SIMPLE recovery. This database is used only to generate new keys to web users. It has two columns - UID and LASTDATE. The UID column is only updated when users...
3
by: Raquel | last post by:
I am a newbie to UDB and reading the backup processes on UDB which look pretty good to me. Now, if these good backup and recovery procedures are already available 'within' UDB, what extra...
3
by: (Pete Cresswell) | last post by:
Seems like creating a #temp table is kind of useless because it cannot be bound to a subform. OTOH, seems like a waste of resources to populate work tables in the "real" database - both because...
25
by: _DD | last post by:
I'd like to include a 'Test Connection' button in an app, for testing validity of a SQL connection string. I'd prefer to keep the timeout low. What is the conventional way of doing this?
4
by: Troels Arvin | last post by:
Hello, I've run into situations where a table was accidentally dropped. The related database contained lots of other tables in lots of other schemas used by many different users. So I couldn't...
0
by: ttcdr | last post by:
We have MS SQL 2000 server, the log file of our Database growth to 23G. If I change the database from Full Recovery mode to Simple Mode, the log file should shink. Am I correct??? And how do...
1
by: Murdz | last post by:
Hello all, The HDD that holds the logs for one of our DB servers has run out of space (More has been ordered). Part of the reason for this is the log files have gotten rather large, as such the...
0
by: Winder | last post by:
Computer Data Recovery Help 24/7 Data recovering tools and services is our focus. We will recover your data in a cost effective and efficient manner. We recover all operating systems and media....
4
by: DaveL | last post by:
hi, we have a database in simple recovery mode if a table were dropped Is there a way to recover the dropped table thanks DaveL
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.