Evening all, hope the weekend went well for everyone. Just a short one today as I’m still in the thick of it with my SAN, oh the joys!
This topic cropped up yesterday when a colleague of mine was setting up a 60GB database for mirroring, and after spending hours transferring the file, discovered he had the database set to use the wrong recovery model and had to start the whole lot again. Rather him than me!
Here is an in depth look at recovery models from Microsoft, and goes into all the detail you’ll need. Below is just a quick break down for an at a glance reference.
Simple mode
This is the minimal amount of logging you can set. It doesn’t do any transaction log tracking, which is why you can’t use this method for database mirroring (mirroring needs transaction log tracking to make sure your mirrored DB is accurate up to the second.). This would be for a low activity database which doesn’t require up to the second backups. In the event of a failure transaction logs can’t be restored so you will only have data up to your last full or differential backup
Bulk Logged
This should only really be used in conjunction with the Full recovery model. If you are doing bulk operations and don’t have the space available to fill your log file, switch to Bulk Recovery temporarily. It will record your data, but bulk operations are only minimally logged to save space in the log file.
Full Recovery
This model records everything that occurs on your database, and also allows for transaction log backups, which in turn allows point in time recovery. What this means is that if you have bad data inserted into your database at 8:45, you can take a transaction log backup, and restore your database to 8:44, losing a minimal amount of data. This is the method you want to use for maximum recovery, and also to allow database mirroring. If you have a transactional system, this is the method to use, as particularly with financial transactions, losing minutes means losing money.
Sorry this is so short, but it’s very essential to learn the difference between the methods, and choose the correct model for your scenario.
The best way to check your current model is to open SQL Management Studio, right click on your database, and click properties. Once in there click on options in the side bar, and look at the recovery model section in there. If you down arrow on the current model, you will be able to select the model you want for your database, and the change can be made on the fly. You can’t switch to Simple if Full is enabled and you have mirroring, or some other log file specific operation enabled on the server, but don’t panic if error messages appear, just read the message closely and it will guide you in the right direction.
Good luck!