Although SharePoint 2010 is a fantastic tool, still does not bring an automatic backup system. And is very important to make regular backups of SharePoint. Many companies don’t make regular SharePoint backups, they only start doing it when they have the system “crashed”.

But to ease the backups you can create a script and add it to the Windows Task Scheduler. Normally, when is the first time, I start by doing a full SQL Server backup and then a full SharePoint backup. After this i run a differential backup on daily basis.

Here are the steps:

NOTE: Replace all local drive reference with the appropriate UNC path. And you also may have to change the SQL Server instance name

Full SQL Server Backup

1. Save the code below to a .bat file (i.e. copy and paste into notepad and save it as SqlServerBackupScript.sql).

   DECLARE @name VARCHAR(150) -- database name
   DECLARE @path VARCHAR(356) -- path for backup files
   DECLARE @fileName VARCHAR(356) -- filename for backup
   DECLARE @fileDate VARCHAR(20) -- used for file name
   DECLARE @DirTree TABLE (subdirectory nvarchar(355), depth INT) SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
   SET @path = '\\192.168.100.241\Public\SQLServerBackup\' + @fileDate + '\'
   
   -- 3 - @DataPath values
   INSERT INTO @DirTree(subdirectory, depth)
   EXEC master.sys.xp_dirtree @path
   
   -- 4 - Create the @DataPath directory
   EXEC master.dbo.xp_create_subdir @path
   
   DECLARE db_cursor CURSOR FOR
   SELECT name
   FROM master.dbo.sysdatabases
   WHERE name NOT IN ('master','model','msdb','tempdb')
   
   OPEN db_cursor
   FETCH NEXT FROM db_cursor INTO @name
   
   WHILE @@FETCH_STATUS = 0
   BEGIN
   SET @fileName = @path + @name + '.BAK'
   BACKUP DATABASE @name TO DISK = @fileName
   
   FETCH NEXT FROM db_cursor INTO @name
   END
   
   CLOSE db_cursor
   DEALLOCATE db_cursor
   

2. Save the code below to a .bat file (i.e. copy and paste into notepad and save it as SqlBackup.bat).

   "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\SQLCMD.EXE" -S (local)\SQLExpress -i "C:\backup scritps\SqlServerBackupScript.sql"
   

   Now you can perform a FULL Sql Backup. Don’t forget to Elevate privileges.

Full SharePoint Backup

1. Save the code below to a .ps1 file (i.e. copy and paste into notepad and save it as FullSharePointBackup.ps1).

   $ver = $host | select version
   if ($ver.Version.Major -gt 1)  {$Host.Runspace.ThreadOptions = "ReuseThread"}
   Add-PsSnapin Microsoft.SharePoint.PowerShell
   Set-location $home
   Backup-SPFarm -Directory "\\192.168.100.241\Public\SharePointBackupData" -BackupMethod Full -Verbose
   

2. Save the code below to a .bat file (i.e copy and paste into notepad and save it as FullSharePointBackup.bat).

   C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe  -NoExit  " & ' C:\backup scritps\FullSharePointBackup.ps1 ' "
   

Diferencial SharePoint Backup

1. Save the code below to a .ps1 file (i.e. copy and paste into notepad and save it as DifferentialSharePointBackup.ps1).

   $ver = $host | select version
   if ($ver.Version.Major -gt 1)  {$Host.Runspace.ThreadOptions = "ReuseThread"}
   Add-PsSnapin Microsoft.SharePoint.PowerShell
   Set-location $home
   Backup-SPFarm -Directory "\\192.168.100.241\Public\SharePointBackupData" -BackupMethod Differential -Verbose
   

2. Save the code below to a .bat file (i.e copy and paste into notepad and save it as FullSharePointBackup.bat).

   C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe  -NoExit  " & ' C:\backup scritps\DifferentialSharePointBackup.ps1 ' "
   

3. Add this script to Task Scheduler

A couple of quick notes:

• Make sure you create the backup directory before running this job
• Look for failures in the Event Viewer