Back up and Restore installations of Windows SharePoint Services that use Microsoft SQL Server 2000 Desktop Engine

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.



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.

Online Backup server