ConfigMgr 2007: How to move the Site Database

I was having trouble finding this post on the old SMSandMOM blog without a direct link, and saw this question asked on an alias so I thought it would be worth posting again on the new blog site for ConfigMgr 2007. This post describes how to move the Site Database for Configuration Manager 2007 to another drive, or another computer running SQL 2005.

INTRODUCTION
This article describes how to move the Site Database in Microsoft System Center Configuration Manager 2007 from a computer that is running Microsoft SQL Server 2005 to another drive on the same computer, or to another computer that is running SQL Server 2005.

MORE INFORMATION
In certain situations, you may have to move the Site Database from a computer that is running SQL Server 2005 to another drive, or to another computer that is running SQL Server 2005. For example, the following situations may require that you move the Site Database:

• You experience hardware issues on the server that you currently use. Additionally, you do not consider the server to be reliable.
• You have to move the Site Database and the log file to a different volume because the database requires more space, or because you want to improve performance.
• The server that you currently use is leased. The lease on the server is scheduled to expire soon.
• New hardware standards have been developed and approved. You must upgrade the computer that is running SQL Server 2005 to the new hardware specifications.

SQL Server 2005 supports the following:

• You can move files and log files from one computer to another computer if both computers are running SQL Server 2005.
• You can move data files and log files from one instance of SQL Server 2005 to another instance of SQL Server 2005 if both instances are on the same computer.
• You can move data files and log files from one volume to another volume on a computer that is running SQL Server 2005.    

For more information about these functionalities in SQL Server 2005, visit the following Microsoft Web site: http://technet.microsoft.com/en-us/library/ms203721.aspx

PREREQUISITES
Before you move the Site Database from a computer that is running SQL Server 2005 to another drive or another computer that is running SQL Server 2005, follow  these steps:

1. Back up all the databases from their current locations. This includes the master database.
2. Verify that you have system administrator permissions on both the computers that are running SQL Server 2005.
3. Verify that you have configured the computer where you want to move the Site Database exactly like the computer that currently hosts the Site Database.
4. Verify that you know the name and the current location of the Site Database.
5. Stop the following Configuration Manager services on the Microsoft System Center Configuration Manager 2007 site server:
       •    SMS_EXECUTIVE Service    
       •    SMS_SITE_COMPONENT_MANAGER Service    
       •    SMS_SITE_SQL_BACKUP Service   
       •    SMS_SITE_VSS_WRITER

Note: You can stop all of these services using the Preinst.exe utility by running the following command without the quotes:

"Preinst.exe /STOPSITE"

Preinst.exe is included with Microsoft System Center Configuration Manager 2007 Server and is located in the following path:

Drive:\Program Files\Microsoft Configuration Manager\bin\i386\00000409 (the last folder is dependent upon the language of the product, 00000409 is for the English version).

Note: If you are running the SMS Provider and the Site Database on the same SQL 2005 Server, and you are moving the Site Database to a new server, you will also need to modify the SMS Provider Configuration in order to move it as well, For more information about moving the SMS Provider in Microsoft System Center Configuration Manager 2007, visit the following Microsoft Web site:  http://technet.microsoft.com/en-us/library/bb693923.aspx.

MOVING THE DATABASE
To move the Site Database from a computer that is running SQL Server 2005 to another drive or another computer that is running SQL Server 2005, follow these steps:

Step 1: Detach the database
1. On the computer that currently hosts the Site Database, click Start, point to Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.    

2. Click the appropriate values in the Server type list, in the Server name list, and in the Authentication list. Then, click Connect.

3. Expand the Databases folder, right-click the SMS_<DatabaseName> folder, point to Tasks, and then click Detach.  Note that the Detach command is visible only if the following conditions are true:

    •    You are a member of the sysadmin fixed server role.    
    •    The server to which you are connected is running SQL Server 2005.    

4. Verify the status of the Site Database. Note that to successfully detach the Site Database, the status in the Databases to detach box in the Status column must read: "The database is ready to be detached." Optionally, you can update statistics before the detach operation. To do this, select the check box under the Update Statistics column in the Databases to detach box.    

5. To close any existing connections in the Site Database, select the check box under the Drop Connections column in the Databases to detach box.

6. Click OK. The database node of the detached Site Database disappears from the Databases folder.

7. After the Site Database is detached, copy the SMS_<DatabaseName>.mdf file and the SMS_<DatabaseName>.ldf file to the drive and path you want to move it to, or to a folder on the computer to which you want to move the Site Database.    

Note: The following path is the default path of the SMS_<DatabaseName>.mdf file:

Drive :\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

The following path is the default path of the SMS_<DatabaseName>.ldf file:

Drive :\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

Step 2: Attach the database

1. Click Start, point to Programs, point to Microsoft SQL Server 2005, and then click SQL Server Management Studio.

2. Click the appropriate values in the Server type list, in the Server name list, and in the Authentication list. Then, click Connect.

3. Right-click the Databases folder, and then click Attach.   Note The Attach command is visible only if the following conditions are true:

• You are a member of the sysadmin fixed server role.
• The server to which you are connected is running SQL Server 2005.

4. In the Attach Databases dialog box, click Add to specify the database that you want to attach.

5. Locate and then click the SMS_<DatabaseName>.mdf file. Then, click OK.

Note The following path is the default path of the SMS_<DatabaseName>.mdf file:  Drive :\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

6. On the View menu, click Refresh to view the database node of the attached Site Database.

Note: If you moved the Site Database files to another drive on the same computer, then your move is complete, and you can start the following services:

• SMS_EXECUTIVE Service
• SMS_SITE_COMPONENT_MANAGER Service
• SMS_SITE_SQL_BACKUP Service
• SMS_SITE_VSS_WRITER

Step 3: Update the database server name

If you moved the Site Database to another server, you need to run the Microsoft System Center Configuration Manager Setup Wizard on the Microsoft System Center Configuration Manager 2007 Server to modify the SQL Server  configuration to specify the new SQL Server name.

1. Ensure the primary site server computer account has administrative privileges over the new site database server computer.

2. Close any open Configuration Manager console connections to the site server.

3. On the primary site server computer, use the hierarchy maintenance tool (Preinst.exe) to stop all site services with the following command: Preinst /stopsite.

4. On the primary site server computer, click Start, click All Programs, click Microsoft System Center, click Configuration Manager 2007, and click ConfigMgr Setup, or navigate to the .\bin\i386 directory of the Configuration Manager 2007 installation media and double-click Setup.exe.

5. Click Next on the Configuration Manager Setup Wizard Welcome page.

6. Click Perform site maintenance or reset this site on the Configuration Manager Setup Wizard Setup Options page.

7. Select Modify SQL Server configuration on the Configuration Manager Setup Wizard Site Maintenance page.

8. Enter the appropriate SQL Server name and instance (if applicable) for the new site database server as well as the site database name on the Configuration Manager Setup Wizard SQL Server Configuration page.

9. Configuration Manager Setup performs the SQL Server configuration process.

10. Restart the primary site server computer, and verify the site is functioning normally.

Note: If you also need to move the Software Update Services Database (SUSDB) you will need to stop IIS Admin Service, and Update Services Service and follow Steps 1 and 2 above to detach, then move the SUSDB.MDF and SUSDB.LDF files, then attach the SUSDB.MDF, in the new drive location or on the new SQL Server 2005.

Enjoy!

Clifton Hughes | Senior Support Engineer – System Center