Migrate content databases from WMSDE to Windows Internal Database

  • Article

Applies To: Windows SharePoint Services 3.0

 

Topic Last Modified: 2007-12-13

In this article:

  • Detach the databases from your WMSDE instance

  • Copy and attach the database files to SQL Server

  • Add the databases to the Web applications

  • Review the upgrade log files for any issues

  • Repeat the restore and add database procedures for all content databases

If you are using Windows SharePoint Services 2.0 in stand-alone mode with Microsoft SQL Server 2000 Desktop Engine (Windows) (WMSDE), it is still possible to upgrade by way of a database migration. First, you must create your new environment in stand-alone mode, complete with the Windows Internal Database. Windows Internal Database uses SQL Server technology as a relational data store for Windows roles and features only, such as Windows SharePoint Services, Active Directory Rights Management Services, UDDI Services, Windows Server Update Services, and Windows System Resources Manager.. For more information about creating the new environment, see Prepare the new Windows SharePoint Services 3.0 environment.

After you have the new environment installed, you can follow the steps below to first detach and then copy and attach the databases SQL Server. Then you add the databases to the Web application that will host the content. When you add the databases to the Web application, the upgrade process runs and upgrades the content databases to the new version.

Note

Before you detach your databases, be sure that you have run the pre-upgrade scan tool on the site content stored in the databases. For more information, see Run the pre-upgrade scan tool (Windows SharePoint Services).

Detach the databases from your WMSDE instance

Important

Take your environment offline to perform this process. With WMSDE databases, you do not have the option to mark the database read-only, as you do with SQL Server 2000 or Microsoft SQL Server 2005. If you do not take your environment offline, there is the risk that users will add content or change settings in their sites; if this occurs, you will not have those changes in your backups and, therefore, in your new environment.

  1. On the computer running Windows SharePoint Services, click Start, point to Run, type cmd, and then click OK.

    Note

    Be sure you are logged on as a member of the Administrators group on the local server.

  2. Type the following command and then press ENTER:

    Osql –S Servername\sharepoint -E

    This connects to the OSQL database management tool, with your SharePoint database instances specified.

  3. Type the following command and then press ENTER:

    EXEC sp_detach_db “Content_Database_name”

    where Content_Database_name is the name of your database, such as MSSharePoint.

  4. Type the following command and then press ENTER:

    Go

  5. If you get an error message such as "Database is in use and cannot be detached," use the following commands on the command line to pause and restart the database engine:

    net pause mssql$sharepoint

    net continue mssql$sharepoint

    For more information, see How to change the location of the Windows SharePoint Services database files (https://go.microsoft.com/fwlink/?LinkId=75398&clcid=0x409).

Repeat these steps for any additional content databases you may have. Do not perform these steps for the configuration database; you do not need the configuration database because you have created a new one in the new environment.

After you have detached the databases, you can copy and then attach the databases to your new environment. To perform these steps, you need to install the SQL Server 2005 Command Line Query Utility; you can download this tool from the Feature Pack for Microsoft SQL Server 2005 - April 2006 (https://go.microsoft.com/fwlink/?LinkID=72684&clcid=0x409). Install both the Microsoft SQL Server Native Client (sqlncli.msi, a pre-requisite for installing the command-line query utility) and the SQL Server 2005 Command Line Query Utility (SQLServer2005_SQLCMD.msi).

Copy and attach the database files to SQL Server

  1. Copy the database files for your content database (with extensions .mdf and .ldf) to the following location in your new environment:

    Drive:\WINDOWS\SYSMSI\SSEE\MSSQL.2005\MSSQL\Data

    where Drive is the drive where Windows SharePoint Services is installed.

  2. If you have not already done so, on the computer running Windows SharePoint Services 3.0, install the Microsoft SQL Server Native Client and the SQL Server 2005 Command Line Query Utility from the Feature Pack for Microsoft SQL Server 2005 - April 2006 (https://go.microsoft.com/fwlink/?LinkID=72684&clcid=0x409).

  3. On the computer running Windows SharePoint Services 3.0, click Start, point to Run, type cmd, and then click OK.

    Note

    Be sure you are logged on as a member of the Administrators group on the local server.

  4. Type the following command and then press ENTER to attach the databases that you copied:

    sqlcmd -S \\.\pipe\mssql$microsoft##ssee\sql\query -E

  5. Type the following command and then press ENTER:

    EXEC sp_attach_db @dbname = N'Content_Database_name', @filename1 = N'%WINDIR%\SYSMSI\SSEE\MSSQL.2005\MSSQL\Data\<dbname>.mdf', @filename2 = N'%WINDIR%\SYSMSI\SSEE\MSSQL.2005\MSSQL\Data \<dbname>_log.ldf’

  6. Type the following command and then press ENTER:

    Go

Add the databases to the Web applications

When you add the content databases, be sure that the root site for the Web application is included in the first content database you add. After you have added the database that contains the root site, you can add the other content databases for the Web application in any order. Be sure that you do not add any new site collections until you have restored all of the content databases.

You must use the command-line tool to add a content database to a Web application.

Note

You cannot add the same content database more than once to a farm, even on different Web applications. Each site collection in a content database has a globally unique identifier (GUID) associated with it, registered in the configuration database. Therefore, it is not possible to add the same site collection twice to the farm, even in separate Web applications. Although you can successfully attach the database in this situation, the site collection cannot be started. If you need a duplicate copy of a site collection in the same farm, first attach the database that contains the site collection to a separate farm, and then use the Stsadm.exe backup and restore operations to copy the site collection over to the other farm. The backup and restore process creates a new GUID for the site collection.

Add a content database to a Web application by using the command-line tool

To add a content database to a Web application, use the addcontentdb operation.

Important

If you are running Windows SharePoint Services 3.0 in host-header mode, you must perform an additional step to set a host header property before you attach the content databases. This step is not needed if you are running Windows SharePoint Services 3.0 with Service Pack 1 applied. On the command line, run the following command:
stsadm.exe -o setproperty -pn V2UsedHostHeaderMode -pv true
After you add the content databases, you must then set the property to false so the next content database operation is for databases that contain site collections that use managed paths (such as \sites):
stsadm.exe -o setproperty -pn V2UsedHostHeaderMode -pv false

  • To add a content database, on the command line, run the following command:

    stsadm -o addcontentdb -url URL [-databaseserver servername]

    –databasename databasename [-DatabaseUser username

    -DatabasePassword password] [-SiteWarning number]

    [-SiteMaximum number] [-SearchServer servername]

The following table explains the parameters for the addcontentdb operation.

Name Required/Optional Description

URL

Required

The URL for the Web application to which this database is being added.

DatabaseServer

Optional

The database server where the new database will be stored. The short version of this parameter is DS. If omitted, this parameter defaults to the name of the server from which you are running the command.

DatabaseName

Required

The name of the database you are creating. The short version of this parameter is DN.

DatabaseUser

Optional

The user account for SQL Server database creation. If you use this parameter, you must also specify the DatabasePassword parameter.

DatabasePassword

Optional (however, required if using DatabaseUser)

The password for the specified DatabaseUser account.

SiteWarning

Optional

The integer number of site collections to allow in this content database prior to generating a warning event in the Windows Event log.

SiteMaximum

Optional

The maximum number of site collections to allow in this content database.

SearchServer

Optional

The Search server to use for indexing content in this content database.

Review the upgrade log files for any issues

After you have attached the database and the upgrade process has completed, you can review the upgrade log file to see if there were any issues during upgrade. The upgrade log file and the trace log file are located at %ProgramFiles%\Common Files\Microsoft Shared\web server extensions\12\LOGS. The trace log is named in the following format: Machine_name-YYYYMMDD-HHMM.log, where YYYYMMDD is the date and HHMM is the time (for example, Server1-20061105-1241.log).

Repeat the restore and add database procedures for all content databases

Repeat these steps for any additional content databases you may have. After you have successfully restored and upgraded all of the content databases, you can review the sites to be sure they upgraded properly (for more information, see Review upgraded sites (Windows SharePoint Services)). Then follow the steps in the Perform post-upgrade steps for an in-place upgrade (Windows SharePoint Services) topic; because database migration is essentially an in-place upgrade as far as your content is concerned, you can use the same post-upgrade steps.

Download this book

This topic is included in the following downloadable book for easier reading and printing:

See the full list of available books at Downloadable books for Windows SharePoint Services.