Remove SharePoint 2010 Databases

I’ve installed and uninstalled SharePoint many times on my VMs and in the lab.  Problem with uninstalling SharePoint 2010 is that it leaves behind it’s many databases in SQL Server.  So not to create a mess when I reinstall, I have a SQL Query I run to do a cleanup by first running the below query which loops tough all the non-system databases in SQL server to generate a second query which I’ll run to remove SharePoint databases from previous install.

Query 1:

set nocount on
declare @dbname as varchar(80)
declare @server_name as varchar(20)
select @server_name = @@servername
declare rs_cursor CURSOR for select name from master.dbo.sysdatabases where name not in ('model','master','msdb','tempdb','alert_db','mssecurity','Adventure*')
open rs_cursor
Fetch next from rs_cursor into @dbname
IF @@FETCH_STATUS <> 0
   PRINT 'No database to backup...Please check your script!!!'
WHILE @@FETCH_STATUS = 0 
BEGIN
  print 'DROP DATABASE [' +  @dbname + ']'
  print 'go'
  print 'print ''Drop of ' + upper(@dbname) + ' database successfully completed'''
  print 'go'
  PRINT ' '
  FETCH NEXT FROM rs_cursor INTO @dbname
END
CLOSE rs_cursor
deallocate rs_cursor
print ' '
print 'print ''SERVER NAME : ' + upper(@server_name) + '-->  All databases successfully dropped'''

The output of this query gives me a second cleanup query in the SQL Server Management studio output screen (shown below).

 

Simply copying the output from the first query into a new query window and executing now drops all the dbs.  Go into the folders where the .mdf and .ldf files resided and verify everything is gone.  You now have a clean slate in SQL for fresh install of SharePoint.

SharePoint Server 2010 Search Folder Permissions

After initial configuration of SharePoint Server 2010 search, queries to indexed content failed with error Internal server error exception…Correlation ID.

It was discovered through ULS trace logs that the Search Service account could not access the windows TEMP directory. To resolve, environment variable for the Windows TEMP directory was switched to D:\TEMP and the search service account was given modify permissions to this folder.

SharePoint 2010 Userprofile Import and NetBIOS

If your NetBIOS and domain names are different, Replicate Directory Changes permission for your user profile synchronization service account is also needed on the cn=configuration container (google it if your not sure where to change). In environments where both items are same, this permission is only required at the Domain level.

Also, before importing user profiles, NetBIOS domain names have to be enabled on the User Profile service application.

To "enable" NetBIOS domain names once a connection is created requires the connection to be deleted and a new connection created with the flag turned on (set to 1 or true).

How To: Enable import of NetBIOS Domain Names:

Using the 2010 SharePoint Management Shell, enter following commands:

Get-SPServiceApplication (lists the Service Applications and their GUIDs)
$UPA = Get-SPServiceApplication –Id <GUID of User Profile Service Application>
$UPA.NetBIOSDomainNamesEnabled=1
$UPA.Update()

See more information regarding this issue at http://social.msdn.microsoft.com/Forums/en-US/sharepoint2010general/thread/398f3553-5de7-456b-b935-4e22cee26b2f

Quick Switch to Edit Mode in SPS 2010

Problem: No easy way to switch to Edit Mode to add a web part.  Current Steps of going to Site Actions and Selecting Edit Page to get to edit mode where you can then click 'Add a Web Part' is not intuitive.

Solution: We can at least make it easier to switch to edit mode by throwing a button on the page only visible to Site Owners.  There is a standard JavaScript function (ChangeLayoutMode()) which can be associated to button.

Achieved this result via a minimal code method as follows:

• Using SharePoint JavaScript functions to make this work (you can discover other JavaScript functions available by viewing source on the SharePoint page).
• Added below source HTML with minimal styling to file called editModeWP.txt and uploaded file to a document library on my SharePoint site. 
• HTML source:

<style type=text/css>
input.xyzBlue {background-color: #51b5e0; font-weight: bold; font-size: 12px; color: white;}
</style>
<input class="xyzBlue" type=BUTTON value="Switch to Edit Mode" name="wpMode" onClick="window.location = 'javascript:ChangeLayoutMode(false);';">

• Added "Content Editor" web part to home page and set it's "Content Link" to the location of the txt file saved above.  Set "Chrome Type" to none.  Set "Target Audience" to "Owners" (owners should be the only ones that see this on page, if all users see it, it will be useless to them when they click because they don't have correct permissions to edit).

Enhancements:

• I approached this using minimal development path, however, if this is something you want consistent throughout you environments, it will require adding the updated code to many of your page layouts.  You could also add logic to this current method switch the button name and onclick event to the JavaScript which stops editing the page.

My Site Branding in SharePoint 2010

I found a nice post on branding My Sites in SharePoint Server 2010 here.  I took it next step and have provided the source code for a feature staple to update the global navigation for branding your SPS 2010 My Sites.

This solution contains sample branding to be implemented to the My Sites host template and personal site template.   This branding is simply implemented for sample purposes and is not a full-fledge, tested, production-ready branded interface.

It was developed as a Feature Staple packaged in Visual Studio 2010.   The Global navigation utilized in the My Sites templates loads from a User Control which cannot be modified via master page updates in SharePoint Designer – thus the feature staple.

The feature staple solution for this customization was built with following components:

• New user control (XYZMysiteNav.ascx).  This user control is copied from existing GlobalNavigation user control.  This new control adds reference to a new CSS file and three new <div> tags.  The new <div> tags are used to accept placement of the header consisting of three images.  New control deployed to web server folder C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\CONTROLTEMPLATES\xyzmysites.
• A new feature (XYZ.MySiteFeatureStaple_XYZ_MySiteBrand).  This feature binds the new user control to the existing user control and is deployed to C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\FEATURES\XYZ.MySiteFeatureStaple_XYZ_MySiteBrand.
• Custom CSS (xyz_mysites.css):  This CSS file contains updated styling for the new <div> tags and updates to positioning, colors, and fonts for existing SharePoint IDs and Classes.  This CSS is mapped to folder C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\LAYOUTS\1033\STYLES for deployment.
• Custom images:  A variety of images used for header, logo, background, and webpart titles referred to in above CSS file.  The images are mapped to folder C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\IMAGES\xyzpilot for deployment.
• Feature Staple (XYZ.MySiteFeatureStaple_XYZ_MySiteStaple).  Feature staple is used to staple the feature mentioned above (XYZ.MySiteFeatureStaple_XYZ_MySiteBrand ) to the Personal Site (SPSPERS) and the My Sites Host (SPSMSITEHOST) site templates and is deployed to C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\FEATURES\XYZ.MySiteFeatureStaple_XYZ_MySiteStaple

The above solution is packaged into a SharePoint solution file (XYZ.MySiteFeatureStaple.wsp – grab from debug folder if you want to deploy to see what it does) and deployed to the web application hosting my sites where it is activated on the My Site Host site collection.

Through feature stapling, the feature is automatically enabled on new personal sites which are created but for all existing sites, the feature has to be activated by running the script below from the SharePoint PowerShell Command prompt on the WFE server (brandpersonal.ps1 powershell script can also be found at C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\TEMPLATE\CONTROLTEMPLATES\xyzmysites):

$personalSites = get-spsite | where {$_.RootWeb.WebTemplate -eq "SPSPERS"} 
    
foreach ($site in $personalSites) {Enable-SPFeature -Identity "MyNewNavFeature" -Url $site.Url}

Download source code from this link.

Remove and Add Back Multiple SharePoint Content Databases with PowerShell

The TechNet documentation for deployment of software updates for SharePoint Server 2007 suggests, among other things, to detach your content databases in order to reduce downtime. This is easy enough to do through Central Admin when you have a few databases and a small number of web applications, but how about in a large farm with many web applications and dozens of content databases with multiple SQL Server Instances? PowerShell to the rescue!

First: Remove/Detach the Databases – Create a CSV file with value headings webapp and url where webapp is friendly name and url is the associated url for the web application. CSV example below:

webapp,url

webApp1,http://webapp1.xyz.com

webApp2,http://webapp2.xyz.com

Use below PowerShell script to loop through all of your web applications using above CSV file, enumerate the content databases via STSADM, and throw the content database name and SQL Server Server/Instance name to a new CSV file named by webapp value. An internal loop reads from the new CSV file and detaches the databases (I commented line out below – uncomment when ready to run).

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")

#loop through a csv file that contains webapp and url, run enumcontentdbs, export results to csv  
Import-Csv .\webapps.csv | ForEach {   
    $strWA = $_.webapp  

    $strURL = $_.url 

    $rawdata=stsadm -o enumcontentdbs -url $strURL 

    $sitesxml=[XML]$rawdata 

    $sitesxml.databases.contentdatabase | Select-Object server,name | export-csv .\$strWA.csv -noType 

    #loop through contentDBs from csv created in above line and delete 

    Import-Csv .\$strWA.csv | ForEach { 

        $strServer = $_.Server 

        $strName = $_.Name 

        write-output WebApp: $strURL ContentDB: $strName 

        #WARNING BELOW COMMAND DANGEROUS -- UNCOMMENT WHEN READY -- WILL REMOVE EVERY CONTENT DB ON FARM 

        #$delDB = stsadm.exe -o deletecontentdb -url $strURL -databasename $strName 

    } 

}

Second: Add the Databases back – After you’ve run all of your updates, you need to add your content databases back. Use below PowerShell script to loop through all of your web applications and read in the values from the CSV files created by running previous script to add the content databases back.

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint") 

#loop through webapps from original csv -- same as remove script
Import-Csv .\webapps.csv | ForEach {

    $strWA = $_.webapp 

    $strURL = $_.url 

    $rawdata=stsadm -o enumcontentdbs -url $strURL 

    #loop through contentDBs and add based on csv created in the webAppDbs_Remove.ps1

    Import-Csv .\$strWA.csv | ForEach {

        $strServer = $_.Server 

        $strName = $_.Name 

        #write-output WebApp: $strURL ContentDB: $strName

        $AddDB = stsadm.exe -o addcontentdb -url $strURL -databasename $strName -databaseserver $strServer -sitewarning 450 -sitemax 500
    } 

}

Add the scipts in appropriately named powershell ps1 files (e.g., webAppDBs_Remove.ps1 and webAppDBs_Add.ps1) on an Application or Web Front End Server where Powershell is installed and call from a .bat file with commands similar to below:

powershell.exe Set-ExecutionPolicy RemoteSigned

REM command to run script from the ps1 file to remove

powershell.exe -noexit .\webAppDBs_Remove.ps1

REM command to run script from the ps1 file to add back

REM powershell.exe -noexit .\webAppDBs_Add.ps1

Handy VBScript for STSADM looping

Before getting into PowerShell, I used to do a lot of administrative scripting for SharePoint using VBScript.  In fact we still use a vbscript based automated farm installation that a consultant assembled for us (Thanks V-man!) – I intend on switching over to powershell in future.

The vbscripts are tried-and-true though.  Here’s a little one I like to use when I need to loop through an stsadm command multiple times.  I use it for things like adding multiple managed paths, renaming sites to clear orphans, and running database repair to clear up list and document library orphans on multiple databases, etc.

'script to add managed paths based on values in paths.csv file stored on same drive as script
'paths.csv file entry example:  
'webappname.domain.com,/test1
'webappname.domain.com,/test2
'webappname2.domain.com,/test1
'webappname2.domain.com,/test2
Const ForReading = 1

Const cStsadmPath = """C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN\stsadm.exe""" 
Dim retval  
Dim objshell 
Dim cmd  
Set objFSO = CreateObject("Scripting.FileSystemObject") 
Set objTextFile = objFSO.OpenTextFile(".\paths.csv", ForReading) 
Do Until objTextFile.AtEndOfStream 
    strNextLine = objTextFile.Readline 
    arrPath = Split(strNextLine, ",") 
    Set objShell = CreateObject("WScript.Shell")
 
    'grab webapp from CSV which is the 1st value and the managed path from 2nd value in the base 0 array to run addpath command

    cmd = cStsadmPath & " -o addpath -url http://" & arrPath(0) & arrPath(1) & " -type wildcardinclusion"

    retval = objShell.run(cmd, 1, True)  
    Wscript.Echo cmd


    set retval = objShell.exec(cmd) 
    'Wait until command is done running before running next command

    Do Until retval.Status

          Wscript.Sleep 250

    Loop 


    set retval = nothing

    Set objShell = Nothing    
Loop

I have a script that enumerates the sites and throws the output to an xml file – and then the vbsscipt uses the xmldom to read in values but the output gets corrupted (proabably because I’m enumerating close to 10,000 site collections) and can’t reliably be read.  I intend on using Powershell and getting the sites through the SharePoint Object model – look for that in a future post.