Chapter 17. Backing Up and Restoring a SharePoint Environment

IN THIS CHAPTER

A streamlined and efficient SharePoint environment is worthless if a hardware failure wipes it out. Consequently, the ability to back up and restore a SharePoint environment is a critical skill for a SharePoint administrator. Fortunately, SharePoint provides for multiple backup and restore techniques, which can be used either exclusively or together as part of an integrated backup strategy. Because there are so many ways of backing up SharePoint, it is important to review each of the backup and restore methods, their pros and cons, and how they can be leveraged to keep a SharePoint environment running smoothly.

This chapter focuses on the backup and restore mechanisms for SharePoint 2007. Step-by-step information on backing up using SharePoint Designer 2007, STSADM, SQL Tools, and SharePoint Central Admin Backup and Restore are outlined, and the pros and cons of each method are described.

Backing Up and Recovering SharePoint Components

Microsoft Office SharePoint Server 2007 and Windows SharePoint Services are critical components in a network infrastructure. Great care should be taken to back up their components and content. It would be a tremendous task to re-create customized SharePoint sites, not to mention the document data associated with each one. A good deal of attention should be paid to the backup and restore procedures for a SharePoint farm.

There are several different approaches to backing up data in SharePoint. Because there are so many options, SharePoint administrators are often confused over which option is the best for their organization. The following backup/restore solutions and their appropriate use are listed as follows:

  • Recycle Bin—While not a traditional backup tool, the Recycle Bin included within SharePoint 2007 is the first line of defense in the event that a restore is needed, as all deleted items in a site collection are placed in a dual-stage Recycle Bin, where they can be restored by the individual user after deletion, or by a site collection administrator. Although the Recycle Bin only handles deletions and not such things as item corruption, using the Recycle Bin in a Sharepoint environment is key to avoiding using more intrusive tools to restore.
  • Backup and restore options in SharePoint Central Administration 3.0—Within the SharePoint Central Admin Tool, the option to back up all farm components or individual web applications exists. This type of backup process, although thorough, is not particularly robust and cannot be easily scheduled. It is typically manually invoked before an administrator makes a major change, such as patching the server or updating the service pack levels.
  • STSADM command-line utility backup—The command line—driven STSADM utility exports entire site collections to flat file formats, allowing for full fidelity backup snapshots of sites at a particular point in time. This type of backup process can be kicked off by a batch file process scheduled with the integrated Windows Scheduled Tasks application or the command-line ‘AT’ command, or scripted to run on a regular basis using the custom script written by the authors and provided in this chapter. STSADM backups also allow individual site collections to be restored to other servers or to different locations on the same server, giving it great flexibility. The only downside to STSADM is that it does not scale well to very large site collections, so it is primarily used by small to medium-sized SharePoint environments.
  • SharePoint Designer 2007 backup—The replacement product for FrontPage 2003 is known as SharePoint Designer 2007. This tool allows individual sites or site elements to be backed up to a .cwp flat file format. This type of backup is typically performed ad hoc, when in the process of designing a SharePoint site or when moving pieces of a site from one location to another. It cannot be easily scheduled.
  • IIS backup script—A built-in script in Windows 2003 allows the settings of the IIS Virtual Servers on the system to be backed up to an xml-format file, which means that the administrators can keep a copy of the IIS configuration in the event that the SharePoint Server needs to be rebuilt. This type of backup is typically used as part of a scheduled batch file process that is run on a regular basis.
  • SQL backup tools—Within SQL Server 2000/2005, built-in tools exist that backup SQL databases, including SharePoint databases, and they can be easily set up and scheduled to run full backups of all SharePoint content. This type of backup process is convenient for organizations that already have a SQL database backup plan in place or that want to supplement another backup option, such as STSADM, with a full database-level backup. SQL restore procedures can only restore entire databases, however, and individual site elements cannot be restored using this technique.
  • Third-party backup tools—Several third-party companies, including DocAve and Veritas, are currently working on backup tools that will work within SharePoint 2007, allowing for item-level restore of documents and other SharePoint settings. Checking on the features of the preferred backup vendor is recommended for those environments needing a truly automated and enterprise-level backup environment for SharePoint.

A combination of backup approaches is often ideal for providing the proper blend of backup and restore flexibility required by an organization. This chapter discusses each of the options described previously in more detail.

Using and Administering the Recycle Bin

One of the main complaints about the 2003 version of SharePoint Products and Technologies was its lack of a built-in undelete option. After a document was deleted, site administrators needed to restore entire site collections or even entire portals to recover a single item.

SharePoint 2007 introduces Recycle Bin functionality to site content, allowing end users and administrators to recover deleted items easily and effectively, reducing the need for performing restore operations on a server. Indeed, this improvement is often cited as one of the major selling points of the 2007 version over the 2003 version, as it opens up SharePoint for more effective document management capabilities.

Examining the Two Stages of the Recycle Bin

The Recycle Bin functionality in SharePoint 2007 is two-stage. This means that deleted items actually go into two Recycle Bins before they are permanently deleted from the database. The first Recycle Bin, an example of which is shown in Figure 17.1, is site-specific, and can be managed by the individual end users. Users can easily enter the Recycle Bin from a link on the navigation bar, check the boxes next to the documents that they want to recover, and then click the Restore Selection link to recover the file or files to their original locations.

Figure 17.1. Recovering items from the end user Recycle Bin.

image

If the retention period has passed, or if a user chooses to delete the items manually from the Recycle Bin, they pass to the second phase, the Site Collection Recycle Bin. Site administrators can then go into the Site Collection Recycle Bin, shown in Figure 17.2, and recover items for the users.

Figure 17.2. Recovering items from the Site Collection Recycle Bin.

image

Enabling Recycle Bin Functionality in SharePoint

Recycle Bin functionality is enabled by default in SharePoint 2007. Administrators can toggle this setting on or off, or can change the thresholds for how long data is kept in the Recycle Bin before it is emptied. These settings can all be changed from within the SharePoint Central Admin Tool. Recycle Bin settings are configured for the entire web application. Any changes made affect all data within the entire web application, so it is important to understand in advance which options will be required for all data within the organization.

To access the Recycle Bin settings for a particular web application, perform the following steps:

  1. Open the SharePoint Central Admin Tool from a SharePoint Server (Start, All Programs, Microsoft Office Server, SharePoint 3.0 Central Administration).
  2. Select the Application Management tab.
  3. Under SharePoint Web Application Management, click the link for Web Application General Settings.
  4. From the drop-down box under Web Application, select the specific web application to be modified.
  5. Scroll down to the Recycle Bin options at the bottom of the page, make any necessary changes, and click OK to save those changes.

The settings listed, shown in Figure 17.3, allow for the following options:

Figure 17.3. Changing Recycle Bin Settings.

image

  • Recycle Bin Status—This setting allows the entire Web Application Recycle Bin, including both stages, to be toggled on or off.
  • Delete Items in the Recycle Bin—This sets the number of days before items are removed from the end user Recycle Bin. The default value is 30 days. The value can be changed to a different number of days, or it can be set to never remove items from the Recycle Bin.
  • Second Stage Recycle Bin—The Site Collection Recycle Bin, also known as the Second Stage Recycle Bin, can be either turned off or configured to be emptied after it reaches a specific percentage of the web application’s quota. For example, if the web application has a quota of 250MB, a setting of 50% allows up to 125MB of data to be stored in the Second Stage Recycle Bin, increasing the effective quota of the web application to 375MB. The numbers can be changed to any percentage up to 100%.

Caution

It is important to find a good balance between overly aggressive and overly lax deletion schedules for the Recycle Bin. Too aggressive, and users won’t have time to recover items they need. Too lax, and it becomes difficult to keep a lid on database sizes.

Using the Recycle Bin included within SharePoint 2007 is an ideal way to limit the amount of restore operations that need to be performed in a SharePoint environment. Subsequently, it is highly recommended to train users in the use of the SharePoint Recycle Bin, and to choose appropriate retention settings for the organization.

Using the SharePoint Central Admin Tool Backup and Restore Options

The most straightforward manual option for backing up an entire SharePoint farm is by using the integrated SharePoint Backup and Restore option in the SharePoint Central Administration tool. The SharePoint Central Admin Tool backup option has the capability to back up individual pieces of a farm or all farm components at the same time. In addition, it has the capability to restore backups of farm content to the original location or to an alternate location, allowing for restored content to coexist with live data.

The biggest disadvantage to the SharePoint Central Admin Tool backup options is the lack of native capability to automate nightly or weekly backups. Backups must subsequently be manually invoked, and there is no command-line option for kicking off backups using this interface.

Performing a Backup Using the SharePoint Central Admin Tool

To back up farm components using the SharePoint Central Admin Tool, perform the following steps:

1. From the SharePoint Server, open the Central Administration tool (Start, All Programs, Microsoft Office Server, SharePoint 3.0 Central Administration).

2. Select the Operations tab.

3. In the Backup and Restore section, click on Perform a Backup.

4. From the Select Component to Backup page, check the Farm box, as shown in Figure 17.4. If only specific components need to be backed up, narrow the selection by unchecking boxes.

Figure 17.4. Backing up the SharePoint farm.

image

5. After making the selections needed for backup, click the Continue to Backup Options link.

6. In the Select Backup Options page, shown in Figure 17.5, select a full backup (differential backups are provided for delta backups of changes made after the last full backup). Enter a backup location and click OK.

Figure 17.5. Selecting backup options.

image

7. After starting the backup, SharePoint displays the Backup and Restore Status screen. It might take several minutes for the backup process to appear on the page. The progress can be monitored. Wait for the Progress field to show Complete for all items. Backup history can be viewed by clicking on the View History link.

Backup files can be viewed in the folder location selected and appear as an XML manifest file and a folder full of BAK files. Do not delete the XML manifest file in the root as it is needed for restore operations.

Restoring SharePoint Using the Central Admin Tool

After backing up a farm or individual farm components, the XML manifest contains the full list of backups, and administrators can choose to restore those backups onto a running server or onto a separate server. Restoring to another server gives SharePoint 2007 the capability to quickly and easily create a test environment which can be refreshed, often based off of production backups. In addition, in the event of a hardware failure on the SharePoint Server, the SharePoint backups can be used to restore the farm after Microsoft Office SharePoint Server (MOSS) 2007 has been installed on the newly rebuilt server.

To invoke the Central Admin tools and begin a restore operation in SharePoint, perform the following steps:

1. From the SharePoint Server, open the Central Administration tool (Start, All Programs, Microsoft Office Server, SharePoint 3.0 Central Administration).

2. Select the Operations tab.

3. In the Backup and Restore section, click on Restore from Backup.

4. On the Backup Location page, enter the folder location where the backup manifest file is located. Click OK.

5. Select the specific backup that will be restored from the list, as shown in Figure 17.6, and click Continue Restore Process.

Figure 17.6. Restoring using the Central Admin Tool.

image

6. Select the particular components that will be restored, keeping in mind that the Configuration database and Central Admin Content database cannot be restored with this utility. When finished selecting restore options, click on Continue Restore Process.

7. In Step 4 of the restore process, shown in Figure 17.7, select to either restore the content onto the same configuration (overwrite the existing data) or restore onto a new configuration (write to a new web application, allowing both the restored data and the current data to coexist simultaneously). Click OK to start the restore process.

Figure 17.7. Restoring a content database.

image

Caution

If you choose the Same Configuration option, you are prompted to see if this is what you really want to do, as choosing that option will permanently delete the existing content and replace it with the content in the backup file. Be sure you really want to do this before continuing!

8. Follow the progress of the Restore from the Backup and Restore Status page. Just as with the backup, you might need to wait several minutes before the status appears. The restore is complete after the progress indicator shows Complete.

The flexibility of the SharePoint Central Admin Tool for manual backups and restores gives administrators a powerful tool to manage and safeguard the data within the environment. Because it can’t be automated, however, many organizations choose to use the tool for specific point-in-time backups, and use some of the other backup options outlined in this chapter for regular daily and weekly backups.

Backing Up and Restoring with SharePoint Designer 2007

One simple solution to backing up a SharePoint site is to use the SharePoint Designer 2007 client product, which is the replacement to the FrontPage suite of products. Because SharePoint is essentially a compilation of web pages and other web-specific information, it can be easily backed up from this type of utility.

Note

Older versions of the software, such as FrontPage 2003, do not support backing up a SharePoint 2007 site. However, SharePoint Designer 2007 does support backing up legacy SharePoint 2003 sites.

Backing Up SharePoint Sites Using SharePoint Designer 2007

To perform a backup of a SharePoint site using SP Designer, perform the following steps:

1. Open SharePoint Designer 2007.

2. Choose File, Open Site.

3. Under Site Name, enter the URL of the SharePoint site collection (for example, http://server4/sites/sales) and click Open.

4. If prompted for a username and password, enter the appropriate credentials.

5. After the site opens, choose Site, Administration, Backup Web Site.

6. From the dialog box shown in Figure 17.8, select whether to include subsites in the backup. Clicking the Advanced button allows an administrator to choose a temporary location for process files to be placed while the backup is taking place. Click OK to continue.

Figure 17.8. Backing up the SharePoint site with SharePoint Designer 2007.

image

7. Select a location to place the .cwp file that will be generated as part of the backup process. Click Save.

8. Click OK when complete.

This technique is useful as it can be run from an administrator’s desktop in the middle of the day, or after making substantial changes to a site.

Caution

Although the SP Designer approach restores most site content, it often does not restore security settings in the same way. You might have to restore some of the security on Document Libraries and Lists after you run the restore. If you desire to restore all aspects of a site, you need to use either the built-in backup tool in the SharePoint Central Admin utility or a utility such as STSADM.EXE, which is described in more detail later in this chapter.

In addition to providing the capability to backup entire sites, SP Designer also gives administrators the ability to back up individual web content, such as document libraries or lists, so they can be used in other site collections. This functionality can be accessed by File, Export, Personal Web Package.

Deleting and Re-creating Sites in Anticipation of a Full Restore

If you need to restore the site you backed up, you first need to ensure that the old site does not exist. In some cases, such as when rebuilding a server, it will not exist, but in other cases, such as the site becoming corrupt or losing data, you will need to delete the site first.

Caution

Do not perform a deletion of your site unless you are sure you have a good backup from SharePoint Designer 2007. It might be wise to perform the restore on a separate test server first, before deleting your production site.

To delete a site in anticipation of restoring it from backup, perform the following steps:

  1. From the Home page of the site collection, while logged in as a site admin, select Site Actions, Site Settings.
  2. Under Site Administration, click the link for Delete this Site.
  3. Click the Delete button at the warning message and then click OK.

After you have deleted the site, you must re-create it in Windows SharePoint Services in the same location that it previously was located (The menu path is SharePoint Central Admin, Application Management Tab, Create Site Collection). The only exception to the standard site creation process is that you should apply the Blank Site template to the site as you create it, as illustrated in Figure 17.9. This leaves the site in the state to which SP Designer can restore the original backed-up site.

Figure 17.9. Choose the Blank Site template to allow for a SP Designer restore of a site collection.

image

Restoring Sites from SP Designer Backups

When you have deleted the original site and created a new site in the same URL location with the Blank Site template, you are ready to restore the site using SP Designer. To perform the restore, do the following:

  1. Open SharePoint Designer 2007.
  2. Choose File, Open Site.
  3. Under Site Name, enter the URL of the SharePoint site collection (for example, http://server4/sites/sales) and click Open.
  4. If prompted for a username and password, enter the appropriate credentials.
  5. After the site opens, choose Site, Administration, Restore Web Site.
  6. Select the location of the .cwp file and click Open.
  7. Click OK to confirm the restore operation. SP Designer begins to restore the site.
  8. Click OK when the restore has completed.

Using STSADM to Move or Back Up Site Collections

The STSADM utility is a tool that is installed with Windows SharePoint Services and Microsoft Office SharePoint Server 2007. It gives a great deal of SharePoint functionality. STSADM can import templates, create sites, change settings, and move data. Unlike backing up and moving data with a program like SharePoint Designer, STSADM must be run from the server on which SharePoint is installed, and the user account running STSADM must have admin rights to the sites it will be backing up.

Note

The SMIGRATE command-line tool that was previously used in SharePoint 2003 is not supported with SharePoint 2007. Command-line backups of SharePoint sites should be performed only with the STSADM tool. Sub-site (web) migration is now possible using the STSADM export and import commands.

The STSADM utility is highly useful for backup and restore operations in particular, as it gives administrators a great deal of freedom and flexibility. It allows command-line automation of backups, flexible restores to different locations, and point-in-time snapshots of individual site collections which can be ported to completely different SharePoint 2007 environments.

Backing Up Site Collections with STSADM

To back up a site collection using STSADM, perform the following steps:

  1. From the server that has Windows SharePoint Services installed, open the command prompt by going to Start, Run and then typing cmd.exe into the Open box.
  2. Type the following into the command-line box: cd  Program  filescommon  filesmicrosoft  sharedweb  server  extensions12in
  3. Type stsadm.exe  –o  backup  –url  http://servername/sitecollectionname  -filename  SiteCollectionBackup.dat  –overwrite. servername is the name of your server and sitecollectionname is the top-level site in a collection, as shown in the example in Figure 17.10.

Figure 17.10. Backing up a site collection using STSADM.

image

The advantage of backing up a site collection using this technique is that it can be scripted or run from a batch file. A script is included in this chapter that automates the backup of SharePoint sites using STSADM.

Deleting a Site Collection in Advance of a STSADM Restore

Using the same STSADM utility, you can restore a site collection to the SharePoint Server. To do this, however, there must either be a new configuration database created (which is what you would do if you were restoring an entire server) or you have to delete the old site collection. After the old site collection is deleted, you can restore the original site data. Unlike with SharePoint Designer, you don’t have to create a blank site to accomplish this, but the original site must be deleted or the restore will fail.

Caution

As always, be sure to run these types of operations in a lab environment before testing in production. You can’t be assured of the integrity of the backup, so it is important to check it first before deleting your site.

To delete the original site collection in advance of the restore, use the following procedure:

1. From the server that has Windows SharePoint Services installed, open the command prompt by going to Start, Run and then typing cmd.exe into the Open box.

2. Type the following into the command-line box: cd  Program  filescommon  filesmicrosoft  sharedweb  server  extensions12in.

Figure 17.11. Deleting a site collection in advance of a restore.

image

3. Type stsadm.exe  –o  deletesite  –url  http://servername/sitecollectionname. servername is the name of the server and sitecollectionname is the name of your site collection).

Restoring SharePoint Site Collections Using the STSADM Utility

After the original site collection has been deleted or a new server has been built to replace the old one, you are ready to restore the site collection using the following procedure:

1. From the server that has Windows SharePoint Services installed, open the command prompt by going to Start, Run and then typing cmd.exe into the Open box.

2. Type in the following into the command-line box, as shown in Figure 17.12: cd  Program  filescommon  filesmicrosoft  sharedweb  server  extensions12in.

Figure 17.12. Restoring a site collection using STSADM.

image

3. Type stsadm.exe  –o  restore  –url  http://servername/sitecollectionname  -filename  SiteCollectionBackup.dat. servername is the name of your server.

Using this approach is ideal for migrating data or restoring a site, as it restores all SharePoint site security and SharePoint list data, and can be scripted to run via batch files.

Automating Backups Using a Custom Script That Invokes STSADM

Because the STSADM backup and restore capabilities are so functional and convenient, many organizations choose to incorporate backups using this tool into a regular backup routine. Subsequently, the writers of this book have written a script (SPSiteBackup) that automates the backup process so it can be run against the full collection of sites in a web application. The script works by performing the following actions:

  • Enumerate Sites—The –enumsites option in STSADM is run by the script, which gives the script a current list of all of the site collections in a web application.
  • Backup Sites—The script then uses the –backup option in STSADM to back up each of the sites that were identified during the Site Enumeration process. The sites are backed up to a remote file location specified as part of the script command-line options.
  • Send Email Report—After all of the sites in the web application have been backed up, an email listing the status of the backup is sent to an address specified in the command-line options of the script.

For example, the following command-line string backs up all of the site collections in the sp.companyabc.com web application and sends a summary email to [email protected] via the exchange.companyabc.com SMTP Server.

image

As noted in the previous syntax, this script assumes that both the script (SPSiteBackup.wsf) and the STSADM.EXE file are both located in the C:Backup file location on the SharePoint Server. If the STSADM.EXE file is not in the same directory as the script (or in the PATH System Variable), the operation fails.

Note

This script can be downloaded free from the SAMS Publishing website (http://www.samspublishing.com). Simply search for the ISBN of this book, and then click on the Downloads link.

The following code excerpt should be saved as a .wsf file extension, and run using the cscript command-line syntax described in the previous example.

image

image

image

image

image

image

image

image

image

image

image

image

image

Note

The SPSiteBackup script works on either a SharePoint 2003 or a SharePoint 2007 Server, but it must use the local copy of STSADM, which is restricted to backing up and restoring only the site version that corresponds to the STSADM file version. In other words, you can’t use the 2007 STSADM file to back up 2003 sites and vice versa.

Scheduling the Custom STSADM Backup Script Using Windows Scheduler

The biggest advantage to using a script such as the one previously shown is that it can be scheduled to run automatic weekly or daily backups of the SharePoint site collections. Scheduling the script to run automatically can be done using the Windows Task Scheduler service, which can be configured to run particular program, executable, or batch files on a regular basis.

With this particular script, the Task Scheduler can be configured to run a batch file that contains the string of commands that it needs, such as the following:

image

This batch file simply executes the script with the options described in the earlier in this chapter. Once again, be sure that the WSF script file is located in the same folder as the STSADM executable. The last step in automating this process is to configure the Task Scheduler service to run this batch file on a regular basis.

Note

The Task Scheduler service must be running for this procedure to work properly. If the service is set to Disabled, creation of the task produces errors, and the tasks fail to run. This is often the case if the Security Configuration Wizard with Windows Server 2003 Service Pack 1 has been run against the server. To enable this functionality, set the service back to Automatic and start it on the SharePoint server.

To use the Task Scheduler to automate the SharePoint site backups using the batch file and script, use the following procedure:

1. On the SharePoint Server, go to Start, Control Panel, Scheduled Tasks, Add Scheduled Task.

2. Click Next at the Intro dialog box.

3. Click Browse to locate the batch file created in Step 2.

4. Browse through the folder hierarchy to locate the WSF script. Click once on it to select it and then click Open.

5. Enter a name for the task and how often it should run and click Next.

6. Select a time, how often to perform the task, and a start date. Click Next.

Note

At the subsequent dialog box the credentials of a user with SharePoint site admin rights must be entered. In addition to rights to the local SharePoint box, this account must have the ability to save the backups to the location specified when the script is run. Because it is desirable to automate the backup of the script to a location not on the server itself, it might be wise to have it written to a file server on the internal network. If this is not feasible, it can be written to the local drive, as long as the system is backed up to tape or other removable media, so it can be quickly recovered.

7. Click Finish.

By setting up a simple yet effective schedule to automate SharePoint site backups, it becomes much easier to recover individual site collections.

Backing Up Internet Information Services Configuration

The configuration of Internet Information Services (IIS) in Windows Server 2003 is not backed up by any of the default SharePoint backup methods. However, IIS contains critical configuration information for the SharePoint web applications, such as security settings, ports used, virtual directory information, and any other customizations made to IIS.

Subsequently, it is critical to include an IIS backup process into a SharePoint recovery plan. NTBackup or third-party software that backs up the operating system can perform this function, but there is a built-in script in Windows Server 2003 that will do the backup quickly and easily. This script is called IISBACK.vbs, and it is located in the WindowsSystem32 directory. It can be scripted to run in the same batch file as the STSADM automated script, or it can run as its own manual process.

Typical syntax for running the IISBack.vbs backup routine would be something like the following:

image

In this example, the backup is run and the backup files are initially saved locally, but are then copied to a remote network location to protect against the event of a SharePoint Server failure.

Performing SQL Database–Level Backups and Restores

SharePoint stores configuration and site content in multiple SQL 2000/2005 Server databases. Several databases are used, including content databases, search databases, and the farm configuration database. The configuration database stores configuration settings for all SharePoint Servers in a deployment, including virtual servers. It is imperative that all databases residing on SharePoint’s SQL Server are backed up regularly to minimize data loss.

Different methods exist to back up and restore a SharePoint database in SQL Server. It is possible to back up databases with the SharePoint Central Admin Tool, the SQL Server Management Studio backup utility, a SQL Server Maintenance Plan, or Transact-SQL scripts fired within SQL Server. In addition, a third-party backup utility with the appropriate SQL Server backup agent can be used to back up the databases.

The integrated backup options within SharePoint are a great way to back up site content and configuration databases. However, they lack the capability of restoring the full SQL Server installation to the point of failure if a disaster occurs. This would include databases, security, logins, SSIS packages, maintenance plans, and so forth. The backup options included with SharePoint do not back up vital SQL Server databases such as Master, MSDB, and TempDB, which are used to maintain vital SQL Server configuration settings. Therefore, formulating a SQL Server database backup and recovery strategy that encompasses all SQL Server proprietary and SharePoint databases is recommended.

Many of the backup and restore features that existed in SQL Server 2000 also exist in SQL Server 2005. SQL Server 2005 builds upon these basic set of features and comes with enhancements that include the following: online restores, copy-only backups, mirrored backups, partial backups, and database snapshots.

Understanding SQL Server Backup Methods

Note

This chapter focuses on backup options using the latest version of SQL Server, SQL Server 2005. SQL Server 2000 is still supported for SharePoint 2007, but using the latest SQL technologies is recommended, if possible.

The backup utility included in SQL Server offers several options for backing up databases. These options include the following:

  • Full—Backs up the full database including all file groups and transaction logs.
  • Differential—Backs up all the modified pages in a database after the last successful full backup is completed.
  • Transaction log backup—Backs up all the transactions performed against the database after the last successful full backup or transaction log backup is completed.
  • File and file group backup—Backs up a portion of the database at a time.
  • Partial backup—New to SQL Server 2005, partial backups back up all the data in the primary file group, every read-write file group, and any optionally specified files. Any file groups which are marked as Read Only are skipped to save time and space.
  • Differential partial backup—Similar to a partial backup, but this backup only records data that has changed in the file groups since the preceding partial backup.
  • Copy-only backup—This backup allows a backup of any type to be taken without affecting any other backups. Normally a database backup is recorded in the database itself and is identified as part of a chain that can be used for restoration.

Note

Transaction log backups can be conducted only on databases using Full and Bulk Logged recovery models.

To perform a full SQL database backup on an individual database using the SQL Server Management Studio, do the following:

1. Choose Start, All Programs, Microsoft SQL Server 2005, SQL Server Management Studio.

2. When prompted, connect to the SQL database where the SharePoint database files are housed and click Connect.

3. In Object Explorer, first expand the desired server and then expand the database folder.

4. Select the desired SharePoint database to backup.

5. Right-click the database, select Tasks, and then select Backup.

6. On the General page in the Back up Database window, review the name of the database(s) being backed and validate that the Backup Type option is set to Full.

7. Type the desired name and description for the backup. Select the Database option, as shown in Figure 17.13.

Figure 17.13. Viewing the SQL Server Backup screen.

image

The Destination section identifies the disk or tape media that will contain the backup. Multiple destinations can be specified in this section by clicking the Add button. For disk media a maximum of 64 disk devices can be specified. The same limit applies to tape media. If multiple devices are specified, the backup information will be spread across those devices. All of the devices must be present to restore the database. If no tape devices are attached to the database server, the option to select Tape is disabled.

8. In the Destination section, choose the Disk option. Accept the default backup location or remove the existing path and click Add to select a new destination path for the backup.

9. In Select Backup Destination, type the path on the hard disk where the database backup will be created, including the backup file name. Click OK. Alternatively, a database administrator can also choose a backup device instead of storing the backup on hard disk.

10. It is possible to now initialize the backup or enter advanced backup options by clicking Options in the Select a Page pane, which brings up the page shown in Figure 17.14.

Figure 17.14. Viewing SQL backup options.

image

The Overwrite media section enables you to specify options relative to the destination media for the backup. Keep in mind that a given media set can contain more than one backup. This can occur if the Append to the Existing Backup Set option is selected. With this option, any prior backup that was contained on the media set are preserved and the new backup is added to it. With the Overwrite All Existing Backup Sets option, the media set only contains the latest backup and all prior backups are not retained.

Options in the Reliability section can be used to ensure that the created backup can be used reliably in a restore situation. Verifying the backup when finished is highly recommended but it extends the backup time during the backup verification. Similarly, the Perform checksum Before Writing to Media option helps ensure that you have a sound backup but again it causes the database backup to run longer.

The options in the Transaction Log section are available for databases that are in the Full Recovery or Bulk Logged model. These options are disabled in the simple recovery model. The Truncate the Transaction Log option removes any inactive portion of the transaction log after the database backup is complete. This is the default option and helps keep the size of your transaction log manageable. The Backup the Tail of the Log option is related to point-in-time restores and is discussed in more detail in the later in this chapter.

The last set of options in the Tape Drive section are only enabled when Tape has been selected for the destination media. The Unload the Tape After Backup option ejects the media tape after the backup completes. This can help identify the end of the backup and prevents the tape from being overwritten the next time the backup runs. The Rewind the Tape Before Unloading option causes the tape to be released and rewound prior to unloading the tape. Continue with the process as follows:

  1. In the Options page and Overwrite Media section, maintain the default settings—Back up to the Existing Media Set and Append to the Existing Backup Set.
  2. In the Reliability section, choose the Verify Backup When Finished, Perform checksum Before Writing Media, and Continue on Error options. Click OK to execute the backup.
  3. Review the Success or Failure error message and click OK to finalize.
  4. Repeat for additional SharePoint databases.

Note

To restore the SQL Server 2005 installation used for SharePoint, it is a best practice to back up all SQL Server system databases (such as Master, Model, and MSDB) in addition to the SharePoint databases. The Maintenance Plan Wizard can be used to backup all of these database backups.

Understanding SQL Server Recovery Models

Three recovery models are associated with a database: Simple, Full, and Bulk Logged. Each model addresses different scenarios on performance, minimization of data loss, and recovery of a database to the point of failure. Simple recovery truncates the transaction log. Therefore, a database can only be recovered up until the last successful full or differential database backup. Data entered into the database after a successful full or differential database backup is lost. Full recovery mode maintains the transaction logs and, therefore, it is possible to restore a database to the point of failure. Database files and transaction logs should be stored on separate hard disks or RAID sets for performance and recovery. Maintaining a transaction log degrades SQL Server performance as all transactions to the database are logged. Bulk Logged recovery maintains a transaction log; however, transaction logging is turned off automatically to maximize database performance when large amounts of data are inserted into the database; for example, Bulk Inserts and Indexing.

Database administrators must identify how much data they are prepared to lose. This decision helps a database administrator identify which recovery model to use on each database. By default the SharePoint configuration, AdminContent, and site content databases’ recovery model is set to Full. As a result, these databases can be restored to the point of failure.

To set the recovery model on a SharePoint content database, perform the following steps:

  1. Choose Start, All Programs, Microsoft SQL Server 2005, SQL Server Management Studio.
  2. In Object Explorer, first expand the desired server and then expand the database folder.
  3. Select the desired SharePoint database, right-click on the database, and select Properties.
  4. In the Database Properties dialog box, select the Options node.
  5. In the Recovery Mode dialog box, shown in Figure 17.15, select Full, Bulk-Logged, or Simple from the drop-down list. Full is typically selected in most cases. Click OK to save the changes.

Figure 17.15. Selecting a recovery model.

image

Examining a Real-World SharePoint Database Backup Scenario

SQL Server concepts can sometimes be complex, and it can sometimes be useful to illustrate an example of a “best practice” implementation. This section presents an example that illustrates the steps of a backup strategy for the SharePoint configuration database used by the fictional CompanyABC.

CompanyABC has a service-level agreement which states that the database administrator must be able to restore the company’s SharePoint configuration database to the point of failure. To accomplish this, the CompanyABC database administrator performs the following steps:

  1. Verifies that the recovery model is set to Full.
  2. Moves the database files and transaction logs onto separate hard drives.
  3. Creates and schedules a full database backup to occur once a day.
  4. Creates a transaction log database backup every hour. The latency on the transaction log backups depends on how much data changes on the database throughout each day.

For this example, CompanyABC has a substantial amount of SharePoint configuration changes and database transactions, which made a case for transaction log backups to be created more frequently. If CompanyABC suffers a disaster at 6:00 a.m. on the configuration database, the database administrator can simply restore the full backup from midnight and replay the transaction logs up until the point of failure. It is also possible to restore a transaction log to a specific time if ever required. To summarize this example, the Full recovery model allows CompanyABC to successfully restore the SharePoint database to the point of failure without suffering any data loss.

Summary

The large number of backup techniques available for SharePoint might seem confusing at first. While it would be ideal to have some type of unified backup technique, each type of backup allows for different backup and restore functionality. It is subsequently important to gain a full understanding of how each method works so SharePoint can be properly backed up and restored when needed.

Best Practices

  • Consider the use of third-party backup software for SharePoint to allow for more enterprise-level automation and restore capabilities.
  • Use the Central Admin Backup and Restore tool for manual, single point-in-time backups. Use other backup strategies, such as scheduled STSADM backups or automated SQL Backups, to supplement the backup process and to provide for backup automation.
  • Incorporate IIS backups into a backup routine. Use the integrated IISBack.vbs script to automate the backup process.
  • Transfer individual elements from one site to another using SharePoint Designer 2007.
  • Use the 2005 version of SQL when possible.
  • Verify that the SQL recovery model is set to Full on SharePoint databases to allow for full restores of SQL data.
  • Use the STSADM command-line program to back up individual sites, but use the SQL tools or the Central Admin Tool to back up the entire farm.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset