9

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?

Community
  • 1
Toni Frankola
  • 724
  • 1
  • 7
  • 14

4 Answers4

8

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

Sean Earp
  • 7,207
  • 3
  • 34
  • 38
  • Laura adds a very good point... I answered the question as asked, but a better question might be "what is the best way to backup SharePoint?" If you just do SQL Server backups, you will have to recreate your Configuration database and manually reattach your content DBs. If you use a SharePoint aware backup application such as Data Protection Manager (http://www.microsoft.com/dpm), it will take care of the database backups (including the config DB) and will still allow you to do point-in-time restores of SharePoint. Muuuuch easier than doing it all manually. – Sean Earp Jun 08 '09 at 21:28
  • Backup is another question we might debate about. DPM is nice but it is not a SME solution. What would you recommend for a single-server (small bussines) farm environments? stsadm backup, symantec or something else? – Toni Frankola Jun 08 '09 at 21:38
  • 1
    Unfortunately, the SharePoint backup story has more "it depends" than any other product I have worked with. Are we talking farm-level backup? Disaster recovery? Site collection backup? Site backup? The SharePoint backup resource center on TechNet has some great resources that walk you through deciding which tool to use for backing up which aspect of SharePoint. As long as you don't mind reconfiguring everything in the config database (you have it documented, right?) doing SQL backups of the content databases will work fine for protecting the farm as a whole. – Sean Earp Jun 08 '09 at 22:52
  • Whoops! forgot the URL: http://technet.microsoft.com/en-us/office/sharepointserver/bb736212.aspx – Sean Earp Jun 08 '09 at 22:52
  • If I back up my SP configuration database plus all the databases related to my various service applications, am I able to rebuild my farm and just mount all those databases and go? – Aaronster Dec 20 '13 at 19:58
5

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.

Laura Thomas
  • 2,825
  • 1
  • 26
  • 24
2

Sharepoint needs to be treated like a SQL database because it IS a SQL database so take all your regular SQL setup precautions in setting up shop. As for backups you should not only be backing up your databases regularly you need to back up your 12-hive which holds all of your SP information.

Check out this thread for more info: http://social.technet.microsoft.com/Forums/en-US/sharepointgeneral/thread/102c5c71-38a3-4360-b5cb-9b8b7c07bfea

SQLChicken
  • 1,307
  • 8
  • 10
  • Not sure why this was downvoted... SQLChicken is correct with the exception of SSP. That requires special care and feeding because of Search Indexes which are NOT in SQL Server. – Jeff Jun 09 '09 at 01:17
  • 2
    I really wish that ServerFault would force people to leave a comment if they downvote... – SQLChicken Jun 10 '09 at 21:03
0

There are some databases that are set to Simple mode out of the box. The Search database, for example. Search data is stored in two locations: a database and the index file on the server file system. You need both to serve search queries, and both of them backed up concurrently for any restored version to work. Since the odds of that are very, very low, most people would opt to simply recrawl their content and regenerate the search index.

In this case, Simple mode would work just fine.

Jeff Costa
  • 481
  • 3
  • 9