I am using SQL Server 2005 Express Edition. I noticed that Recovery Model is set to Simple, by default. I changed this option to Full but want to know which other options to enable to guarantee full-recovery if there is no backup.
Is there any way to take scheduled backups of SQL Server 2005 Express databases?
Best Answer
One answer to add to help explain Recovery models in a nutshell, the terminology (especially with Full also being a type of backup) confuses people.
We'll skip bulk logged which is a hybrid recovery model that is ideal when you are doing certain bulk operations and can programatically change your recovery model as part of the processing.
Quick Background - SQL Server maintains a write ahead transaction log. In the example of your application making an update - the update is written to the log first (and HAS to be written for the transaction to be considered committed). The write will eventually make it to the data files at certain points but it doesn't need to be in the event of a restart/crash of SQL Server. Because it is hardened to the log file, SQL has the right info to recover or "replay" that transaction if it was all done before the crash or roll it back or "undo" the transaction if it wasn't all done. This log file continues to grow and grow and grow depending on your recovery model:
Full Recovery Mode --> You are telling SQL Server "I really care about being able to restore to a point in time." So this means, essentially, that you want SQL to keep it's transaction log records even beyond what is necessary just for that crash recovery but you want to keep the log records, back them up on a schedule and have the ability to restore to a specific point in time. Under this recovery model your log file continues to grow until you take a backup and then it can be truncated (physical size stays where it has grown to but some portion of the log file can be reused based on some other rules)
Simple Recovery Mode --> "I don't need to go to a specific point in time, my last good full backup is fine." You are telling SQL Server to just truncate that log file or reuse the space within it as SQL sees fit - when the records are no longer needed for that crash/restart recovery, basically.
Under either mode above you have the ability to fully recover your database, if you mean - restore it to a known good point. Simple mode means your recovery can only be the last good full backup. Full mode gives you the ability to restore to a specific point of time if your log file is intact at the time of failure or to the last good log backup if not.
So the question is - what are your RPOs/RTOs promised and agreed to with your business unit (http://www.straightpathsql.com/archives/2011/02/can-you-restore-sql-server-sqlu/) to determine which mode. Just remember, if going to full it is imperative that you take transaction log backups or face log file growth that just keeps going. Also remember that when you switch from simple to full, you must take a full backup to initialize your change before you are able to start taking log backups.
More on SQL Server Recovery Models - http://msdn.microsoft.com/en-us/library/ms189275.aspx