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

Excel Spreadsheet opening in browser – “unable to process your request. Wait a few minutes and try performing this operation again”

If you ever have this issue or a similar one with Word.
Error

Try checking this folder in the 14-hive:
C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\XML

There are a couple of files to consider.

  • serverfiles.xml
  • serverfilesExcelServer.xml
  • serverfilesPerformancePoint.xml
  • serverfilesvisioserver.xml

For my issue. I had one server acting out (not behaving the same way as the other two. Somebody manually changed the content of the file ‘serverfilesExcelServer.xml’ and commented out the first Mapping element

 <ServerFiles>

     <!-- <Mapping FileExtension="xlsx" RedirectUrlTemplate="/_layouts/xlviewer.aspx?id=|0" NoGetRedirect="TRUE" CreateRedirectUrlTemplate="/_layouts/xlviewer.aspx?new=1"/> -->

    <Mapping FileExtension="xlsb" RedirectUrlTemplate="/_layouts/xlviewer.aspx?id=|0" NoGetRedirect="TRUE" CreateRedirectUrlTemplate="/_layouts/xlviewer.aspx?new=1"/>

    <Mapping FileExtension="xlsm" RedirectUrlTemplate="/_layouts/xlviewer.aspx?id=|0" NoGetRedirect="TRUE" CreateRedirectUrlTemplate="/_layouts/xlviewer.aspx?new=1"/>

</ServerFiles>

After I changed the first mapping for xlsx the three servers had the same behavior. This was a head-scratcher especially if some people go around not caring about supported changes and just make manual changes to 14-hive files. Shame on you, if you do!