|
I am considering switching from SIMPLE to FULL recovery model in order to support backup/restoring different Filegroups. What are the space (and other resource) implications from switching from SIMPLE to FULL recovery For example, does the ldf file get larger?
(comments are locked)
|
|
Swapping from SIMPLE to FULL will bring a few changes to your system. First of all, you will now need to run LOG backups. Previously (with SIMPLE) you didn't need to do this at all. This is a requirement to make sure that the changes you have committed to the database are "hardened" in the data file. Pay attention to this, especially how often you run a backup. If you have lots of data movement (inserts, updates, deletes) in the database, the volume of data in the log file will be greater. Without log backups the data changes will remain in the log file and the LDF file will continue to grow. The next part to consider is that you now have the ability to perform a point in time recovery. This may not be a necessity for you (you mention filegroup restores being the reason for FULL recovery), but is the main reason for using the FULL recovery model. With the Log file backups required with the FULL recovery model, you also need to consider the additional hard disk space needed to hold backup files. The additional overhead of administration of backups/restores is another point to consider. Take a look at the Recovery Model Overview on MSDN to see a little more detail on each recovery model. Thank you.
Nov 09 '11 at 10:53 AM
MakipR
(comments are locked)
|
|
Simply the main difference in two recovery models. In SIMPLE log file inactive portion is truncated automatically by truncate logon check point. So data is not available in that inactive portion so we are not able to take a log backups. So if we don't have log backups we are not able to do point in time and point of failure restoration. Full recovery model the inactive portion of log file is not truncated automatically. So we can able to take a log backups. When we are taking the log backup need maintain those files in hard drive space is impact. While taking a log backup the inactive portion of ldf file is truncated and that space is free. So we have a chance to do pint in time and point of failure restoration.
(comments are locked)
|
|
I would recommend you to read Understanding SQL Server Recovery Model article which explains three different types of recovery models available in SQL Server and when to use a specific recovery model.
(comments are locked)
|

