Microsoft KB Archive/833797

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

Article ID: 833797

Article Last Modified on 9/26/2007

-

APPLIES TO


 * Microsoft Windows SharePoint Services

-





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.''



IN THIS TASK

 * INTRODUCTION
 * Back up and restore Windows SharePoint Services Web sites
 * Back up and restore Windows SharePoint Services content databases
 * Back up content databases
 * Step 1: Document the content databases that your Windows SharePoint Services virtual servers use
 * Step 2: Create a backup folder to store the backup copy of the content databases
 * Step 3: Use the OSQL command-line tool to back up the content databases
 * Restore content databases
 * Step 1: Remove the content databases from the virtual server
 * Step 2: Pause SQL Server 2000 Desktop Engine (Windows) to release exclusive locks
 * Step 3: Use the OSQL command-line tool to back up the content databases
 * Step 4: Add the content databases that you restored to the virtual server
 * Restore content databases to another server
 * Step 1: Install Windows SharePoint Services to another server
 * Step 2: Use the OSQL command-line tool to restore the content database
 * Step 3: Configure SQL permissions
 * Step 4: Remove the content databases from the virtual server
 * Step 5: Add the content database that you restored to the virtual server
 * Step 6: Configure SQL permissions back to the original settings
 * REFERENCES



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 to the top

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 &quot;Backing Up and Restoring Web Sites&quot; topic in the &quot;Backup and Migration&quot; chapter of the Microsoft Windows SharePoint Services 2.0 Administrator's Guide.

back to the top

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 to the top

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.

back to the top

 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:



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.

back to the top

 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:  Click Start, and then click Run. In the Open box, type cmd, and then press ENTER. At the command prompt, type the following lines and press ENTER after each line, where  is the name of the server:

osql -S \sharepoint -E

backup database STS_ _1 To Disk = ' :\program files\microsoft sql server\mssql$sharepoint\backup\sts_ _1'

GO

 Repeat step 3 for each database that you want to back up. Type QUIT, and then press ENTER.

back to the top

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.

back to the top

 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
 * 1) Type exit, and then press ENTER.

back to the top

 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:  Click Start, and then click Run.</li> In the Open box, type cmd, and then press ENTER.</li> At the command prompt, type the following lines and press ENTER after each line, where  is the name of the server:

osql -S \sharepoint -E

restore database STS_ _1 from Disk = ' :\program files\microsoft sql server\mssql$sharepoint\backup\sts_ _1'

GO

</li> Repeat step 3 for each database that you want to restore.</li> Type QUIT, and then press ENTER.</li></ol>

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.

back to the top

 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.

back to the top

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 &quot;Installation&quot; 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.

back to the top

 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:  Click Start, and then click Run.</li> In the Open box, type cmd, and then press ENTER.</li> At the command prompt, type the following lines and press ENTER after each line, where  is the name of the server:

osql -S \sharepoint -E

restore database STS_ _1 from Disk = ' :\program files\microsoft sql server\mssql$sharepoint\backup\sts_ _1'

GO

</li> Repeat step 3 for each database that you want to restore.</li> Type QUIT, and then press ENTER.</li></ol>

back to the top

 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:  Click Start, and then click Run.</li> In the Open box, type cmd, and then press ENTER.</li> At the command prompt, type the following lines and press ENTER after each line, where  is the name of the server:

osql -S ServerName \sharepoint -E sp_addsrvrolemember 'nt authority\network service', 'sysadmin'

GO

</li> <li>Type exit, and then press ENTER.</li></ol>

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 &quot;Migrating from WMSDE to SQL Server&quot; topic in the &quot;Backup and Migration&quot; 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 &quot;Database already exists&quot; error message when you try to manage your Windows SharePoint Services content database

back to the top

 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.

back to the top

 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.

back to the top

 Step 6: Configure SQL permissions back to the original settings

To configure SQL Server permissions back to the original settings, follow these steps: <ol> <li>Click Start, and then click Run.</li> <li>In the Open box, type cmd, and then press ENTER.</li> <li>At the command prompt, type the following lines and press ENTER after each line, where  is the name of the server:

osql -S ServerName \sharepoint -E sp_dropsrvrolemember 'nt authority\network service', 'sysadmin'

GO

</li> <li>Type exit, and then press ENTER.</li></ol>

back to the top

<div class="references_section">