Get rid of GUID at the end of the central administration content database

So for all of you, who have a database admin, who does not find the GUIDs at the end of databases cute and cuddly here is how to get rid of them for content databases, in this case specifically the central administration content database, which you have no control over what name is used when it is created, because SPConfig does this for you and it’s no secret that most of the time when MS is trying to help, it’s actually not that helpful.

So for standard content databases it’s quite straight forward.

1. Put the database in offline mode via central administration

2. Create a backup of the database via SQL Management Studio

3. Remove the Content Database from the Web Application via PowerShell

4. Restore the backup with a different database name via SQL Management Studio

5. Attach the new Content Database to the web application via PowerShell

Voilà!

Problem with the central administration is, that when you type ‘get-spwebapplication’ you do not get the central administration. You need to explicitly call

$ca = get-spwebapplication [central admin url].

Then you can get the content database(s) by doing the following:

$ca.ContentDatabases

With a fresh new farm you will get only one.

So there are three commands you will need to use, and at the bottom of this post I have a script that creates those three lines for you. Two of them are sql scripts, the last one is based on the following powershell method:

Mount-SPContentDatabase [-Name] <String> [-WebApplication] <SPWebApplicationPipeBind> [-AssignmentCollection
<SPAssignmentCollection>] [-AssignNewDatabaseId <SwitchParameter>] [-ChangeSyncKnowledge <SwitchParameter>]
[-ClearChangeLog <SwitchParameter>] [-Confirm [<SwitchParameter>]] [-DatabaseCredentials <PSCredential>]
[-DatabaseServer <String>] [-MaxSiteCount <Int32>] [-NoB2BSiteUpgrade <SwitchParameter>] [-SkipIntegrityChecks
<SwitchParameter>] [-WarningSiteCount <Int32>] [-WhatIf [<SwitchParameter>]] [<CommonParameters>]


if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction silentlyContinue) -eq $null) { Add-PSSnapin "Microsoft.SharePoint.PowerShell" }

$CentralAdminUrl = "http://dev:31";

$RestoreContentDatabaseAs = "WSS_Content_Admin";
$ca = get-spwebapplication $CentralAdminUrl

$ca.ContentDatabases | % {
$cdb = $_;
$name = $cdb.Name;
$restore = "RESTORE DATABASE [$RestoreContentDatabaseAs] FROM DISK = N'$name.bak' WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10 GO"
$backup = "BACKUP DATABASE [$name] TO DISK = N'$name.bak' WITH FORMAT, INIT, SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO"
$mount = "Mount-SPContentDatabase -Name $RestoreContentDatabaseAs -WebApplication $CentralAdminUrl"
Write $backup;
Write "";
Write $restore;
Write "";
Write $mount;
}


So all you have to do now is run the script above and make sure you put your content db offline and delete it from the web application as well as from sql between backup and restore.