Checking what recovery model a DB has

[Robbomobb]

Is there a way to see what kind of recovery model all my db´s have using T-SQL.

At my company we´re using a third party backupsystem for our sql servers. The log backups fail on some db´s due to recovery model is being set to SIMPLE. There are many DB´s in my system and i don´t have the time checking them manually.

I was thinking of automizing it, but i can´t find where this information is being stored.

Thankful for all replies

 

[Glyndwr]

Robbomobb

I think this does what you want

create table #dbRecoveryModel (dbname sysname , recoverymodel sql_variant)

insert into #dbRecoveryModel exec sp_msforeachdb 'SELECT ''?'' , databasepropertyex(''?'', ''recovery'')'
select * from #dbRecoveryModel

 

[SamGarland]

To find all databases in an instance of SQL Server that have the simple recovery model use:

SELECT name
FROM sysdatabases
WHERE DATABASEPROPERTYEX(name, 'RECOVERY') = 'SIMPLE'

 

[Robbomobb]

SamGarland

That was exactlly what i was looking for, it worked like a charm. Thanks for the help.

Glyndwr

Thanks for the reply, but i didn´t get it to work. The second time i executed the script i got an error message that said the table already existed. I thought putting an # infront of the tablename made it a temporary table which is supposed to be dropped after the query ends. Anyway thanks for your time.

 

[Glyndwr]

Robbomobb

if you put the code in a stored proc the temp table will disappear, if you run it in QA then it will remain until you drop it

 

[Robbomobb]

Glyndwr

Ok, now i know why it remains. Thanks for the input.

 

[SQLBill]

Robbomobb,

A temporary table 'hangs' around until the session ends. With a stored procedure the session ends when the sp finishes. When you are running Query Analyzer, the session ends when you quit Query Analyzer.

But it's usually best practice to use DROP TABLE for temporary tables.

-SQLBill

 

[Robbomobb]

I´m not an SQL-wizard and i had to try out what you´ve suggested, there´s a problem thou. How do i make a stored procedure out of an query? i´m not sure about it.

 

[SQLBill]

With SQL Server you also get the Books OnLine (BOL). Find it at Start>Programs>Microsoft SQL Server>Books OnLine. Use the Index tab and enter CREATE PROCEDURE.

Basically, you do this:

CREATE PROCEDURE usp_myprocname
AS
<code>

Then you run it by using:

EXEC usp_myprocname

Do NOT start your procedures name with sp_. That is poor progamming. Why? When SQL Server has to run a procedure with the beginning sp_ it begins looking in the Master database regardless of where the procedure actually might be.

You can also use variables in your procedure. Check the BOL.

-SQLBill

 

[Robbomobb]

Ok, got it. I´m familiar with BOL but i didn´t understand the concept when reading it. Anyhow, i got it to work now. Thanks