Moving the Databases (Windows SharePoint Services 2.0)

Article
05/05/2010

Use the procedures in this section to move the databases that support Windows SharePoint Services to another drive on your server computer or to a separate server running Microsoft SQL Server 2000.

Moving the Databases to a Different Hard Disk Drive on the Same Server

This procedure guides you through taking each database offline, detaching it, moving it to a different drive on the same server, reattaching it, and restarting it.

Move databases to a different hard disk drive

Stop the Web server by typing the following at the command prompt:
```
iisreset /stop
```
Stop the SharePoint Timer Service by typing the following at the command prompt:
```
net stop sptimer
```
At the command prompt, launch the OSQL command line utility by typing the following:
```
osql –E –S %computername%\SharePoint
```
To detach each database, type the following, pressing ENTER after each line:
```
EXEC sp_detach_db "Database_Name", "true" GO
```
The prompt will increment in value as you enter each command.

Note

This step must be followed once for your configuration database and once for each content database.
By using Windows Explorer, move each database to its new location. For each database, move the database (.mdf) file and the transaction log (.ldf) file.
In the command window running the OSQL utility, reattach each database using the new location for the files, by typing the following:
```
EXEC sp_attach_db 'Database_Name', 'd:\New_Location\Database_Name.mdf', 'd:\New_Location\Database_Name_log.LDF' GO
```
Note

These steps must be followed once for your configuration database and once for each content database.
Exit the OSQL utility by pressing CTRL+C.
Restart the SharePoint Timer Service by typing the following at the command prompt:
```
net start sptimer
```
Restart the Web server by typing the following at the command prompt:
```
iisreset /start
```

Moving the Databases to a Database Server Running Microsoft SQL Server 2000

The procedures in this section guide you through moving your database files from the default location to a separate server, reattaching them, and updating the configuration database to point to the new location. Before starting, choose or create a domain account to use to connect to the SQL Server 2000 databases after they are moved to the new server. To help protect security, give this account the minimum privileges that will support it having the database owner (dbo) role on the SQL Server database. For details, see the Microsoft SQL Server 2000 documentation.

Note

In the following procedure, the server from which you are moving the database files is called the source server and the server to which you are moving the files is called the destination server.

Move the databases

On the source server, stop the Web server by typing the following at the command prompt:
```
iisreset /stop
```
On the source server, stop the SharePoint Timer Service by typing the following at the command prompt:
```
net stop sptimer
```
On the source server, stop the WMSDE database server by typing the following at the command prompt:
```
net stop mssql$sharepoint
```
By using Windows Explorer, move each database to the new location on the destination server.

Note

This step must be followed once for your configuration database and once for each content database. For each database, move the database (.mdf) file and the transaction log (.ldf) file.

Attach the databases

On the destination server, at a Windows command prompt, launch the OSQL command line utility by typing the following:
```
osql –E –S %computername%
```
Note

If you specified an instance name when installing SQL Server, append it to %computername% as follows:
```
osql –E –S %computername%\Instance_Name
```
In the command window running OSQL, attach each database using the new location for the files by typing the following and pressing ENTER after each line:
```
EXEC sp_attach_db 'Database_Name', 'd:\New_Location\Database_Name.mdf', 'd:\New_Location\Database_Name,_log.LDF' GO
```
Note

This step must be followed once for your configuration database and once for each content database.
Exit the OSQL utility by pressing CTRL+C.

Assign a domain account under which the databases will run

On the source server, click Start, point to All Programs, point to Administrative Tools, and then click Internet Information Services (IIS) Manager.
In Internet Information Services (IIS) Manager, expand the local computer and expand the Application Pools folder. All application pools for the server are listed. By default, the application pools STSAdminAppPool and STSAppPool1 are associated with Windows SharePoint Services virtual servers. If you have created other application pools for virtual servers, they will be listed here.
For each application pool associated with a virtual server, do the following:
1. Right click the application pool, and then click Properties.
2. On the Identity tab, click Configurable.
3. In the User name field, enter the domain account under which the databases will run on the destination server. This same account must have database owner (dbo) privileges in the SQL Server database.
  
  Note
  
  You can reset the default account and password by clicking Predefined.
4. In the Password field, enter the password for the domain account, and then click OK.
On the source server, add the domain account to the IIS_WPG and STS_WPG local groups.

Set the configuration database in Windows SharePoint Services

On the source server, in Internet Information (IIS) Services Manager, expand the local computer, expand Web Sites, right-click the SharePoint Central Administration Web site, and click Start.
Open the SharePoint Central Administration Pages. On the Start menu, point to All Programs, click Administrative Tools, and then click SharePoint Central Administration.
In the Server Configuration section, click Set configuration database server.
On the Set Configuration Database page, in the Database server field, enter the name of the destination server.
On the Set Configuration Database page, click Connect to existing configuration database, and click OK.
On the Central Administration page, in the Server Configuration section, click Set default content database server.
On the Set Default Content Database Server page, in the Database server field, enter the name of the destination server, and then click OK.

Set the content database in Windows SharePoint Services

On the Central Administration page, under Virtual Server Configuration, click Configure virtual server settings.
Click the link for the virtual server containing the databases that you are moving.
On the Virtual Server Settings page, under Virtual Server Management, click Manage content databases.
For each content database listed under Content Databases, do the following:
1. Click the database link.
2. On the Manage Content Database Settings page, note the database name as displayed in the SQL Server database name field.
3. On the Manage Content Database Settings page, note the database capacity settings, as displayed in the Number of sites before a warning message is generated and the Maximum number of sites that can be created in this database fields. You will need this information when adding the databases to the destination server.
4. Disconnect the database from the source server by selecting the Remove content database check box, and then click OK.
Attach each content database to the destination server. For each content database listed under Content Databases, do the following:
1. Click Add a content database.
2. In the Database Name field, type the name of the content database.
3. In the Number of sites before a warning message is generated and the Maximum number of sites that can be created in this database fields, enter the capacity settings information you saved for the content database, and click OK.
If you are moving databases for more than one virtual server, repeat steps 2 through 5 for each additional virtual server.
On the source server, restart the SharePoint Timer Service by typing the following at the command prompt:
```
net start sptimer
```
On the source server, restart the Web server by typing the following at the command prompt:
```
iisreset /start
```

Conclusion

By following the steps in this document, you have learned how to locate the databases on a server running Microsoft Windows SharePoint Services and move them to a different drive on the same server or to a separate server running Microsoft SQL Server 2000. After you have moved the databases and restarted IIS, test the databases in their new locations by browsing to your Web sites and viewing pages, document libraries, and lists.