Skip to content

Script to automatically backup sharepoint

May 9, 2011

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
Advertisements

From → SharePoint 2010

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: