Post
Ask Question

How to Dismount and Mount SharePoint 2010 Content Databases For SQL Server Move

Introduction

Consider a scenario where the drives in SQL Server are running out of space and the SQL Server is heavily used. So you decided to move some of the content databases to another SQL Server and attach it back to SharePoint. This can be done manually, but consider a case where you want to move hundreds of content databases. This article explains how to do it with a PowerShell script.

Planning to move Content databases

Before moving the content databases you must plan how to execute it. Find the activities at the following high level:

  • Capture content database details (the content databases to be moved)
  • Detach the content database from the SharePoint web application
  • Inform SQL admin to move the content database to the new SQL Server
  • Attach the content database to the SharePoint web application

Please use the following procedure to do the successful movement of content databases to another SQL Server and attaching them back to a SharePoint web app.

Step 1

Before the actual move of the content database, you must capture the details of the content database first. The following piece of code will help in capturing details like:

  • Content database name
  • Web application URL with which it is attached
  • Content database ID
  • Content database status
  • Database server
  • Total site count on the content database
  • Warning site count
  • Maximum site count
  1. $Output = $scriptBase + "\" + "CDBDetails.csv";  
  2. "CDBName" + "," + "WebAppURL" + "," + "CDBID" + "," + "CDBStatus" + "," + "DatabaseServer" + "," + "TotalSiteCount" + "," + "WarningSiteCount" + "," + "MaximumSiteCount"  | Out-File -Encoding Default -FilePath $Output;  
  3. foreach($DB in get-content "$scriptbase\DBDetails.csv")  
  4. {  
  5.     $CDB = get-spcontentdatabase $DB  
  6. $DB + "," + $CDB.webapplication.url + "," + $CDB.ID + "," + $CDB.Status + "," + $CDB.server + "," + $CDB.currentsitecount + "," + $CDB.WarningSiteCount + "," + $CDB.MaximumSiteCount | Out-File -Encoding Default  -Append -FilePath $Output;  
  7. } 

The preceding code requires an input file (DBDetails.csv) placed under the same location where the PowerShell script exists. The input file should have names of the content databases that need to be moved to a new SQL Server.

The script captures the preceding said information and export it to a CSV file (CDBDetails.csv). This output file will be used as an input file for another function.

Step 2

Now you have successfully captured the content databases info. The next step would be to detach the content databases from SharePoint.

The following piece of code helps in detaching the content databases from SharePoint:

  1. $ans = read-host "Do you want to continue with dismounting the CDB's ?(y/n) "  
  2. if($ans -eq 'y')  
  3. {  
  4. Write-host "!!!INFORMATION ------ GOING TO DISMOUNT THE CDB'S!!!"   
  5.     $csvfile1 = $scriptbase + "\" + "CDBDetails.csv"  
  6.     import-csv $csvfile1 | where {  
  7. write-host "Dismounting CDB" $_.CDBName " under SQL instance " $_.DatabaseServer -fore yellow  
  8.     Dismount-spcontentdatabase $_.CDBID  
  9.     write-host "Dismount completed" -fore green  
  10. }  
  11. }  
  12. else  
  13. {  
  14.     write-host "User choose to exit the script" -fore cyan  
  15.     exit;  
  16. } 

The preceding code uses the output file (CDBDetails.csv) that was generated in Step 1 as an input file. It uses the content database name to detach from SharePoint.

Step 3

Now the content databases are successfully detached from SharePoint. Inform the SQL DBA to move the content databases to the new SQL Server.

Step 4

Attach the content databases back to SharePoint once you get confirmation from the SQL DBA that Step 3 is completed.

The following piece of code helps you to attach the content database to SharePoint with the new SQL Server information:

  1. Function MountCDB([string]$CDBName, [string]$WebAppURL, [string]$MaximumSiteCount, [string]$WarningSiteCount)  
  2. {  
  3. Mount-SPContentDatabase $CDBName -DatabaseServer $SQLInstance -WebApplication $WebAppURL -MaxSiteCount $MaximumSiteCount -WarningSiteCount $WarningSiteCount  
  4. }  
  5. $global:SQLInstance = read-host "Specify the New SQL instance "  
  6. $objSQLConnection = New-Object System.Data.SqlClient.SqlConnection  
  7. $objSQLCommand = New-Object System.Data.SqlClient.SqlCommand  
  8. $objSQLConnection.ConnectionString = "Server=$SQLInstance;Integrated Security=SSPI;"  
  9. Write-Host "Trying to connect to SQL Server instance on $sqlinstance..." -NoNewline  
  10. $OpenConnection = $objSQLConnection.Open()  
  11. if($objSQLConnection.state -eq "open")  
  12. {  
  13. Write-host "!!!SUCCESSFULLY CONNECTED TO SQL SERVER!!!"           
  14. write-host "Mount CDB operation is under process" -fore yellow  
  15.     $csvfile = $scriptbase + "\" + "CDBDetails.csv"  
  16.     import-csv $csvfile | where {  
  17.     MountCDB $_.CDBName $_.WebAppURL $_.MaximumSiteCount $_.WarningSiteCount  
  18.     }  
  19.     write-host "Mount CDB operation completed successfully" -fore green  
  20. }  
  21. else  
  22. {  
  23. Write-host "!!!SQL CONNECTION FAILED OR YOU MUST HAVE ENTERED WRONG SQL SERVER DETAILS!!!"   
  24. } 

The preceding code gets input from the user of the new SQL Server name and it uses the output file from Step 1 as an input file to set the same settings for the content databases when adding it back to SharePoint.

Complete Code

  1. $LogTime = Get-Date -Format yyyy-MM-dd_hh-mm  
  2. $LogFile = ".\DBDismountAndMountPatch-$LogTime.rtf"  
  3. # Add SharePoint PowerShell Snapin  
  4. if ( (Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null ) {  
  5.     Add-PSSnapin Microsoft.SharePoint.Powershell  
  6. }  
  7. $scriptBase = split-path $SCRIPT:MyInvocation.MyCommand.Path -parent  
  8. Set-Location $scriptBase  
  9. #Deleting any .rtf files in the scriptbase location  
  10. $FindRTFFile = Get-ChildItem $scriptBase\*.* -include *.rtf  
  11. if($FindRTFFile)  
  12. {  
  13.     foreach($file in $FindRTFFile)  
  14.         {  
  15.             remove-item $file  
  16.         }  
  17. }  
  18. start-transcript $logfile  
  19. function Blink-Message  
  20. {  
  21.     param([String]$Message,[int]$Delay,[int]$Count,[ConsoleColor[]]$Colors)   
  22.     $startColor = [Console]::ForegroundColor  
  23.         $startLeft  = [Console]::CursorLeft  
  24.         $startTop   = [Console]::CursorTop  
  25.         $colorCount = $Colors.Length  
  26.         for($i = 0; $i -lt $Count; $i++)   
  27.     {  
  28.             [Console]::CursorLeft = $startLeft  
  29.             [Console]::CursorTop  = $startTop  
  30.             [Console]::ForegroundColor = $Colors[$($i % $colorCount)]  
  31.             [Console]::WriteLine($Message)  
  32.             Start-Sleep -Milliseconds $Delay  
  33.         }  
  34.         [Console]::ForegroundColor = $startColor  
  35. }  
  36. Function MountCDB([string]$CDBName, [string]$WebAppURL, [string]$MaximumSiteCount, [string]$WarningSiteCount)  
  37. {  
  38. Mount-SPContentDatabase $CDBName -DatabaseServer $SQLInstance -WebApplication $WebAppURL -MaxSiteCount $MaximumSiteCount -WarningSiteCount $WarningSiteCount  
  39. }   
  40. write-host "########################################################################################################" -fore cyan  
  41. write-host "Enter 1 to get the details of each and every CDB in the list" -fore green  
  42. write-host "Enter 2 to dismount the CDB's from sharepoint" -fore green  
  43. write-host "Enter 3 to mount the CDB's back to SharePoint" -fore green  
  44. write-host "########################################################################################################" -fore cyan  
  45. $option = read-host "Enter the option "  
  46. switch($option)  
  47. {  
  48.     1{  
  49.         $Output = $scriptBase + "\" + "CDBDetails.csv";  
  50. "CDBName" + "," + "WebAppURL" + "," + "CDBID" + "," + "CDBStatus" + "," + "DatabaseServer" + "," + "TotalSiteCount" + "," + "WarningSiteCount" + "," + "MaximumSiteCount"  | Out-File -Encoding Default -FilePath $Output;  
  51.         foreach($DB in get-content "$scriptbase\DBDetails.csv")  
  52.         {  
  53.             $CDB = get-spcontentdatabase $DB  
  54. $DB + "," + $CDB.webapplication.url + "," + $CDB.ID + "," + $CDB.Status + "," + $CDB.server + "," + $CDB.currentsitecount + "," + $CDB.WarningSiteCount + "," + $CDB.MaximumSiteCount | Out-File -Encoding Default  -Append -FilePath $Output;  
  55.         }  
  56.      }  
  57.     2{  
  58.         $ans = read-host "Do you want to continue with dismounting the CDB's ?(y/n) "  
  59.         if($ans -eq 'y')  
  60.         {  
  61. blink-message "!!!INFORMATION ------ GOING TO DISMOUNT THE CDB'S!!!" 250 20 Red, White, DarkRed, Green  
  62.             $csvfile1 = $scriptbase + "\" + "CDBDetails.csv"  
  63.             import-csv $csvfile1 | where {  
  64. write-host "Dismounting CDB" $_.CDBName " under SQL instance " $_.DatabaseServer -fore yellow  
  65.             Dismount-spcontentdatabase $_.CDBID  
  66.             write-host "Dismount completed" -fore green  
  67.             }  
  68.         }  
  69.         else  
  70.         {  
  71.             write-host "User choose to exit the script" -fore cyan  
  72.             exit;  
  73.         }  
  74.       }  
  75.   
  76.     3{  
  77.         $global:SQLInstance = read-host "Specify the New SQL instance "  
  78.         $objSQLConnection = New-Object System.Data.SqlClient.SqlConnection  
  79.         $objSQLCommand = New-Object System.Data.SqlClient.SqlCommand  
  80. $objSQLConnection.ConnectionString = "Server=$SQLInstance;Integrated Security=SSPI;"  
  81.         Write-Host "Trying to connect to SQL Server instance on $sqlinstance..." -NoNewline  
  82.         $OpenConnection = $objSQLConnection.Open()  
  83.         if($objSQLConnection.state -eq "open")  
  84.         {  
  85. blink-message "!!!SUCCESSFULLY CONNECTED TO SQL SERVER!!!" 250 20 yellow, White, green, cyan  
  86.             write-host "Mount CDB operation is under process" -fore yellow  
  87.             $csvfile = $scriptbase + "\" + "CDBDetails.csv"  
  88.             import-csv $csvfile | where {  
  89. MountCDB $_.CDBName $_.WebAppURL $_.MaximumSiteCount $_.WarningSiteCount  
  90.             }  
  91.             write-host "Mount CDB operation completed successfully" -fore green  
  92.         }  
  93.         else  
  94.         {  
  95. blink-message "!!!SQL CONNECTION FAILED OR YOU MUST HAVE ENTERED WRONG SQL SERVER DETAILS!!!" 250 20 yellow, White, green, cyan  
  96.         }  
  97.       }  
  98.       
  99. }  
  100. stop-transcript 

Execution Procedure

Step 1

The first step is to populate the input file (DBDetails.csv) with the list of content database to work with. Place the input file under the same location where the PowerShell script is placed. The input file should look as in the following:



Step 2

Launch the SharePoint management shell.

Step 3

Navigate to the path where the script is placed.

Step 4

Execute the PowerShell script.



The script gives you the following 3 options to choose from.

“1”: Capture the content database details before detaching it. The following details are captured:



“2”: Detach the content database from SharePoint.

“3”: Attach the content database back to SharePoint after the SQL Server move.

First you need to enter “1” to capture the details for the content database. This information will be used to attach the content database back to SharePoint with the same settings.

Second enter “2” to actually dismount the content databases from SharePoint.

Third enter “3” to attach the dismounted content database back to SharePoint once the SQL Server move is completed. This option prompts you to enter the new SQL Server details.

Conclusion

Thus in this article I have explained how to dismount and mount SharePoint content databases for a SQL Server move using a PowerShell script.


Similar Articles