How to back up and restore installations of Windows SharePoint Services that use Microsoft SQL Server 2000 Desktop Engine (Windows)

Article translations Article translations
Article ID: 833797 - View products that this article applies to.
Expand all | Collapse all

On This Page

SUMMARY

This article discusses how to back up and restore installations of Windows SharePoint Services that use Microsoft SQL Server 2000 Desktop Engine (Windows) (WMSDE). Installations of Windows SharePoint Services with WMSDE have unique backup requirements. You can use either the Stsadm.exe command-line tool or the Osql.exe command-line tool to back up and restore Windows SharePoint Services content. This article contains information about how to back up and restore Windows SharePoint Services Web sites. This article also describes how to back up Windows SharePoint Services content databases and restore the data to the same server where the backup was performed and how to restore the data to another server that is running Windows SharePoint Services.

INTRODUCTION

This article describes how to back up and restore Microsoft Windows SharePoint Services if you use Microsoft SQL Server 2000 Desktop Engine (Windows) as the database type.
Note You can directly backup and restore Microsoft Windows Sharepoint Services 3.0. See the topic about backing up and restoring in the Microsoft Windows SharePoint Services 3.0 Administrator's Guide

Installations of Windows SharePoint Services that are deployed with WMSDE have unique backup requirements because the Enterprise Management component that is included in Microsoft SQL Server 2000 is not included in WMSDE. If you use WMSDE as the database type when you install Windows SharePoint Services, you can use either the Stsadm.exe command-line tool or the Osql.exe command-line tool to back up and restore Windows SharePoint Services content. It is a good idea to back up your data regularly. You may also want to back up your data before you apply an update to the server.

Back up and restore Windows SharePoint Services Web sites



In Microsoft Windows SharePoint Services, you can back up and restore individual Web sites that are hosted on your server or on your server farm. You can back up and restore Web sites when you have to replace a Web site that is corrupted or when you have to replace a site that contains changes that must be rolled back. For example, you may have to recover a list or a list item that was unintentionally deleted.

When you back up a Web site, you back up the data in the content database for the site. The data that you back up includes Web pages in the Web site, files in document libraries or in lists, security and permission settings, and feature settings. The backup process creates a single file that contains all the data. After you back up a Web site, you can restore the Web site either to the same location or to a new location.

You can back up only top-level Web sites, not individual subsites. The file that is created after you back up a top-level Web site includes subsites of the top-level Web site. For more information about how to use the Stsadm.exe command-line tool to back up and restore Windows SharePoint Services Web sites, see the "Backing Up and Restoring Web Sites" topic in the "Backup and Migration" chapter of the Microsoft Windows SharePoint Services 2.0 Administrator's Guide.

Back up and restore Windows SharePoint Services content databases

In a typical installation of Windows SharePoint Services with WMSDE, Windows SharePoint Services creates one configuration database and one content database. For any particular deployment of Windows SharePoint Services there is only one configuration database. However, there may be one or more content databases.

You have to back up only the content databases. You do not have to back up the configuration database. When you restore the content databases, you can restore content databases even if you use a new configuration database.

Back up content databases

To back up content databases, follow these steps.

Step 1: Document the content databases that your Windows SharePoint Services virtual servers use

To document the content databases that your Windows SharePoint Services virtual servers use, follow these steps:
  1. Start SharePoint Central Administration.
  2. Under Virtual Server Configuration, click Configure virtual server settings.
  3. On the Virtual Server List page, click the name of the first virtual server that you extended. For example, click Default Web Site.
  4. On the Virtual Server Settings page, click Manage content databases under Virtual Server Management.
  5. Make a note of the names of the databases that are displayed in the Database Name column on the Manage Content Databases page.
  6. Repeat steps 3 through 5 for each virtual server that is configured on the Web server.


Step 2: Create a backup folder to store the backup copy of the content databases

On the hard disk, create a folder to store the backup copy of the content databases.

By default, the OSQL query examples store backup copies of the content databases to the following folder:
Drive:\Program Files\Microsoft SQL Server\Mssql$Sharepoint\Backup
This folder is not created by WMSDE. If you want to use this folder to store backup copies of the content databases, you have to create it.



Step 3: Use the OSQL command-line tool to back up the content databases

To use the OSQL command-line tool to back up the content databases, follow these steps:
  1. Click Start, and then click Run.
  2. In the Open box, type cmd, and then press ENTER.
  3. At the command prompt, type the following lines and press ENTER after each line, where ServerName is the name of the server:
    osql -S ServerName\sharepoint -E
    backup database STS_ServerName_1 To Disk = 'Drive:\program files\microsoft sql server\mssql$sharepoint\backup\sts_ServerName_1'
    GO
  4. Repeat step 3 for each database that you want to back up.
  5. Type QUIT, and then press ENTER.

Restore content databases

When you restore a content database and add it back to a virtual server, you have to perform additional steps so that Windows SharePoint Services can detect whether the content database requires an update. For example, if you apply an update to Windows SharePoint Services after you perform a backup of Windows SharePoint Services, the content database must also be updated after the content database is restored to the virtual server. To make sure that the content database is also updated when it is restored to the virtual server, you have to remove the content database from the virtual server, restore the content database by using the OSQL command-line tool, and then add the content database back to the virtual server by using SharePoint Central Administration.

To restore content databases, follow these steps.

Step 1: Remove the content databases from the virtual server

To remove the content databases from the virtual server, follow these steps:
  1. Start SharePoint Central Administration.
  2. Under Virtual Server Management, click Manage content databases.
  3. On the Manage Content Databases page, click the name of the content database that you want to restore from backup.
  4. On the Manage Content Database Settings page, in the Remove Content Database area, click to select the Remove content database check box.
  5. Click OK.


Step 2: Pause SQL Server 2000 Desktop Engine (Windows) to release exclusive locks

To pause WMSDE, follow these steps:
  1. Click Start, and then click Run.
  2. In the Open box, type cmd, and then press ENTER.
  3. At the command prompt, type the following lines and press ENTER after each line:net pause mssql$sharepoint
    net continue mssql$sharepoint
  4. Type exit, and then press ENTER.



Step 3: Use the OSQL command-line tool to restore a database

To use the OSQL command-line tool to restore a database, follow these steps:
  1. Click Start, and then click Run.
  2. In the Open box, type cmd, and then press ENTER.
  3. At the command prompt, type the following lines and press ENTER after each line, where ServerName is the name of the server:
    osql -S ServerName\sharepoint -E
    restore database STS_ServerName_1 from Disk = 'Drive:\program files\microsoft sql server\mssql$sharepoint\backup\sts_ServerName_1'
    GO
  4. Repeat step 3 for each database that you want to restore.
  5. Type QUIT, and then press ENTER.
Note You may receive an error message that is similar to the following when you perform the restore operation:
Exclusive access could not be obtained because the database is in use.
If you receive this error message, repeat the procedure in the Step 2: Pause SQL Server 2000 Desktop Engine (Windows) to release exclusive locks section earlier in this article.



Step 4: Add the content databases that you restored to the virtual server

To add the content databases that you restored to the virtual server, follow these steps:
  1. Start SharePoint Central Administration.
  2. Under Virtual Server Configuration, click Configure virtual server settings.
  3. On the Virtual Server List page, click the name of the virtual server where you restored the database.
  4. On the Virtual Server Settings page, click Manage content databases under Virtual Server Management.
  5. On the Manage Content Databases page, click Add a content database under Content Databases.
  6. On the Add Content Database page, click Specify database server settings in the Database Information area, and then specify the name of the database and the name of the database server.
  7. In the Database Capacity Settings area, type the number that you want in the Number of sites before a warning event is generated box.
  8. Click OK.
  9. Repeat steps 2 through 8 for each content database that you want to add.


Restore content databases to another server

There may be certain situations where you want to restore a content database to another server. For example, if data is accidentally deleted from a Windows SharePoint Services Web site, you may not want to restore the whole content database. There may be additional data that was added to the site after the last backup operation was performed. In this situation, you may want to install Windows SharePoint Services on another server, and then restore the content database to the new server. After you restore the original content database to the new server and you add the content database that you restored to the virtual server, you can connect to the Windows SharePoint Services by using the server name of the new server. For example, use http://ServerName to connect to the Web site. The Web site represents the data that was present at the time of the last backup.

To restore content databases to another server, follow these steps.

Step 1: Install Windows SharePoint Services to another server
  1. Install Windows SharePoint Services to another server that is running Microsoft Windows Server 2003. For additional information about how to install Windows SharePoint Services, see the "Installation" chapter of the Microsoft Windows SharePoint Services 2.0 Administrator's Guide.
  2. Copy the backup copy of the content database from the original server to the new server.


Step 2: Use the OSQL command-line tool to restore the content database

To use the OSQL command-line tool to restore the content database, follow these steps:
  1. Click Start, and then click Run.
  2. In the Open box, type cmd, and then press ENTER.
  3. At the command prompt, type the following lines and press ENTER after each line, where ServerName is the name of the server:
    osql -S ServerName\sharepoint -E
    restore database STS_ServerName_1 from Disk = 'Drive:\program files\microsoft sql server\mssql$sharepoint\backup\sts_ServerName_1'
    GO
  4. Repeat step 3 for each database that you want to restore.
  5. Type QUIT, and then press ENTER.


Step 3: Configure SQL permissions

To configure SQL permissions so that you can add the content database to the virtual server by using SharePoint Central Administration, follow these steps:
  1. Click Start, and then click Run.
  2. In the Open box, type cmd, and then press ENTER.
  3. At the command prompt, type the following lines and press ENTER after each line, where ServerName is the name of the server:
    osql -S ServerName \sharepoint -E sp_addsrvrolemember 'nt authority\network service', 'sysadmin'
    GO
  4. Type exit, and then press ENTER.
Note This procedure adds the Network Service account to the SQL sysadmin role. The Network Service account is a built-in Windows account that Windows SharePoint Services uses for installations with WMSDE. Windows SharePoint Services SharePoint runs under the context of the Network Service account to connect to WMSDE. If you deploy a remote SQL server and you use a domain account, you have to configure the domain account as the database owner (dbo) of the database before you add the database to the virtual server.

For more information about migrating from SQL Server 2000 Desktop Engine (Windows) to Microsoft SQL Server, see the "Migrating from WMSDE to SQL Server" topic in the "Backup and Migration" chapter of the Microsoft Windows SharePoint Services 2.0 Administrator's Guide. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
828815 "Database already exists" error message when you try to manage your Windows SharePoint Services content database


Step 4: Remove the content databases from the virtual server

To remove the content databases from the virtual server, follow these steps:
  1. Start SharePoint Central Administration.
  2. Under Virtual Server Management, click Manage content databases.
  3. On the Manage Content Databases page, click the name of the database that was created when you installed Windows SharePoint Services on the server.
  4. On the Manage Content Database Settings page, click to select the Remove content database check box in the Remove Content Database area.
  5. Click OK.


Step 5: Add the content database that you restored to the virtual server

To add the content database that you restored to the virtual server, follow these steps:
  1. On the Central Administration page, click Configure virtual server settings under Virtual Server Configuration.
  2. On the Virtual Server List page, click the name of the virtual server.
  3. On the Virtual Server Settings page, click Add a content database under Virtual Server Management.
  4. On the Virtual Server Settings page, click Manage content databases under Virtual Server Management.
  5. On the Add Content Database page, click Specify database server settings in the Database Information area, and then specify the name of the database and the name of the database server.
  6. In the Database Capacity Settings area, type the number that you want in the Number of sites before a warning event is generated box.
  7. Click OK.


Step 6: Configure SQL permissions back to the original settings

To configure SQL Server permissions back to the original settings, follow these steps:
  1. Click Start, and then click Run.
  2. In the Open box, type cmd, and then press ENTER.
  3. At the command prompt, type the following lines and press ENTER after each line, where ServerName is the name of the server:
    osql -S ServerName \sharepoint -E sp_dropsrvrolemember 'nt authority\network service', 'sysadmin'
    GO
  4. Type exit, and then press ENTER.

REFERENCES

To obtain the Microsoft Windows SharePoint Services 2.0 Administrator's Guide, visit the following Microsoft Web site:
http://www.microsoft.com/downloads/details.aspx?FamilyID=a637eff6-8224-4b19-a6a4-3e33fa13d230&displaylang=en
For more information about Windows SharePoint Services, visit the following Microsoft Web site:
http://technet.microsoft.com/windowsserver/sharepoint/default.aspx

Properties

Article ID: 833797 - Last Review: September 26, 2007 - Revision: 3.3
APPLIES TO
  • Microsoft Windows SharePoint Services 2.0
Keywords: 
kbwebservices kbbackup kbhowto KB833797

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com