Microsoft KB Archive/929649

Knowledge Base

How to use SQL Server to take a snapshot of a Windows SharePoint Services 3.0 content database

Article ID: 929649

Article Last Modified on 1/9/2007

---

APPLIES TO

• Microsoft Windows SharePoint Services 3.0
• Microsoft Office SharePoint Server 2007

---

INTRODUCTION

This article describes how to use Microsoft SQL Server 2005 to take a snapshot of a Microsoft Windows SharePoint Services 3.0 content database. You can use SQL Server snapshot technology to create a temporary backup of a Windows SharePoint Services 3.0 content database. Snapshots let users access deleted content without having to fully restore the content database.

MORE INFORMATION

A SQL Server snapshot is a read-only view of a database as the database existed at the time that the snapshot was created. SQL Server snapshots are created by using differential copying. After the snapshot is created, the snapshot technology uses a copy-on-write scheme to make sure that the snapshot images are static views of the database.

You can use snapshot technology to create a read-only, point-in-time copy of a Windows SharePoint Services 3.0 content database without the need to create a full backup of the Windows SharePoint Services 3.0 content databases. You can create snapshot instances of a Windows SharePoint Services 3.0 site to let users retrieve content from different points in time.

For more information about SQL Server snapshots, visit the following Microsoft Web site:

http://msdn2.microsoft.com/en-us/library/ms187054.aspx

Prerequisites for creating SQL Server snapshots of the Windows SharePoint Services 3.0 content database

The following list contains the prerequisites for creating SQL Server snapshots of the Windows SharePoint Services 3.0 content database:

• A computer that has the following applications installed:
  • Either Window SharePoint Services 3.0 or Microsoft Office SharePoint Server 2007
  • Microsoft SQL Server 2005 Enterprise Edition
• A backup computer for each point-in-time copy of the content database that you want. Your backup computers must have the same version of Windows SharePoint Services or SharePoint Server installed that is installed in your production environment. When you install Windows SharePoint Services on the backup computer, make sure that you select a farm deployment during the setup process.

Note If the Windows SharePoint Services 3.0 site or the SharePoint Server 2007 site contains custom Web parts, solutions, or other binaries that you want to include in the backup, make sure that the same custom Web parts, solutions, and binaries also reside on the backup computer.

How to create a SQL Snapshot of the Windows SharePoint Services 3.0 content database

1. Use an account that has administrative permissions to log on to the computer that is running SQL Server 2005.
2. Click Start, point to All Programs, click Microsoft SQL Server 2005, and then click SQL Server Management Studio.
3. In the Connect to Server dialog box, click the options that you want, and then click Connect.
4. In the database list, click the content database that you want.
5. In the Microsoft SQL Server Management Studio MMC snap-in, click New Query.

6. Copy the following text, and then paste it to the query pane.

   CREATE DATABASE WSS_Backup1
   ON
   (
   NAME=WSS_Content,
   FILENAME = 'c:\WSS_Backup1.ss')
   AS SNAPSHOT OF <WSS_Content>;

   WSS_Content is the name of the content database that hosts the SharePoint site.

7. Click Execute to take a snapshot of the content database.
8. On the backup computer, click Start, point to Administrative Tools, and then click SharePoint 3.0 Central Administration.
9. On the Central Administration page, click Application Management.
10. On the Application Management page, click Create or Extend Web Application.
11. On the Create or Extend Web Application page, click Create a New Web Application.
12. Next to Database Name and Authentication, specify in the database server field the name of the production database server that contains the WSS_backup1 database.
    
    When you complete this step, the backup server points to the snapshot database. To verify this, open the SharePoint site on the backup server to make sure that the site displays the information that you backed up. Changes to your production server do not affect the backup system.

To use a new snapshot with the backup server, delete the original snapshot, and then run the SQL Server query to create a new snapshot that has the same name. No update is needed in Windows SharePoint Services 3.0 as long as the site map information in the content database remains the same.

If you change the site map information, you must remove the old snapshot database image from SharePoint 3.0 Central Administration. For example, you must remove the old snapshot database image if you add a new top-level site. Then, add the new snapshot database image to update your configuration database on the backup system. To do this, you can use the following commands at the command prompt.

Note By default, the Stsadm.exe file is located in the Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN folder.

stsadm -o deletecontentdb -url http:// backupservername:port -databasename WSS_Backup1 -databaseserver sqlservername

stsadm -o addcontentdb -url http:// backupservername:port -databasename WSS_Backup1 -databaseserver sqlservername

The placeholders in these commands are defined as follows:

• The backupservername placeholder is the name of the backup computer.
• The port placeholder is the port number.
• The sqlservername placeholder is the name of the computer that is running SQL Server 2005 and that hosts the Windows SharePoint Server content database.
• The WSS_Backup1 placeholder is the name of the snapshot image.

Note

• The snapshot version of the Windows SharePoint Services Web site does not have full functionality. For example, you cannot write to the snapshot version or upload a file to the snapshot version.

Additional query words: MOSS 2007 WSSv3 timeline

Keywords: kbexpertiseinter kbhowto KB929649

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.