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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: