I am creating a new database in SQL Server 2008 Express R2. In the New Database dialog, I could see an option to set the Recovery Model, which is by default set to: Simple. If I set it to Full, what will happen?

I also see properties like: Auto-Shrink and Encryption. I checked online help for Auto-Shrink property where it shows information about the Compact Edition. But my database will reside on the Server and it is not a compact db. Should I enable it? What are the advantages of enabling it on a server-class database?

I also want to use the Encryption property but I worry that it might make the database unreadable if not handled correctly.

Please throw some light on these.

2 Answers 2


The simple recovery model means that you don't have the option of point in time recovery - essentially, if you take a full backup of the database each day, any data you enter after the backup might be lost if there was a problem. You'd restore to how the data was in the last good backup. As long as your data doesn't change that much between full backups, this is the simplest solution.

Full recovery means you also need to take regular transaction log backups as well - it allows you to restore to a point in time if there is a problem. Typically, you might take a daily full backup of the databases, and transaction log backups every hour. The problem is that if the transaction log backups fail, the transaction logs themselves will grow - and fill the discs if it's not fixed.

Auto-shrink is a setting where SQL Server will trying to shrink the database files when it is idle. It causes performance havoc, and will be removed in a future SQL Server release. Don't use it! (gbn added bold!)

Encryption is a bit more complicated, and best left alone unless you really understand all that is involved IMHO.

  • I do want Point-in-time recovery features but how to keep the transaction logs in control. What type of backup is recommended for a typical standalone application? I heard that SQL Server just have a .mdf and a .ldf file. If something goes wrong, just copy these files to a new installation and Attach. Is it not sufficient?
    – RPK
    Jul 22, 2011 at 11:48
  • FYI: a link to match my added bold sqlskills.com/blogs/paul/post/…
    – gbn
    Jul 22, 2011 at 11:58
  • @RPK If the server melts down, you won't have any mdf or ldf to copy! Regular full backups, and log backups on a schedule that meets your acceptable data loss, like hourly, copied someplace safe are your best insurance.
    – SqlACID
    Jul 22, 2011 at 13:28
  • How to optimize the database every month? Are there any FREE tools?
    – RPK
    Jul 22, 2011 at 15:43
  • Maintenance plans will get you started with standard maintenance functions like backups, reindexing, and checkdb - or alternatively I'm a big fan of scripts by Ola at ola.hallengren.com Jul 24, 2011 at 8:09

Enabling encryption allows your DB to use TDE (Transparent Data Encryption). This mechanism has been implemented in SQL 2008 onwards and provides better overall performance than previous encryption methods. TDE slightly increases CPU and IO usage, so it should be properly tested for systems that already use a lot of CPU and IO. There is a trade off between performance and security.

Using this encryption also involves the setup of security objects, and these objects will have to be properly backed up and retained. So loosing these security objects will pose a problem when you need to revert your encryption.

Full/Simple mode has been answered by Peter Schofield and gbn has pointed out with a link on what to (or not to) do about auto-shrink.

As for the .mdf and .ldf files, they will be working fine until something goes wrong, and that is when you will really need backups. If there are corruptions in your allocation pages, you will have problems when trying to reattach your DB. I would suggest doing a backup and restore rather than detaching and attaching. Since you don't want point-in-time recovery, you will need full backups.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.