Upgrade your Internet Experience
Microsoft.com
Welcome Sign in
Windows SharePoint Services
Click to Rate and Give Feedback
TechNet
TechNet Library
Windows
Deployment
 Migrate content databases from Wind...
Migrate content databases from Windows Internal Database to an instance of SQL Server (Windows SharePoint Services 3.0)

Updated: 2008-07-15

You can move Windows SharePoint Services 3.0 content databases from Windows Internal Database to an instance of Microsoft SQL Server 2005. 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.

ImportantImportant:

This article covers moving content databases only and does not provide information for moving other databases that are associated with Windows SharePoint Services 3.0.

ImportantImportant:

If you detach and reattach a content database, be aware that the next time the content within that content database is crawled a full crawl will occur even if an incremental crawl has been requested. Because a full crawl re-crawls all content that the crawler encounters, regardless of whether that content has been previously crawled, full crawls can take significantly more time to complete than incremental crawls.

If you are running the Infrastructure Update for Windows SharePoint Services 3.0, the identifier (ID) of each content database is retained when you restore or reattach the database by using built-in tools. Default change log retention behavior when using built-in tools is as follows

  • The change logs for all databases are retained when you restore a farm.

  • The change log for a content database is retained when you reattach the database.

  • The change log for a content database is NOT retained when you restore just the content database.

When a database ID and change log are retained, Search continues crawling based on the regular schedule defined by crawl rules. When a change log is not retained, Search performs a full crawl during the next scheduled crawl.

For more information, see Move content databases (Windows SharePoint Services 3.0) and Protecting and restoring the farm (Windows SharePoint Services 3.0).

If you are restoring to a different farm you must make the database access account a member of the Administrators group on the database server during the restore process. This is required for the account to replicate the security setting for the databases. This access level can be removed after the restore process is complete.

Move content databases from Windows Internal Database to an instance of SQL Server

In the move process, you will use both Windows SharePoint Services 3.0 tools and Microsoft SQL Server 2005 tools. You can use the SharePoint Central Administration Web site or the Stsadm command-line tool. Steps for both are provided. The process involves the following phases:

  1. By using Windows SharePoint Services 3.0 tools, remove the content database from the Web application. Removing the content database does not delete the database; it only removes the association of the database with the Web application. This action is analogous to detaching a database in SQL Server in that the content of the database remains intact.

  2. Copy or move the .mdf and .ldf files from the source server to the destination server (if they are different servers).

  3. By using SQL Server 2005 tools, select the transferred files and attach the database to the destination instance of SQL Server.

  4. By using Windows SharePoint Services 3.0 tools, add the content database to the destination Web application. Be sure that you use exactly the same name to reattach the content database, or Windows SharePoint Services 3.0 will create a new database.

  5. By using Windows SharePoint Services 3.0 tools, perform a full crawl of the content in the newly reattached content database.

The following procedures provide steps for moving content databases by using Central Administration or the Stsadm command-line tool.

ImportantImportant:

Membership in the Administrators group on the local computers is required to complete this procedure. In addition, you must have at least the following roles in SQL Server 2005:

The db_owner fixed database role for the database on the source server running SQL Server 2005 or instance of SQL Server, to detach the database.

The dbcreater fixed server role on the destination server running SQL Server 2005 or instance of SQL Server, to attach the database.

Move content databases by using Central Administration

  1. In Central Administration, on the Application Management page, in the SharePoint Web Application Management section, click Content databases.

    NoteNote:

    Record the exact names of the content databases that you want to move. If you are moving or copying several content databases from more than one Web application, record which content database is associated with each Web application.

  2. On the Manage Content Databases page, click the content database that you want to move.

    NoteNote:

    If the content database does not appear, it might be associated with another Web application. To select another Web application, on the Web Application menu, click Change Web Application.

  3. On the Manage Content Database Settings page, in the Remove Content Database section, select the Remove content database check box, and then click OK.

    NoteNote:

    Removing the content database does not delete the database; it only removes the association of the database with the Web application.

  4. Repeat steps 2 and 3 for each content database that you want to move.

  5. In Windows Explorer, browse to the location of the .mdf and .ldf files for the content databases.

  6. Select the files and either copy or move them to the destination directory.

  7. In SQL Server 2005 Management Studio, open the destination instance of SQL Server.

  8. Right-click the Databases node, point to Tasks, and then click Attach.

  9. In the Attach Database dialog box, browse to the location to which you transferred the .mdf and .ldf files, select the .mdf file for the database you want to attach, and then click OK.

  10. Repeat steps 8 and 9 for each content database that you are moving.

  11. In Central Administration, on the Application Management page, in the SharePoint Web Application Management section, click Content databases.

  12. On the Manage Content Databases page, click Add a content database.

  13. On the Add Content Database page, in the Database Name box, type the exact name of the transferred content database, and then click OK.

  14. Repeat steps 12 and 13 for each database you are adding. Be sure that you select the correct Web application from the Web Application menu for each database.

  15. On the drive on which SharePoint Products and Technologies is installed, open a command prompt, and then change to the following directory: %COMMONPROGRAMFILES%\Microsoft shared\Web server extensions\12\Bin.

  16. Type the following command, and then press ENTER:

    Stsadm -o spsearch -action fullcrawlstart

    For more information about the Spsearch operation, see Spsearch: Stsadm operation (Windows SharePoint Services).

Move content databases by using the Stsadm command-line tool

  1. On the drive on which SharePoint Products and Technologies is installed, open a command prompt, and then change to the following directory: %COMMONPROGRAMFILES%\Microsoft shared\Web server extensions\12\Bin.

  2. Type the following command, and then press ENTER:

    stsadm -o deletecontentdb -url <URL name> -databasename <database name> [-databaseserver <database server name>]

    For more information about the deletecontentdb operation, see Deletecontentdb: Stsadm operation (Windows SharePoint Services).

    NoteNote:

    You must perform this operation for each content database that you want to move. This command only removes the association between the Web application and the content database; it does not actually delete the database.

  3. In Windows Explorer, browse to the location of the .mdf and .ldf files for the content databases.

  4. Select the files and either copy or move them to the destination directory.

  5. In SQL Server 2005 Management Studio, open the destination instance of SQL Server.

  6. Right-click the Databases node, point to Tasks, and then select Attach.

  7. In the Attach Database dialog box, browse to the location to which you transferred the .mdf and .ldf files, select the .mdf file for the database you want to attach, and then click OK.

  8. Repeat steps 6 and 7 for each content database that you are moving.

  9. On the drive on which SharePoint Products and Technologies is installed, open a command prompt, and then change to the following directory: %COMMONPROGRAMFILES%\Microsoft shared\Web server extensions\12\Bin.

  10. Type the following command, and then press ENTER:

    stsadm -o addcontentdb -url <URL name> -databasename <database name> [-databaseserver <database server name>]

    For more information about the addcontentdb operation, see Addcontentdb: Stsadm operation (Windows SharePoint Services).

    NoteNote:

    You must perform this command for each content database you want to move.

  11. Type the following command, and then press ENTER:

    stsadm -o spsearch -action fullcrawlstart

    For more information about the Spsearch operation, see Spsearch: Stsadm operation (Windows SharePoint Services).

See Also

Tags What's this?: Add a tag
Community Content   What is Community Content?
Add new content RSS  Annotations
Moving All SharePoint Databases from Windows Internal Database to SQL Server 2005      Abrarc   |   Edit   |   Show History

I have tried moving all the SharePoint databases (Not just Content Datbases) from windows internal database instance to SQL Server 2005 instance. However, when I use the cliconfg.exe tool to add TCP connection alias to windows internal database, I'm unable to connect to the database. I also get "Access Denied" error when I try to restore the ssp from the old database instance (#SSEE) to the new database instance(running SQL Server 2005) per the Technet article http://technet.microsoft.com/en-us/library/cc288554.aspx. Note that both instances are running on same phsyical sql server. I transferred all logins with proper roles from old to new instance. However, the problem still persists, and I've not had much luck. Not sure if this is supported sceanrio. I am interested in knowing other's experiences.

Be sure to detach the database first      Box293   |   Edit   |   Show History
Step 5 says: In Windows Explorer, browse to the location of the .mdf and .ldf files for the content databases.

Before doing this I had to detach the database using SQL Management Studio, this step seems to be overlooked here.
Add NT AUTHORITY\NETWORK SERVICE account as a sysadmin      Box293   |   Edit   |   Show History

When I went to re-attach the content database I received unknown error.

I needed to Add NT AUTHORITY\NETWORK SERVICE account as a sysadmin to the SQL 2005 server I was migrating too.

See http://support.microsoft.com/kb/888041 for help.

Tags What's this?: Add a tag
Flag as ContentBug
© 2009 Microsoft Corporation. All rights reserved. Terms of Use | Trademarks | Privacy Statement
Page view tracker