So it’s easy enough to get Share Point Central Administration to backup your farm, but it involves logging in every day to click the backup button, which can get tedious. I wanted a way to automate it but of course nothing was easy, after about two weeks to trying this that and the other thing, I came up with a little cheat sheet. It should be noted that I’m not a Share Point or SQL admin, and in fact I’m pretty new to working with either of them, so if you see something that could be improved on, please let me know.

The first time around in our test environment we didn’t install Share Point properly, and we tried to change the service accounts running Share Point through the Central Administration site. I’m not sure if it was bad luck, or something else, but the whole damn thing came crashing down, so please RTFM when installing new-to-you software. We  ended up going back, doing a little more planning, and performed a fresh install. I’ve decided to include some of those planning notes as pre-reqs before we go into getting the backup to work, as you’ll run into permissions problems if they’re not taken care of.

Pre Requisites Notes

Service Accounts:

svcSQL – This account is used to run all SQL related services, regardless of server.

svcSharePoint – This account is the account specified during SharePoint installation, also known as the “farm account” or “timer account” (don’t ask, I don’t understand either)

svcScheduledTask – This is the account under which the schedule task runs, when configuring the task make sure to use this account.

Installation of Applications

Okay, so when you install SQL make sure to use the svcSQL account to run all of the services.

When you install SharePoint, make sure to use the svcSharePoint account as the Farm account when installing. Also make sure to point all of your SQL needs to the SQL server and not a local database.

Assign the following permissions to the SQL databases for each service account:

User: svcSharepoint
Server Instance: SQL_SERVER\SQL_INSTANCE
Server Instance-Wide Role(s):
--dbcreator
--public
--securityadmin

Database Specific Role(s):
-SharePoint_AdminContent_{GUID}
--db_owner
--Public
--SharePoint_Shell_Access

-WSS_Content_Application_Pools
--db_owner
--Public
--SharePoint_Shell_Access

-SharePoint_Config
--db_owner
--Public
--SharePoint_Shell_Access

-WSS_Content_Application_Pools
--db_owner
--Public
--SharePoint_Shell_Access

-WSS_Content_{GUID}
--db_owner
--public

-WSS_Search_SQL_SERVER
--db_owner
--public

User: svcScheduledTask
Server Instance: SQL_SERVER\SQL_INSTANCE
Server Instance-Wide Role(s):
--Public


Database Specific Role(s):
-ALL Databases
--db_backupoperator
--public
--SharePoint_Shell_Access

Actual Backup Procedure

Now that the software is installed correctly we can take backups, if the software is not installed correctly forget about backups you’ll be lucky that central administration doesn’t implode. This is probably one of the most convoluted backup procedures you’ll ever see. Because of the combination of Batch scripts, and trying to call 1 Power Shell script from another, in combination with the fact that the Task Scheduler has a hell of a time with quotes, you end up with this, which is a scheduled task that runs 3 batch files, two of which contain Power Shell commands.

First, Create a new shared folder on the backup server, in this example we called it “data” give Full Control permissions to each service account (all three)

Then, Create a batch script called “cleanup” and put the following in it (adjust for LOCAL path)

rmdir C:\Data\SharepointBackups /s /q
mkdir c:\data\sharepointbackups


Next, Create a new Power shell script called “FarmBackup” (notepad file saved as .ps1) with the following lines (adjusted for UNC path):

Add-PSSnapin Microsoft.SharePoint.PowerShell
Backup-SPFarm -Directory \\BACKUP_SERVER\Data\sharepointbackups -BackupMethod Full

Next, Create a new batch script called “LaunchPowerShell” and put the following in it: (adjust for LOCAL path)

%SystemRoot%\system32\WindowsPowerShell\v1.0\powershell.exe -command "& 'C:\Data\FarmBackup.ps1' "


Next, Create a new scheduled task that runs with the following settings on each tab:
General Tab:
-Run Whether use is logged in or not
-Run with highest privileges
Triggers Tab:
-Weekly – setup with proper days and times
Actions Tab:
-Create a new action, Run a Program, and run the Cleanup.bat file
-Create a 2nd action, Run a program, and enter the following command (adjusted for LOCAL path) in the Program/Script field:
D:\data\launchpowershell.bat

Run each of the following first to verify that they work before running a test job:

Cleanup.bat
Launchpowershell.bat
FarmBackup.ps1

Failures on either mean that the job won’t run.

Finally don’t forget to backup the C:\Data\SharepointBackups folder with some backup software.