Backup via SharePoint or backup via SQL Server?

Question

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?

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

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

Answer 5

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.")

Answer 6

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?