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:

clip_image002

Current Content DB with 2 Site Collections

clip_image004

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: http://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