Disaster Recovery: SQL 2008 R2 Maintenance Plans for SharePoint 2010
Backing up your Content Databases is one of the most critical activities that can be undertaken in order to preserve your data/have a strong Disaster Recovery Plan for SharePoint 2010. In this post we will focus on a simple Maintenance Plan with one “gotcha” I came across.
SharePoint 2010 and SQL 2008 R2 offer a host of backup/restore options and often include a Farm backup (can be automated via PowerShell) to capture the configuration data, and SQL Content Database backups (automated) for the content.
Here is the TechNet link which lays out SharePoint 2010 backup options:
http://technet.microsoft.com/en-us/library/ee428316.aspx
SQL does not back up your configuration data, but by using a Maintenance Plan you can and should automate the backup of your Content Databases… and even better… delete old data (auto-cleanup activities controlled by the Maintenance Cleanup Task).
Here is a great post that I often reference to get a good baseline for backing up and cleaning up my Content Databases. Bear in mind that your requirements may vary, but the core process is very straightforward:
And now for the “gotcha”… Once I had my Maintenance Plan in place, I executed it only to receive the following:
Error Number -1073548784 “Invalid device name. The length of the device name provided exceeds supported limit (maximum length is:259).”
I immediately thought that my system Content Databases had too long of a name with their GUIDs (ex. the Managed Metadata Service_SuperlongGUIDHere), but instead it was the entire path including the backup folder that caused the issue (D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup).
To remedy this, I simply made a folder in the root of my backup drive named “Database_Backups” (D:\Database_Backups) and edited my Maintenance Plan to include this shortened path.
Remember, a strong Disaster Recovery Plan (DRP) takes a lot of careful work and by no means is a SQL Maintenance Plan the complete answer, but it is a core component and will help you rest easier at night (while SQL hums away backing up and removing old backup data sets… if you added a Maintenance Cleanup Task).