What's your best practice Recovery Model for SharePoint databases
Paul Randal asked some really nice questions about best practices for SharePoint SQL databases. Today, while helping a customer to maintain SharePoint installation he asked me a question about best SQL recovery model for SharePoint database.
It is my practice (I am not DB admin :)))) to use Simple recover model. If SharePoint databases are backed up on regular basis and you also have a 3rd party tool backup on item-level basis you really do not need to hold the entire logs.
Am I missing something here? Is this the right approach? Have you ever used SharePoint DB log to recover your data?
Solution 1:
It depends entirely on how much data you are willing to lose versus the amount of administrative effort required. If you are using the simple recovery model and taking backups once a week on Sundays... if you have a crash at 11:59 on Saturday, then you have lost a week of work. Increasing the frequency of backups (or taking differentials) will reduce the amount of data loss.
By taking regular full/differential backups but using the full recovery model with transaction logs, you can restore the last backup and then replay transaction logs to a point in time immediately before the crash, and lose little to no data.
Speaking of Paul Randal... he just wrote a great article on exactly this topic for TechNet Magazine this month :) http://technet.microsoft.com/en-us/magazine/dd822915.aspx
Solution 2:
Backing up just the database will NOT get all your sharepoint information. Sure it will get everything in the database, but all the customizations and the look and feel get lost. This may not matter to you as an admin but I assure you your users will be unhappy.
Options include getting a backup agent that can read the sharepoint database for your backup software, or doing some scripted backups that grab the configuration information and putting that as well as your SQL database backup somewhere safe.
http://technet.microsoft.com/en-us/library/cc288330.aspx Has some information.
TEST your backups. Restore them. See what changes, what works what doesn't. Our first restore was not as good as it could have been. Fortunately for us it was just part of the process in making a test server that was a duplicate of our production server, rather than trying to recover lost or destroyed data.
Edited for relevance Upon reading this again I realized I got distracted and missed the answer point of my answer. If you do full backups with transaction logging you can roll back to much finer points in time. This does require more skill as a DBA but it isn't that hard. If you don't have a ton of updates and losing a whole day's work isn't the end of the world then you are probably fine. Other options include running the simple backup more often. Say Midnight, 10AM, 2PM, 6PM, or whatever works for organizations work cycle. This will eat up more disk, but reduce your data loss risks. As with all backups it is a balance between what the users will tolerate and what the admins can provide.