SQL Recovery models

13 Dec

The default is “Full” which you can view in SSMS by going to database properties->options. Full is the best recovery option, but is it the best option for you? Full has the highest likelihood of causing transaction log issues because every IUD is logged. If you create a new database with the default settings and never back it up, your log file will grow and grow and grow until it fills up the disk.

My theory is “Simple” mode is the best choice for 80% of SQL databases. Simple mode and nightly backups. If you have a group of 20 users and your database fails at 2:10pm in the afternoon some users might be angry that they have to re-do all their work that day so proceed with caution. With simple you won’t have the option of a point in time recovery if your data file is corrupt so you’ll have to go to your nightly full. However, most application users totally accept the fact that they will have to re-do 1 day of work given the circumstances of a server or drive failure. Also, in the case of a DBA oops like dropping or truncating a table you can restore your nightly full to a separate database and do an object level restore. You will have to understand the design of the tables and relationships to pull this off but it is possible.

There is a happy medium recovery model called “Bulk-Logged”. For most transactions it gives you the point-in-time restore capability and for bulk operations it will give you the ability to restore before or after the operation.

So what do you choose??? It depends. If you don’t know all of your users or have transactions you can’t get back, choose full.

Leave a comment

Posted by on December 13, 2010 in SQL Admin


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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: