Recovery Models in SQL Server 2005

Knowing what recovery model your database is using has important ramifications for database administration and management, especially during recovery. And even more especially, Disaster Recovery! The following code will show the recovery model for all your databases that aren’t system db’s:

select name, recovery_model, recovery_model_desc, state_desc
from sys.databases
where owner_sid .isnotequal. 0x01
order by name;

You need to replace the .isnotequal. with a less than and a greater than key. You can, of course, remove the where clause if you want the system databases included. In my experience casual databases (those which aren’t critical, production db’s for which you need up-to-the-second backups) and more easily managed with the simple recovery model. You do need Full for database mirroring and business dbs, however.
If you want to change a database to the simple recovery model you can use a script like the following:

use [master];
alter database [my_database] set recovery simple with no_wait;

The trouble with the Full Recovery model is that you need to keep on top of it. You’re in charge of shrinking it down when it gets full of old transactions.
You can issue the following command when using a particular database to see how many virtual log files are contained in your current log (there’ll be one row for each and anything beyond 20 rows is probably too much):


And you can shrink your logs using this:

exec sp_helpfile;
exec sp_helpfile;


About tgrignon

I came I saw I rented the DVD
This entry was posted in Miscellany and tagged , , , . Bookmark the permalink.

One Response to Recovery Models in SQL Server 2005

  1. Pingback: Recovery Models in SQL Server 2005 – Part 2 « Golbing

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s