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!