Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Sandwiches

Sqlsandwiches is a tool for me to communicate what I have been learning to the SQL community.

Recovery Model for Every Database

One thing that is enjoyable about my position is working with people who have no idea what SQL is besides “a difficult version of excel”. This gives me the chance to use what I have learned over the last couple of years and explain it the people who are using it in easy digestable….sandwiches, if you will.

One thing I see lot of is FULL recovery mode with no log backups. Sometimes these databases get tossed into prodution environments by third party tools that have full admin rights. Being I haven’t been here that long, I am still working on locking down all the production servers. So I needed a quick way to find which DBs were in SIMPLE and which DBs were in FULL.

So I ran into a very very cool function recently. DATABASEPROPERTYEX. If you ask this dude the status of a database, he’ll give it to you. Want to know the version? Yup, he can do that too. For the full list, check out – BOL. I used this guy to tell me the recovery model of every DB on that server.

Check it out:

SELECT [name] AS dbname, Databasepropertyex([name], ‘Recovery’) AS recoverymodel FROM MASTER.dbo.sysdatabases 

Team that up with registered servers and you can find out the recovery model of every DB on all your servers!

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.