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:

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:

  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. }
  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. }
  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.