Automating SharePoint 2010 Administration with PowerShell: Renaming the Admin Content DB

Article
01/20/2013

If you don’t like the GUID that trails the Central Admin Content DB, you have 2 options, create the Farm with PowerShell / scripting and create the databases with names that you like OR if you already have a Farm, you can use the scripts below to change the Admin Content (Central Admin Content Database). Needless to say during this operation your Central Admin site is not accessible. These scripts also call IISRESET and hence plan for downtime.

There are 2 examples of this script, both are 100% automated. The only thing the Script expects the User to provide is the name of the New Content Database. I would love to automate it with Read-YourMind cmdlet… but let’s leave it for now J.

Method 1 – Using Move-SPSite cmd-let.

What we are doing here:

1. Getting the Central Admin URL and the New Content DB Name from the User

2. Populating the variables we need to perform the operation like the Current Admin Content DB Name, SQL Server Name and the Database ID

3. Creating a New Content Database and Attaching it to the Central Admin Web Application

4. Calling SP-MoveSite to Move 2 sites from Old Content Database to the New one

5. Lastly, we ask the user if he wants to Detach the Content DB from the web application or completely delete it from SQL

The operation can be screen in the below screenshots:

Current Content DB with 2 Site Collections

New Content DB Attached to the same Web App and Site Collections have been moved to the New Content DB

This is the Script. For convenience, you can also download the zip file attached to this post. The zip file contains 2 scripts RenameAdminContentDB_Method1.PS1 (Move-SPSite Method) and RenameAdminContentDB_Method1.PS1 (Backup – restore methos described later in the post.)

  1: Clear-Host
  2: ############################################################################################################################ DO NOT EDIT ANYTHING BELOW THIS LINE ###########################
  3: #############################################################################################
  4: # Start Loading SharePoint Snap-in
  5: $snapin = (Get-PSSnapin -name Microsoft.SharePoint.PowerShell -EA SilentlyContinue)
  6: IF ($snapin -ne $null){write-host -f Green "SharePoint Snapin is loaded... No Action taken"}
  7: ELSE  {write-host -f Yellow "SharePoint Snapin not found... Loading now"
  8: Add-PSSnapin Microsoft.SharePoint.PowerShell
  9: write-host -f Green "SharePoint Snapin is now loaded"}
  10: # END Loading SharePoint Snapin
  11: #Detect CA Site URL 
  12: $CA = (Get-SPWebApplication -IncludeCentralAdministration | where {$_.DisplayName -eq "SharePoint Central Administration v4"}).Url
  13: #Get New Content DB Name from User
  14: $NewContentDB = Read-Host "Enter the New Content Database Name for your Central Admin Site"
  15: IF ($NewContentDB -eq ''){Write-Host -f Red "Blank value for Content DB. Cannot proceed. Exiting..."
  16: break}
  17: # grab SQL Server Name
  18: $SQLServer = (Get-SPContentDatabase -WebApplication $CA -EA Stop).Server
  19: $OldDB = (Get-SPContentDatabase -WebApplication $CA -EA Stop).DB
  20: $OldDBID = (Get-SPContentDatabase -WebApplication $CA -EA Stop).ID
  21: #Create new SP_contentDB
  22: Write-Host -f Green "Creating Content DB..."
  23: New-SPContentDatabase -Name $NewContentDB -WebApplication $CA -DatabaseServer $SQLServer -ErrorAction STOP
  24: Write-Host -f Green "New Content DB Created and Attached to your Central Admin Site"
  25: #Grab ID of the New Content DB
  26: $NewContentDBID = (Get-SPContentDatabase -WebApplication $CA | ?{$_.Name -eq $NewContentDB}).id
  27: #Get and Move SP-Site
  28: Get-SPSite -ContentDatabase $OldDBID | Move-SPSite -DestinationDatabase $NewContentDBID -Confirm:$False -EA Stop
  29: Write-Host -f Green "Site Move complete."
  30: Write-Host -f Green "Resetting IIS now..."
  31: IISRESET
  32: $RemoveOption = Read-Host "
  33: Choose Remove Database Option:
  34: 1 - Remove (DELETES the Database from SQL)
  35: 2 - Dismount (Detach the DB from Web Application)"
  36: IF ($RemoveOption -eq '1') {Remove-SPContentDatabase -Identity $OldDBID -EA Stop
  37: Write-Host -F Green "Database deleted from SQL Server."}
  38: ElseIF ($RemoveOption -eq '2') {Dismount-SPContentDatabase -Identity $OldDBID -Confirm:$False -EA Stop
  39: Write-Host -F Yellow "Database detached from SharePoint Web Application. Please delete the Database manually from SQL Server."}
  40: Else {Write-Host -f Yellow "Invalid Option chosen. No action was taken."}
  41: Write-Host -F Green "All Opearation Completed Successfully."
  42: ################### END SCRIPT ###########################

Method 2 – Using SQL Backup and Restore.

This method is primary for demonstration purposes. It shows how you can call SQL Commands and SMO (SQL Server Management Objects) directly from PowerShell along with SharePoint. I will use the same method in future posts on automating tasks for Database mirroring. Though this script can be used for any content database, I have tested this with the Central Admin Content Database only. Also, the script below you will see that when we restore the database, we are getting the LogicalName of the SQL MDF and LDF File from the backup set (your original SharePoint_AdminContent_GUID). Though it’s possible to fire a Alter Database Statement to change the names, running Alter Database statement on SharePoint Databases are not supported and you should refrain from doing that.

This script also has the following prerequisite:

To use this script you need SQL Server Client Components to be installed on the Server and you need the required permissions for backup / restore on SQL. It’s also possible to run the SQL Section of the script on the SQL Box, or put it on the SQL Box as a ps1 file and call it form the script below with –session parameter if you do not have SQL Server Client Components installed on the server.

This script is fully automated and performs the following steps:

Loads SharePoint and SQL Snap-in
Detects the Central Admin Site URL
Populates the required variables like SQL Server Name, Database Name
Loads SQL Assemblies to get SQL Server Defaults for Backup Location and MDF / LDF File Locations
Constructs a new Backup File name with TimeStamp
Creates the Backup Statement and stores it in a String
Invokes SQLCmd to execute the backup statement – this first runs a full backup and then a Transaction Log backup so that we don’t lose the Tail of the Log (required for all databases with FULL Recovery Model)
Enters Restore Operation block
Grabs the LogicalName of the MDF and LDF File from the BackupSet through SQLSMO.Restore object
Gets the Name of the New Content DB from the User
Generates the Restore Statement
Invokes SQLCmd to execute the Restore statement – First the Full backup with NORECOVERY option and then the Transaction Log File
Comes back to SharePoint Loop – First step is to dismount the Old Content DB
Mounts the new Content DB to the Web App
Runs IISRESET
Provides a remove option to the user – if user selects that option, the database is permanently deleted.

  1: Clear-Host
  2: ############################################################################################################################ DO NOT EDIT ANYTHING BELOW THIS LINE ###########################
  3: #############################################################################################
  4: # Start Loading SharePoint Snap-in
  5: $snapin = (Get-PSSnapin -name Microsoft.SharePoint.PowerShell -EA SilentlyContinue)
  6: IF ($snapin -ne $null){write-host -f Green "SharePoint Snapin is loaded... No Action taken"}
  7: ELSE  {write-host -f Yellow "SharePoint Snapin not found... Loading now"
  8: Add-PSSnapin Microsoft.SharePoint.PowerShell
  9: write-host -f Green "SharePoint Snapin is now loaded"}
  10: # END Loading SharePoint Snapin
  11: # Start Loading SQL Snap-in
  12: ## Check to see if SQL Server Provider for Windows PowerShell is installed 
  13: # Add the SQL Server Provider. Ref: https://technet.microsoft.com/en-us/library/cc281962.aspx 
  14: $ErrorActionPreference = "Stop"
  15: $sqlpsreg="HKLM:\SOFTWARE\Microsoft\PowerShell\1\ShellIds\Microsoft.SqlServer.Management.PowerShell.sqlps"
  16: if (Get-ChildItem $sqlpsreg -ErrorAction "SilentlyContinue")
  17: {    throw "SQL Server Provider for Windows PowerShell is not installed. Please install Client Components first."}
  18: Else{    $item = Get-ItemProperty $sqlpsreg
  19:     $sqlpsPath = [System.IO.Path]::GetDirectoryName($item.Path)}
  20: $snapin = (Get-PSSnapin -name SqlServerCmdletSnapin100  -EA SilentlyContinue)
  21: IF ($snapin -ne $null){write-host -f Green "SQL Snapin is loaded... No Action taken"}
  22: ELSE  {write-host -f Yellow "SQL Snapin not found... Loading now"
  23: Add-PSSnapin SqlServerCmdletSnapin100
  24: write-host -f Green "SQL Snapin is now loaded"}
  25: # END Loading SQL Snap-in
  26: ######################### START SharePoint Loop ##################################
  27: # Detect CA Site
  28: $CA = (Get-SPWebApplication -IncludeCentralAdministration | where {$_.DisplayName -eq "SharePoint Central Administration v4"}).Url
  29: # Get Content DB Name
  30: $CACurrentdb  = (Get-SPContentDatabase -WebApplication $CA).Name
  31: $SQLServer = (Get-SPContentDatabase -WebApplication $CA).Server
  32: Write-Host -f Green "Central Admin Content DB Name: " $CACurrentdb
  33: Write-Host -f Green "Detected SQL Server: " $SQLServer
  34: Write-Host -f Green " Entering SQL Loop Now..."
  35: ######################### END SharePoint Loop ##################################
  36: ######################### START SQL Loop ##################################
  37: [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO")  | out-null
  38: [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMOExtended")  | out-null
  39: [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
  40: [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null
  41: #get server defaults for Backup, MDF and LDF Locations
  42: $smosql=New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlserver
  43: $ServerDefaultBackupLocation = $smosql.Settings.BackupDirectory 
  44: Write-Host -f green "Captured Default Backup Directory: " $ServerDefaultBackupLocation
  45: $GetMDFFileLocation = $smosql.MasterDBPath
  46: $GetLDFFileLocation = $smosql.MasterDBLogPath
  47: $timestamp = Get-Date -format yyyyMMddHHmmss
  48: # Construct a new Backup File name to avoid any conflics
  49: $bkSetName = $ServerDefaultBackupLocation +"\CA_Backup_DB_"+$timestamp +  ".bak"
  50: # Creating Backup Statement for Database and Transaction Log Backup
  51: [String] $bkupstmt ="BACKUP DATABASE [" + $CACurrentdb + "]
  52: TO  DISK = N'" + $bkSetName + "' WITH NOFORMAT, NOINIT, NAME = N'SharePoint_AdminContent_BackUp', SKIP, NOREWIND, NOUNLOAD, STATS = 10
  53: GO
  54: BACKUP LOG [" + $CACurrentdb + "] 
  55: TO  DISK = N'" + $bkSetName + "' WITH NOFORMAT, NOINIT, NAME = N'SharePoint_AdminContent_BackUp', SKIP, NOREWIND, NOUNLOAD, STATS = 10
  56: GO"
  57: # Firing the SQL Query
  58: Invoke-Sqlcmd -Query $bkupstmt -ServerInstance $sqlserver
  59: Write-Host -F Green "Backup Completed Successfully"
  60: # Start Restore Operation
  61: #Grab Logical File Names from Backup Set
  62: $res = new-object Microsoft.SqlServer.Management.Smo.Restore
  63: $res.Devices.AddDevice($bkSetName, [Microsoft.SqlServer.Management.Smo.DeviceType]::File)
  64: $LogicalDataFileName = $res.ReadFileList($smosql).Rows[0]["LogicalName"]
  65: $LogicalLogFileName = $res.ReadFileList($smosql).Rows[1]["LogicalName"]
  66: #Get New Content DB Name from User
  67: $NewContentDBName = Read-Host "Enter the name of the New Content Database "
  68: # Start Restore Operation... Create the Query
  69: [String] $restoreStmt = "
  70: RESTORE DATABASE [" + $NewContentDBName + "] FROM DISK = N'"+ $bkSetName+"' WITH FILE = 1, MOVE N'" + $LogicalDataFileName + "' 
  71: TO N'"+ $GetMDFFileLocation +"\"+ $NewContentDBName + ".mdf', MOVE N'"+ $LogicalLogFileName + "' 
  72: TO N'" + $GetLDFFileLocation +"\" + $NewContentDBName + "_log.LDF', NORECOVERY, NOUNLOAD, STATS = 10
  73: GO
  74: RESTORE LOG ["+ $NewContentDBName +"] FROM DISK = N'"+ $bkSetName +"' WITH FILE = 2, NOUNLOAD, STATS = 10
  75: GO"
  76: # Fire the SQL Query
  77: Invoke-Sqlcmd -Query $restoreStmt -ServerInstance $SQLServer
  78: Write-Host -f Green "Retore Completed Successfully... "
  79: # END SQL Loop
  80: # Back to SP Loop to attach the new content DB to Central Admin Site
  81: # First - Dismounting DB
  82: Dismount-SPContentDatabase -Identity (Get-SPContentDatabase -WebApplication $CA) -Confirm:$false
  83: Mount-SPContentDatabase $NewContentDBName -DatabaseServer $SQLServer -WebApplication $CA
  84: Write-Host -f green "New Content Database Attached to Central Admin Site.
  85: "
  86: # reset IIS
  87: Write-Host -f green "Running IISRESET now..."
  88: iisreset
  89: $RemoveOption = Read-Host "
  90: Choose Remove Database Option:
  91: 1 - Remove (DELETES the Database from SQL)
  92: 2 - Exit (Do Nothing)"
  93: IF ($RemoveOption -eq '1') { 
  94: [String] $DropDB = "
  95: ALTER DATABASE ["+ $CACurrentdb + "]
  96: SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
  97: DROP DataBase [" + $CACurrentdb+ "]"
  98: Invoke-Sqlcmd -Query $DropDB -ServerInstance $SQLServer
  99: Write-Host -F green "Database Removed from SQL"}
  100: Else {Write-Host -f Yellow "Exiting Remove Operation... Database was not Dropped from SQL Server."}
  101: Write-Host -F Green "All Opearation Completed Successfully."
  102: #################### END SCRIPT ####################

Cheers Smile

Priyo

Scripts.zip

Automating SharePoint 2010 Administration with PowerShell: Renaming the Admin Content DB

Additional resources