SSIS SharePoint Backup Package

My last post outlined how to automate backing up SharePoint using visual basic scripting, but being a DBA and developer it was only a matter time before I spun this into a SQL related exercise. I would again love to take credit for the aforementioned posted vbs code, but my only efforts were to make MINIMAL changes to the script outlined on Technet:

http://technet.microsoft.com/en-us/library/cc967308.aspx

So again let me lay out my specific business needs:

1. Due to limited storage only 2 full SharePoint farm backups can be maintained on our storage device
2. A full SharePoint farm backup for the most recent two days must be maintained
3. To assure ease and reduce confusion the spbrtoct.xml file must be kept up to date recording ONLY those backups in SharePoint that are available

The above link provides an EXCELLENT source for using vbs to accomplish this task, but aside from my admitted personal bias and preference, there are several benefits that are immediately realized in replacing this script with managed code.  Some of these benefits include:

1. Structured error handling

Please do not interpret this statement as to say that there is no structured error handling in vbs, but the catching and handling or errors in .NET is easier and more encompassing. 

  2. Logging

SSIS packages can utilize log providers that provide a much greater depth of information into what occurs during package execution than what is immediately available in scheduling vbs from scheduled tasks

http://msdn.microsoft.com/en-us/library/ms140246.aspx

3. Task History

After deploying and scheduling an SSIS package using SQL server agent the history can be viewed and maintained along with the recorded outcome and error(s).

Rather than ramble on let's jump into the process of creating an SSIS package to maintain SharePoint backups.  The first step is to create a SQL Server integration services project using BIDS, in this post I am using SQL/BIDS 2008, which supports script tasks for both C# and VB.NET.  The posted VB code will works just as well in SQL 2005, but the C# code is obviously not an option as only VB is supported in SSIS script tasks. 

Once the project is created drag and drop a Script Task onto the control flow pane.  The task will need to Import System.IO and Import System.XML, for C# you must reference using  System.IO and using System.XML.
The VB code is listed here:

      Public Sub Main()

        'An XmlDocument is used to hold the spbrtoct.xml

              Dim doc As New XmlDocument
              Dim logcount As Integer = 0

' path(s) variables contain the path for the log and spbrtoct 
'In order to assure consistency and reduce the chance of improper

        Dim log As String = "\\SERVER\MOSS Backup\BackupCleanUp.log"

        Dim spbr As String = "\\SERVER\MOSS Backup\spbrtoct.xml"

        ' Begin by adding a line to the log file that will delineate the date
        ' that the process began

File.AppendAllText(log, "----------------------------" & vbTab & "Backup/Cleanup Process Begun: " & vbTab & ().ToString & vbTab & "----------------------------" & _ vbCrLf)

        Try

            'Structured error handling is used in case an error occurs during the

            'loading of the spbrtoct.xml

 doc.Load(spbr)

        Catch ex As Exception

            ' If an error occurs during the loading of the spbrtoct.xml then an

            ' entry is made in the log along with the ex.Message and since

            ' nothing more can be done the sub is Exited

            File.AppendAllText(log, Now().ToString & vbTab & "Error: Could not load the SharePoint Backup / Restore History." & vbCrLf & _

Now() & vbTab & "Reason: " & ex.Message.ToString & vbCrLf)

            logcount += 1

            Exit Sub

        End Try

Dim bupHistory As XmlNode

        Try

            'Loop through each elements child node

            For Each bupHistory In doc.DocumentElement.ChildNodes

'If the returns true and the is more than two days old

If bupHistory.ChildNodes(6).InnerText = True And & _

CDate(bupHistory.ChildNodes(5).InnerText.ToString) & _
< Now.AddDays(-2) Then

   'The node is deleted and the deletion is recorded in the log

                   doc.DocumentElement.RemoveChild(bupHistory)

File.AppendAllText(log, Now().ToString & vbTab & "Removed node: "& _ bupHistory.ChildNodes(7).InnerText.Substring(0, Len(bupHistory.ChildNodes(7).InnerText) - 1) & "." & vbCrLf)

                    logcount += 1

'The directory specified in the <SPBackupDirectory> is deleted and the
'deletion is recorded in the log

If Directory.Exists(bupHistory.ChildNodes(7).InnerText.Substring(0,
Len(bupHistory.ChildNodes(7).InnerText) - 1)) = True Then

 Directory.Delete(bupHistory.ChildNodes(7).InnerText.Substring(0,
Len(bupHistory.ChildNodes(7).InnerText) - 1))

File.AppendAllText(log, Now().ToString & vbTab & "Deleted: " &
bupHistory.ChildNodes(7).InnerText.Substring(0,
Len(bupHistory.ChildNodes(7).InnerText) - 1) & "." & vbCrLf)

                        logcount += 1

                    Else

File.AppendAllText(log, Now().ToString & vbTab & "Directory does not exist: " & bupHistory.ChildNodes(7).InnerText.Substring(0,
Len(bupHistory.ChildNodes(7).InnerText) - 1) & "." & vbCrLf)

                    End If

                End If

            Next

        Catch ex As Exception

'If an error occurs during the deletion of the node or directory an entry is made 'in the log

File.AppendAllText(log, Now().ToString & vbTab & "Error: Could not delete the directory or remove the historic backup node." & vbCrLf & _

Now() & vbTab & "Reason: " & ex.Message.ToString & vbCrLf)

            logcount += 1

        End Try

        Try

'The changes made to the spbrtoct.xml are saved back to the directory and if an 'error occurs then an entry is made in the log

            doc.Save(spbr)

        Catch ex As Exception

File.AppendAllText(log, Now().ToString & vbTab & "Error: Could not save changes made to spbrtoct.xml." & vbCrLf & Now() & vbTab & "Reason:" & ex.Message.ToString & vbCrLf)

            logcount += 1

        End Try

        If logcount = 0 Then

            File.AppendAllText(log, "############################    Nothing Logged Completed At     " & Now().ToString & "  ###########################" & vbCrLf)

        End If

Dts.TaskResult = ScriptResults.Success

      End Sub

End Class

and  the C# code:

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Runtime;

using System.Windows.Forms;

using System.IO;

using System.Xml;

namespace ST_1ca426443e474993a2ce11e5f0224a09.csproj

{

    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]

    public partial class ScriptMain :
Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    {

        #region VSTA generated code

        enum ScriptResults

        {

            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,

            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

        };

        #endregion

        public void Main()

        {

{

//In order to assure consistency and reduce the chance of improper path(s) the paths

    //for both the spbrtoct and log paths are stored as variables

    string log = "\\\\SERVER\\MOSS Backup\\BackupCleanUp.log";

    string spbr = "\\\\SERVER\\MOSS Backup\\spbrtoct.xml";

//Begin by adding a line to the log file that will delineate the date that the process began
    File.AppendAllText(log, @"----------------------------" + "\t Backup/Cleanup Process Begun:\t " + DateTime.Now + "\t----------------------------
\r\n");

    //An XmlDocument is used to hold the spbrtoct.xml   
XmlDocument doc = new XmlDocument();

    int logcount = 0;

    try {

        //Structured error handling is used in case an error occurs during the loading of the spbrtoct.xml

doc.Load(spbr);

    }

    catch (Exception ex) {

        //If an error occurs during the loading of the spbrtoct.xml then an entry is made in the log

        //along with the ex.Message and since nothing more can be done the sub is Exited

        string loaderror = DateTime.Now + @"\t Error: Could not load the SharePoint Backup / Restore History. \t" + DateTime.Now + "Reason: " + ex.Message + "\r\n";

        File.AppendAllText(log, loaderror);

        logcount += 1;

        return;

    }

try {

        //Loop through each elements child node

        foreach (XmlNode bup in doc.DocumentElement.ChildNodes)

   {

//If the returns true and the is more than two days old

            if (bup.ChildNodes[6].InnerText == "True" & DateTime.Parse(bup.ChildNodes[5].InnerText) < DateTime.Now.AddDays(-2))

   {

//The node is deleted and the deletion is recorded in the log

             doc.DocumentElement.RemoveChild(bup);

                File.AppendAllText(log, DateTime.Now + "\t Removed node: " +
bup.ChildNodes[7].InnerText.Substring(0, (bup.ChildNodes[7].InnerText).Length -
1) + ". \r\n");

                logcount += 1;

//The directory specified in the <SPBackupDirectory> is deleted and the
//deletion is recorded in the log

                if (Directory.Exists(bup.ChildNodes[7].InnerText.Substring(0,
(bup.ChildNodes[7].InnerText).Length - 1)) == true)

                {

                    Directory.Delete(bup.ChildNodes[7].InnerText.Substring(0,
(bup.ChildNodes[7].InnerText).Length - 1));

File.AppendAllText(log, DateTime.Now + "\t Deleted: " + bup.ChildNodes[7].InnerText.Substring(0, (bup.ChildNodes[7].InnerText).Length - 1) + ". \r\n");    

                }

                else
{

File.AppendAllText(log, DateTime.Now + "\t Deleted: " + bup.ChildNodes[7].InnerText.Substring(0, (bup.ChildNodes[7].InnerText).Length - 1) + ". \r\n");

                    }

                    logcount += 1;

                }

            }

        }

    catch (Exception ex) {

        //If an error occurs during the deletion of the node or directory an entry is made in the log

File.AppendAllText(log, DateTime.Now + @"\t Error: Could not delete the directory or remove the historic backup node. \t" + DateTime.Now + " \tReason: " + ex.Message + "\r\n");

        logcount += 1;

    }

    try {

//The changes made to the spbrtoct.xml are saved back to the directory and if an error occurs then an entry is made in the log

        doc.Save(spbr);

    }

    catch (Exception ex) {

        File.AppendAllText(log, DateTime.Now + @"\t Error: Could not save changes made to spbrtoct.xml. \t" + DateTime.Now + "\t Reason: " + ex.Message + "\r\n");

        logcount += 1;

    }

    if
(logcount == 0) {

File.AppendAllText("C:\\BackupCleanUp.log", "############################ Nothing Logged Completed At " + DateTime.Now + " ########################### \r\n");

    }

        }

        }

    }

            Dts.TaskResult = (int)ScriptResults.Success;

        }

}

Once the script task is completed then drop an Execute Process Task onto the control flow pane and connect the script tasks success precedent constraint to the Execute Process Task.

Configure the Execute Process Task to be point at the STSADM.exe, the default path is :
C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\Bin\STSADM.exe
the Arguments should be:
-o backup -directory "\\SERVER\MOSS Backup" -backupmethod full
and the WorkingDirectory should be configured as:
 C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\Bin\

***Keep in mind my requirements are for a farm backup so the below screen shot of the command line arguments are for a full farm back up.

After I completing this package I began to obsess about all of the possible enhancements I could include, bells, whistles, features, etc, but was soon called back to reality with the oh so familiar, “C’mon man!! We got other projects to complete!!”  The one enhancement I do plan on implementing is to create an SSIS variable that will be assigned the script error and package messages, using the OnError event handler, and then emailed using a Send Mail Task.

I have done my best to clean up the script task code to make it easy to cut and paste, but I finally gave up so please contact me if there are any question.

Backing Up/Cleaning Up SharePoint Backups

    Microsoft provides an easy to use graphical user interface for most every server application, but such amenities are not immediately available in SharePoint 2007. The necessity to restore and recover a SharePoint farm in case of a disaster is a requirement of most, if not all, organizations. I was surprised to find that the ability to create and schedule such a task from a user interface was not included in SharePoint. After a week or so of manually backing up our SharePoint implementation and cleaning up the outdated backups I decided that it was time find a way to automate this task. The documentation for performing such a task is well outlined on-line using the stsadm executable.

     I was able to quickly and easily find documentation that met my needs of creating a scheduled backup of our SharePoint farm, but as the back-ups grew and space became an issue, I realized that I needed a more encompassing solution. I again found AWESOME documentation on how to maintain the backup files to only 2 days, but realized that this did not clear the history that SharePoint maintained and I was forced to deal with the spbrtoc.xml file, this is the xml file used to record the backup history of SharePoint. Some may find this a rather minor hurdle, but remember I deal mainly with SQL Server, relational, and working with xml, hieratical, can prove a bit frustrating.

     I would love nothing more than to provide my solution as I implemented it as MINE, but I must admit that a GREAT majority of the work was already done for me. I was able to find the online documentation “Clean up backup files (Windows SharePoint Services 3.0) “on TechNet which almost solved my entire automation conundrum:
http://technet.microsoft.com/en-us/library/cc967308.aspx
    
This vb script covered all of my requirements except:
1. Required user input
2. Did not include a backup command for SharePoint

     As I stated previously the above referenced TechNet link should be more than enough for most that run upon this post so I would highly recommend studying this.

     The below vb script will remove the folder(s) containing all backups that are more than two days old and uses the entries in the spbrtroc.xml to retrieve these directories. After the directories are removed the associated node(s) in the spbrtoc.xml file is removed thereby assuring that the entry will not be shown as an option to restore from the SharePoint central administration site. The final step is to call a command object that will run the stsadm executable to do a full farm backup:

Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder("\\server\Moss Backup")
OlderThanDate = DateAdd("d", -2, Date)
Set sf = f.SubFolders

Dim dtDeleteDate
dtDeleteDate = DateAdd("d",-2,Now)

Set objXML = CreateObject("Microsoft.XMLDOM")
Set objFS  = CreateObject("Scripting.FileSystemObject")
Set objLog = objFS.OpenTextFile("BackupCleanUp.log",8,True)
    
' Load SharePoint Backup and Restore TOC File
objXML.Async = False

objXML.Load("\\server\Moss Backup\spbrtoc.xml")

If objXML.ParseError.ErrorCode <> 0 Then
    objLog.WriteLine(Now() &amp;vbTab&amp; "Error: Could not load the SharePoint Backup / Restore History." &amp; vbCrLf&amp;_
                     Now() &amp;vbTab&amp; "Reason: " &amp; objXML.ParseError.Reason&amp; ".")
    WScript.Quit
End If

' Delete Backup Nodes Older Than Deletion Date
For Each objNode In objXML.DocumentElement.ChildNodes
    If CDate(objNode.SelectSingleNode("SPFinishTime").Text) < dtDeleteDate Then
        If objNode.SelectSingleNode("SPIsBackup").Text = "True" Then
   Dim f
   f = Left(objNode.SelectSingleNode("SPBackupDirectory").Text, Len(objNode.SelectSingleNode("SPBackupDirectory").Text), -1)
   objFS.DeleteFolder(f)
        objLog.WriteLine(Now() &amp;vbTab&amp; "Deleted: " &amp; objNode.SelectSingleNode("SPBackupDirectory").Text &amp; ".")
        objXML.DocumentElement.RemoveChild(objNode)
           End If    
    End If
Next

' Save XML File With Old Nodes Removed
objXML.Save("\\server\Moss Backup\spbrtoc.xml")
objLog.WriteLine(Now() &amp;vbTab&amp; "Finish: Completed backup clean up.")
Set fso = CreateObject("Scripting.FileSystemObject")
Set f = fso.GetFolder("\\server\MOSS Backup")
OlderThanDate = DateAdd("d", -2, Date)

Dim sf
Set sf = f.SubFolders
For Each f In sf
     If InStr(f, "spbr") Then
           If f.DateCreated < OlderThanDate Then
                f.Delete
           End If    
     End If
Next

Set objShell = CreateObject("WScript.Shell")
objShell.Run "STSADM.exe -o backup -directory ""\\server\Moss Backup"" -backupmethod full"

     After completing the script and testing I placed the .vbs file in the SharePoint bin folder, where the stsadm is located, and scheduled it using Scheduled tasks.  Further documentation on backing up SharePoint can be found here:

http://msdn.microsoft.com/en-us/library/cc264321(office.14).aspx

     I am sure that this will come to no surprise to many, but my follow up post will be doing the same actions using an SSIS package, and YES I will include C# code.