{"id":345,"date":"2011-09-02T03:10:37","date_gmt":"2011-09-02T03:10:37","guid":{"rendered":"http:\/\/www.stuartroberts.net\/?p=345"},"modified":"2011-09-06T05:17:45","modified_gmt":"2011-09-06T05:17:45","slug":"move-sharepoint-database","status":"publish","type":"post","link":"http:\/\/www.stuartroberts.net\/index.php\/2011\/09\/02\/move-sharepoint-database\/","title":{"rendered":"Move SharePoint Database"},"content":{"rendered":"<p>Before attempting to move the databases associated with your SharePoint instance, make sure you backup your farm before continuing.  For example, the following PowerShell script performs a full backup of the farm:<\/p>\n<p><em>Backup-SPFarm -Directory c:\\backuppath -BackupMethod Full<\/em><\/p>\n<p>Depending on the size of the databases, this may take some time to complete.<\/p>\n<p>One thing to check before starting the move is that the version of SQL is identical for both the source and target servers.<\/p>\n<p>Once your happy that SharePoint has been backed up, the first thing to do is stop all SharePoint and Internet Information Services (IIS) services on the server that is running the Central Administration Web site.  Do this by opening the Services snap-in from the Microsoft Management Console.<br \/>\n<!--more--><br \/>\n<div id=\"attachment_348\" style=\"width: 310px\" class=\"wp-caption alignnone\"><a href=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/2011-09-02_10-30-58.png\"><img aria-describedby=\"caption-attachment-348\" decoding=\"async\" loading=\"lazy\" src=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/2011-09-02_10-30-58-300x273.png\" alt=\"\" title=\"Services Snap-In\" width=\"300\" height=\"273\" class=\"size-medium wp-image-348\" srcset=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/2011-09-02_10-30-58-300x273.png 300w, http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/2011-09-02_10-30-58.png 381w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><p id=\"caption-attachment-348\" class=\"wp-caption-text\">Services Snap-In<\/p><\/div><\/p>\n<p>The following SharePoint services should be stopped:<\/p>\n<ul>\n<li>\nSharePoint 2010 Administration\n<\/li>\n<li>\nSharePoint 2010 Timer\n<\/li>\n<li>\nSharePoint 2010 Tracing\n<\/li>\n<li>\nSharePoint 2010 User Code Host\n<\/li>\n<li>\nSharePoint 2010 VSS Writer\n<\/li>\n<li>\nSharePoint 2010 Foundation Search V4\n<\/li>\n<li>\nSharePoint 2010 Server Search 14\n<\/li>\n<\/ul>\n<div id=\"attachment_353\" style=\"width: 219px\" class=\"wp-caption alignnone\"><a href=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/SharePoint-Services.png\"><img aria-describedby=\"caption-attachment-353\" decoding=\"async\" loading=\"lazy\" src=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/SharePoint-Services.png\" alt=\"\" title=\"SharePoint Services\" width=\"209\" height=\"118\" class=\"size-full wp-image-353\" \/><\/a><p id=\"caption-attachment-353\" class=\"wp-caption-text\">SharePoint Services<\/p><\/div>\n<p>Finally, stop the World Wide Web Publishing Service:<\/p>\n<div id=\"attachment_354\" style=\"width: 199px\" class=\"wp-caption alignnone\"><a href=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/Web-Service.png\"><img aria-describedby=\"caption-attachment-354\" decoding=\"async\" loading=\"lazy\" src=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/Web-Service.png\" alt=\"\" title=\"Web Service\" width=\"189\" height=\"18\" class=\"size-full wp-image-354\" \/><\/a><p id=\"caption-attachment-354\" class=\"wp-caption-text\">Web Service<\/p><\/div>\n<p>Next, type the following at a command prompt: <em>iisreset \/stop<\/em><\/p>\n<p>This will stop all running Internet Services on the server.<\/p>\n<p>Moving onto the database server, open the <em>Microsoft SQL Server Management Studio<\/em> and detach the SharePoint databases.<\/p>\n<p>By default, the following databases should have been created:<\/p>\n<ul>\n<li>Application_Registry_Service_DB_GUID<\/li>\n<li>Bdc_Service_DB_GUID<\/li>\n<li>Managed Metadata Service_GUID<\/li>\n<li>PerformancePoint Service Application_GUID<\/li>\n<li>Search_Service_Application_CrawlStoreDB_GUID<\/li>\n<li>Search_Service_Application_DB<\/li>\n<li>Search_Service_Application_PropertyStoreDB_GUID<\/li>\n<li>Secure_Store_Service_DB_GUID<\/li>\n<li>SharePoint_AdminContent_GUID<\/li>\n<li>SharePoint_Config<\/li>\n<li>StateService_GUID<\/li>\n<li>User Profile Service Application_ProfileDB_GUID<\/li>\n<li>User Profile Service Application_SocialDB_GUID<\/li>\n<li>User Profile Service Application_SyncDB_GUID<\/li>\n<li>WebAnalyticsServiceApplication_ReportingDB_GUID<\/li>\n<li>WebAnalyticsServiceApplication_StagingDB_GUID<\/li>\n<li>WordAutomationServices_GUID<\/li>\n<li>WSS_Content<\/li>\n<li>WSS_Logging<\/li>\n<\/ul>\n<p>To detach, right click each database and from the <em>Tasks<\/em> menu and click <em>Detach&#8230;<\/em><\/p>\n<div id=\"attachment_357\" style=\"width: 310px\" class=\"wp-caption alignnone\"><a href=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/Detach-Database.png\"><img aria-describedby=\"caption-attachment-357\" decoding=\"async\" loading=\"lazy\" src=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/Detach-Database-300x54.png\" alt=\"\" title=\"Detach Database\" width=\"300\" height=\"54\" class=\"size-medium wp-image-357\" srcset=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/Detach-Database-300x54.png 300w, http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/Detach-Database.png 726w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><p id=\"caption-attachment-357\" class=\"wp-caption-text\">Detach Database<\/p><\/div>\n<p>From the resulting dialog, click the <em>OK<\/em> button to detach the database.  You could, of course, write a SQL script to do all of this for you.<\/p>\n<p>Once all the relevant databases have been detached, the next step is to copy\\move the <em>mdf<\/em>, <em>log<\/em> and <em>ndf<\/em> files to the new SQL server.  The location of these files can easily be found by highlighting one of them (before you detached it) and looking at the <em>Primary File Path<\/em> value in the Object Explorer Details window, as shown below.<\/p>\n<div id=\"attachment_358\" style=\"width: 310px\" class=\"wp-caption alignnone\"><a href=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/Primary-File-Path.png\"><img aria-describedby=\"caption-attachment-358\" decoding=\"async\" loading=\"lazy\" src=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/Primary-File-Path-300x45.png\" alt=\"\" title=\"Primary File Path\" width=\"300\" height=\"45\" class=\"size-medium wp-image-358\" srcset=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/Primary-File-Path-300x45.png 300w, http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/Primary-File-Path.png 540w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><p id=\"caption-attachment-358\" class=\"wp-caption-text\">Primary File Path<\/p><\/div>\n<p>Once you&#8217;ve copied or moved the database files to the new server, the next step is to ensure that all of the SQL Server logins, fixed server roles, fixed database roles, and permissions for the databases from the source server are configured correctly on the new server.<\/p>\n<p>The following page from the Microsoft site describes the scripts that are required to achieve this: <a href=\"http:\/\/support.microsoft.com\/kb\/918992\/\"><\/p>\n<p>To attach the databases to the new server, connect to it via the SQL Server Management Studio, right click on the <em>Databases<\/em> node in the explorer window and select <em>Attach&#8230;<\/em><\/p>\n<div id=\"attachment_360\" style=\"width: 188px\" class=\"wp-caption alignnone\"><a href=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/Attach-Database.png\"><img aria-describedby=\"caption-attachment-360\" decoding=\"async\" loading=\"lazy\" src=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/Attach-Database.png\" alt=\"\" title=\"Attach Database\" width=\"178\" height=\"87\" class=\"size-full wp-image-360\" \/><\/a><p id=\"caption-attachment-360\" class=\"wp-caption-text\">Attach Database<\/p><\/div>\n<p>Next, click the <em>Add<\/em> button from the Attach Database dialog.<\/p>\n<div id=\"attachment_361\" style=\"width: 310px\" class=\"wp-caption alignnone\"><a href=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/Add-Database.png\"><img aria-describedby=\"caption-attachment-361\" decoding=\"async\" loading=\"lazy\" src=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/Add-Database-300x175.png\" alt=\"\" title=\"Add Database\" width=\"300\" height=\"175\" class=\"size-medium wp-image-361\" srcset=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/Add-Database-300x175.png 300w, http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/Add-Database.png 569w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><p id=\"caption-attachment-361\" class=\"wp-caption-text\">Add Database<\/p><\/div>\n<p>Then select a database file to attach from the list presented and click the <em>OK<\/em> button.<\/p>\n<div id=\"attachment_362\" style=\"width: 310px\" class=\"wp-caption alignnone\"><a href=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/Select-Database.png\"><img aria-describedby=\"caption-attachment-362\" decoding=\"async\" loading=\"lazy\" src=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/Select-Database-300x212.png\" alt=\"\" title=\"Select Database\" width=\"300\" height=\"212\" class=\"size-medium wp-image-362\" srcset=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/Select-Database-300x212.png 300w, http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/Select-Database.png 310w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><p id=\"caption-attachment-362\" class=\"wp-caption-text\">Select Database<\/p><\/div>\n<p>Finally, click the <em>OK<\/em> button in the <em>Attach Databases<\/em> dialog to complete the attach process.<\/p>\n<p>Repeat this for all databases that were detached and copied to the new SQL server.<\/p>\n<p>The next step is to use SQL connection aliases to update all Web servers.  To do this, open the <em>SQL Server Configuration Manager<\/em>.<\/p>\n<div id=\"attachment_364\" style=\"width: 257px\" class=\"wp-caption alignnone\"><a href=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/SQL-Server-Configuration-Manager.png\"><img aria-describedby=\"caption-attachment-364\" decoding=\"async\" loading=\"lazy\" src=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/SQL-Server-Configuration-Manager.png\" alt=\"\" title=\"SQL Server Configuration Manager\" width=\"247\" height=\"216\" class=\"size-full wp-image-364\" \/><\/a><p id=\"caption-attachment-364\" class=\"wp-caption-text\">SQL Server Configuration Manager<\/p><\/div>\n<p>From the application, expand the <em>SQL Native Client Configuration<\/em> node and from the <em>Aliases<\/em> node, right click and select <em>New Alias&#8230;<\/em><\/p>\n<div id=\"attachment_365\" style=\"width: 310px\" class=\"wp-caption alignnone\"><a href=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/New-Alias.png\"><img aria-describedby=\"caption-attachment-365\" decoding=\"async\" loading=\"lazy\" src=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/New-Alias-300x120.png\" alt=\"\" title=\"New Alias\" width=\"300\" height=\"120\" class=\"size-medium wp-image-365\" srcset=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/New-Alias-300x120.png 300w, http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/New-Alias.png 392w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><p id=\"caption-attachment-365\" class=\"wp-caption-text\">New Alias<\/p><\/div>\n<p>For the new alias, ensure you enter the source server instance name as the <em>Alias Name<\/em>, select TCP\/IP and enter the new server name as the <em>Server<\/em>.<\/p>\n<div id=\"attachment_368\" style=\"width: 310px\" class=\"wp-caption alignnone\"><a href=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/New-Alias-Details.png\"><img aria-describedby=\"caption-attachment-368\" decoding=\"async\" loading=\"lazy\" src=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/New-Alias-Details-300x111.png\" alt=\"\" title=\"New Alias Details\" width=\"300\" height=\"111\" class=\"size-medium wp-image-368\" srcset=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/New-Alias-Details-300x111.png 300w, http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/New-Alias-Details.png 386w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><p id=\"caption-attachment-368\" class=\"wp-caption-text\">New Alias Details<\/p><\/div>\n<p>Of course, you may not have <em>SQL Server Configuration Manager<\/em> installed on your WFE and application servers.  In this case, the following method will create a new SQL alias for you.<\/p>\n<p>Run the program <em>cliconfg.exe<\/em> located by default in C:\\Windows\\System32.<\/p>\n<div id=\"attachment_385\" style=\"width: 310px\" class=\"wp-caption alignnone\"><a href=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/AliasTab.png\"><img aria-describedby=\"caption-attachment-385\" decoding=\"async\" loading=\"lazy\" src=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/AliasTab-300x87.png\" alt=\"\" title=\"Alias Tab\" width=\"300\" height=\"87\" class=\"size-medium wp-image-385\" srcset=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/AliasTab-300x87.png 300w, http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/AliasTab.png 531w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><p id=\"caption-attachment-385\" class=\"wp-caption-text\">Alias Tab<\/p><\/div>\n<p>Switch to the Alias tab, as shown above and click the <em>Add<\/em> button.  Fill in the alias information in the same way as described above, then click <em>OK<\/em> twice to apply the alias.<\/p>\n<div id=\"attachment_386\" style=\"width: 310px\" class=\"wp-caption alignnone\"><a href=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/SaveServerAlias.png\"><img aria-describedby=\"caption-attachment-386\" decoding=\"async\" loading=\"lazy\" src=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/SaveServerAlias-300x154.png\" alt=\"\" title=\"Save Server Alias\" width=\"300\" height=\"154\" class=\"size-medium wp-image-386\" srcset=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/SaveServerAlias-300x154.png 300w, http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/SaveServerAlias.png 588w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><p id=\"caption-attachment-386\" class=\"wp-caption-text\">Save Server Alias<\/p><\/div>\n<p>Don&#8217;t know what port SQL is listening on?  Go to your SQL Server and open the <em>SQL Server Configuration Manager<\/em> application.  Click on the <em>SQL Server Services<\/em> node in the left hand tree and locate the instance being used by SharePoint.  Take a note of the Process ID for it.<\/p>\n<div id=\"attachment_387\" style=\"width: 310px\" class=\"wp-caption alignnone\"><a href=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/SQL_PID.png\"><img aria-describedby=\"caption-attachment-387\" decoding=\"async\" loading=\"lazy\" src=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/SQL_PID-300x40.png\" alt=\"\" title=\"SQL PID\" width=\"300\" height=\"40\" class=\"size-medium wp-image-387\" srcset=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/SQL_PID-300x40.png 300w, http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/SQL_PID.png 990w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><p id=\"caption-attachment-387\" class=\"wp-caption-text\">SQL Process ID<\/p><\/div>\n<p>Now open a command prompt and type the following: <em>netstat -ano | find \/i &#8220;1508&#8221;<\/em><\/p>\n<p>Replace the value 1508 with the PID from your SQL Server instance, keeping the double quotes.  From the output, you&#8217;ll see the port that&#8217;s being used.<\/p>\n<div id=\"attachment_388\" style=\"width: 310px\" class=\"wp-caption alignnone\"><a href=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/netstat-Output.png\"><img aria-describedby=\"caption-attachment-388\" decoding=\"async\" loading=\"lazy\" src=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/netstat-Output-300x18.png\" alt=\"\" title=\"netstat Output\" width=\"300\" height=\"18\" class=\"size-medium wp-image-388\" srcset=\"http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/netstat-Output-300x18.png 300w, http:\/\/www.stuartroberts.net\/wp-content\/uploads\/2011\/09\/netstat-Output.png 589w\" sizes=\"(max-width: 300px) 100vw, 300px\" \/><\/a><p id=\"caption-attachment-388\" class=\"wp-caption-text\">netstat Output<\/p><\/div>\n<p>Repeat this process on all Web and Application servers in the farm.<\/p>\n<p>The last step in this process is to restart IIS and the services that you stopped previously.  This involves doing the following:<\/p>\n<p>Type the following at a command prompt: <em>iisreset \/start<\/em> and then start the following Windows services.<\/p>\n<ul>\n<li>\nSharePoint 2010 Administration\n<\/li>\n<li>\nSharePoint 2010 Timer\n<\/li>\n<li>\nSharePoint 2010 Tracing\n<\/li>\n<li>\nSharePoint 2010 User Code Host\n<\/li>\n<li>\nSharePoint 2010 VSS Writer\n<\/li>\n<li>\nSharePoint 2010 Foundation Search V4\n<\/li>\n<li>\nSharePoint 2010 Server Search 14\n<\/li>\n<\/ul>\n<p>All going well, your SharePoint sites will come back up and will be using the new database location.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Before attempting to move the databases associated with your SharePoint instance, make sure you backup your farm before continuing. For example, the following PowerShell script performs a full backup of the farm: Backup-SPFarm -Directory c:\\backuppath -BackupMethod Full Depending on the &hellip; <a href=\"http:\/\/www.stuartroberts.net\/index.php\/2011\/09\/02\/move-sharepoint-database\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false,"jetpack_post_was_ever_published":false,"jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":[]},"categories":[3],"tags":[26,81,86],"jetpack_publicize_connections":[],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/plx2I-5z","_links":{"self":[{"href":"http:\/\/www.stuartroberts.net\/index.php\/wp-json\/wp\/v2\/posts\/345"}],"collection":[{"href":"http:\/\/www.stuartroberts.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.stuartroberts.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.stuartroberts.net\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.stuartroberts.net\/index.php\/wp-json\/wp\/v2\/comments?post=345"}],"version-history":[{"count":23,"href":"http:\/\/www.stuartroberts.net\/index.php\/wp-json\/wp\/v2\/posts\/345\/revisions"}],"predecessor-version":[{"id":384,"href":"http:\/\/www.stuartroberts.net\/index.php\/wp-json\/wp\/v2\/posts\/345\/revisions\/384"}],"wp:attachment":[{"href":"http:\/\/www.stuartroberts.net\/index.php\/wp-json\/wp\/v2\/media?parent=345"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.stuartroberts.net\/index.php\/wp-json\/wp\/v2\/categories?post=345"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.stuartroberts.net\/index.php\/wp-json\/wp\/v2\/tags?post=345"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}