Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

How should I backup my 2010 SharePoint server? As I see it there are 3 choices:

  1. Full Farm backup
  2. Site collection backup
  3. SQL Server backup

We're only doing backups using points 1 (occasionally) and 2 (nightly, using a PowerShell script).
I get the feeling (but can't find evidence to confirm it) that by just using SharePoint you do not backup all the information you need?

If I was to also backup SharePoint data using option 3 SQL Server what would I need to backup?

At present there are 22 databases in the SQL data directory, do I need to backup all of them?

share|improve this question

6 Answers 6

up vote 11 down vote accepted

There are lots of point to discuss before coming to a conclusion here. But I will just explain the different possible ways you could do backup in SharePoint in an order of good to best.

  1. Central Admin Backup - This is a default UI option that comes within Central Administration. It allows to take full and differential backups. You have the additional flexibility to backup and restore Search functionality. This is not a full proof backup plan though due to limitations in scheduling, backing up config db, config settings in the farm and failure of timer jobs.

  2. Powershell or Stsadm Backup - Powershell gives you high amount of flexibility in taking full or differential backups. Windows task scheduler helps in scheduling the scripts. It also takes backup of content dbs and search index files and is much more reliable than Central Admin Backup. This also has limitations like lack of backup of config and IIS settings.

  3. SQL Server Backup - As we know, SQL full and differential backups, enable us to bring back the SharePoint sites and config db almost 100 percent. This is comparatively the fastest backup and restore method, with more reliability than the above methods. But lacks backup of farm, IIS settings and search crawl indexes, which will have to be done manually.

  4. Microsoft Data Protection Manager - aka DPM, this is the tool that promises to give you the best shot for SharePoint backups. You can backup and restore literally everything related to SharePoint using this single tool.

    • All DBs whether content or config
    • Farm settings, IIS settings and system state settings.
    • Backup and restore of 14 hive or other file directories.
    • Farm level backup even if the farm is in running state.
    • Removes the processing burden in the farm during backups and increase in performance.

    The only limitation would be the additional funding required to buy this tool.

If you need more detailed understanding about backup and restore, go through this Technet article or check out the detailed SharePoint Backup and Recovery slides from Joel Oleson

share|improve this answer
    
The breakdown of options is very useful, thanks. –  best Mar 2 '12 at 8:22
    
+1 for Microsoft Data Protection Manager –  Tom Resing Mar 6 '12 at 22:06
    
One option missing in this list, which IMO is by far the best option, is third-party backup tools. There is a cost but it is well worth the cost because it lowers the management overhead and cost, and backup/recovery time span is much less. –  Hossein Aarabi Jul 28 '12 at 22:33

Doing the SQL Server backup of the content databases is almost identical to what you are doing with the PowerShell script: protecting your content. This is of course the most important thing that you will want to protect. The advantage that doing these backups through a maintenance plan on SQL gives you is that you can perform both full and differential backups and have them automate at different times through the day to try to limit the amount of data lost should there be a failure. Currently if you do your backups at say 8PM and there is a failure at 7:50PM, you could potentially lose a whole day's worth of data. By setting up a full backup at 8PM and differential backups at every 2-4 hours then you can reduce the amount of data lost should there be failure prior to the next full backup.

There are a lot of other approaches to Disaster recovery that you can take to help with these backups. There are also third party backup tools that will help you recover all of your farm settings as well as data. You may want to consider investigating these tools as well.

share|improve this answer
    
Would you suggest doing only regular "standard" SQL backups & maintenance plans work, scraping our PowerShell SharePoint backup. Then complementing this with a Farm backup at longer intervals? –  best Mar 1 '12 at 18:50
1  
You don't need to scrap the powershell backups unless you choose to. This will do that same thing. I'm kind of a backup overkill fan, so would leave both in place. And yes, I would just complete a farm backup at longer intervals. Maybe every week or two. –  Lori Mar 1 '12 at 19:15
    
This is similar to what we do, except we only do the database backups and that has allowed us to pull Prod content to our Test and Dev systems just by restoring the content databases. –  MichaelF Mar 1 '12 at 21:42
    
We do the same, but it is important to make sure the different environments have exactly the same SharePoint version (and same updates) installed. –  Russell Mar 1 '12 at 22:34
    
@best - With regards to just the database, you can also set it to use the Full recovery model and never lose any data. Hop on over to DBA.SE if you want to ask about the nitty gritty of database recovery models and backup strategies. –  Nick Chammas Mar 2 '12 at 1:03

As with all things SharePoint, the answer is: "It Depends". In this case, it depends on your business requirements and recovery needs. Microsoft has some good documents to get you started and they also provide a worksheet

share|improve this answer
    
"It depends" is the most frustrating part of trying to learn how to administer a SharePoint system. Thanks for the links in the right direction. –  best Mar 1 '12 at 18:41
1  
I hate to be the bearer of bad news but that really is the answer to most questions related to the management of SharePoint. It is such a large system and is used by such a wide variety of companies and in so many different ways that questions like this usually do not have just one right answer. What is right for one company in one situation may be completely wrong for another. –  Dave Wise Mar 1 '12 at 19:06

Try not to put all your eggs in the one basket. For example a nightly SQL backup plan, and a weekly farm backup. This gives you options and alternatives depending on circumstances. Then if for some reason you are unable to restore a farm backup, you can use SQL backups, and vice versa.

SQL backups allow you to backup transactions to the minute, helping minimise data loss.

SharePoint 2010 allows you to attach a content DB to restore content quickly, so I suggest having SQL content DB backups available. This can be a common business request ("I accidentally deleted a column in my list.")

share|improve this answer

The biggest determining factor here between SharePoint Backups and SQL Server backups is going to be how much content you have in your farm. Based on what I could find on TechNet, SharePoint backups are not supported for content databases over 200 GB in size (http://technet.microsoft.com/en-us/library/cc262787.aspx) and site collections over 100 GB in size (http://technet.microsoft.com/en-us/library/cc261687.aspx). If your farm has targets over either of those thresholds, you need to use SQL Server or a third party product.

If you're under those thresholds, I'll give you two things to consider when determining your backup strategy (there's way more than that, you could probably fill a whole book with stuff like that or something...) in general with either of these backup tools:

  1. Scheduling - which tool are you more comfortable creating a scheduling mechanism for backups with: PowerShell and Scheduled Tasks in the case of SharePoint backups and Maintenance Plans (or your existing SQL Server backup tool) in the case of SQL Server. If you already have a backup tool for SQL Server it may make more sense to integrate into that approach rather than creating something new for SharePoint.
  2. How are you going to restore? This is a tricky one, but very important. With SharePoint recovery, the approach I always try to take is to preserve your content and know how to build a new environment to restore that content into. You have to build a new SharePoint farm to restore into, and my read on MS's documentation is that they don't support restoring a configuration database from backup, which is why I encourage focusing on content and change management for a successful restore. This also places less emphasis on service applications unless you have a specific business need for them to be restored immediately after a loss. If you're really focusing just on the content, which means just the SharePoint content databases, you will find that its easier to target them w/ SQL Server backups instead of SharePoint backups. But, if you need more of the farm or if you need service apps, then SharePoint backups may be easier to cover with a broad stroke.

Does that make sense?

John

share|improve this answer
    
Yes it makes sense, very helpful for my understanding of exactly what I'll need to restore. With so many SQL databases for Content, Config, Search & Analytics as a part time SharePoint Admin / DBA its quite overwhelming when learning what is irreplaceable and what you can re-create with a clean install –  best Mar 2 '12 at 8:21
    
If you're looking for more info on the topic, there are a couple of books out there you could take a look at for a lot of deep information on the subject: - amazon.com/SharePoint-2010-Disaster-Recovery-Availability/dp/… by Stephen Cummins - amazon.com/dp/1435456459?tag=ferringercom-20 by Sean McDonough and myself –  John Ferringer Mar 2 '12 at 19:17
    
+1 for the limitations discussion. –  Tom Resing Mar 6 '12 at 22:05

If you do a full SP farm backup, there is no need to perform option 2 and 3.

Beside doing a backup, make sure you can also do a recovery.

OOB SP and SQL Server backups do not provide the full capabilities that third-party tools provide. If you have a medium size farm or bigger, I suggest you buy a backup third-party tool based on the following factors:

  • Can it perform the backup/restore process fast enough to meet your maintenance window.
  • How complete is the recovery?
  • How granular can you recover? (some tools allow you recover very granular objects, such as list item, without the need to restore the whole SP farm)
  • What backup types it supports? (full, differential, incremental)
  • How easy it is to use the tool?
  • How expensive is it?
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.