SharePoint Content Database PowerShell Scripts

There are a number of PowerShell database cmdlets provided by SharePoint; in this tip I will cover the ones that deal with content databases.  The following is the outline for this tip:

• PowerShell 101 - getting setup to use PowerShell to manage content databases
• Get-SPContentDatabase - get information about content database(s)
• New-SPContentDatabase - create a new content database in a web application
• Dismount-SPContentDatabase - detach a content database from a web application
• Mount-SPContentDatabase - attach a content database to a web application
• Remove-SPContentDatabase - detach a content database from a web application and drop the database from the SQL Server instance; be careful with this one!
• Set-SPContentDatabase - set properties of a content database
• Test-SPContentDatabase - test a content database against a web application to see if all of the customizations in the content database are in the web application

If you choose to use SharePoint Central Administration to manage content databases, you'll use the Manage content databases option in the screenshot below.  I will cover using Central Administration to manage content databases in a future tip.

PowerShell 101

There are quite a few tips on using PowerShell to perform various SQL Server tasks; take a look at the PowerShell tips category onMSSQLTips.com for some great content.   For managing SharePoint content databases, you have two options available in Windows Server 2008:

• SharePoint 2010 Management Shell
• Windows PowerShell ISE

The SharePoint 2010 Management Shell is installed automatically by SharePoint; you can find it in the Microsoft SharePoint 2010 Products program group.  The user interface is pretty much like the Command Prompt; it is simple and effective.  The Windows PowerShell ISE is what I prefer; it is a GUI tool that's a little more polished than the SharePoint 2010 Management Shell.  A good SQL Server analogy would be SQLCMD versus SQL Server Management Studio.

Windows PowerShell ISE is not installed by default on your Windows 2008 server; follow these steps to install it:

• Go to Server Manager, Add Features
• Check Windows PowerShell Integrated Scripting Environment (ISE)

After installing Windows PowerShell ISE, you can launch it from the Accessories / Windows PowerShell program group.  There is one additional step that you need to perform in order to use it with SharePoint; you need to load the Microsoft.SharePoint.PowerShell snap-in.  There are two ways to do this:

• Run the PowerShell command Add-PSSnapin Microsoft.SharePoint.PowerShell (every time you open PowerShell ISE)
• Add the snap-in in the profile file; e.g. C:\Windows\System32\WindowsPowerShell\v1.0\profile.ps1 (one time)

The following is an example of the code you can put in the profile to add the snap-in:

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

There is one more thing you need and that is proper permission to use PowerShell to update a content database and the farm configuration database; you need to be a member of the SharePoint_Shell_Access database role in the respective databases.  You will need a SharePoint farm admin to add you to this role using the Add-SPShellAdmin cmdlet.

Add a user to the SharePoint_Shell_Access role in the farm configuration database only:

Add-SPShellAdmin-UserName domain\username

Add a user to the SharePoint_Shell_Access role in the farm configuration database and content database specified:

Add-SPShellAdmin -UserName DOMAIN\USERNAME -Database DATABASENAME

Add a user to the SharePoint_Shell_Access role in the farm configuration database and every content database:

Get-SPContentDatabase | Add-SPShellAdmin -UserName DOMAIN\USERNAME

Introduction

Here are a couple of points before we get in to the details:

• If you are new to SharePoint 2010, take a look at our earlier tip Introduction to SharePoint 2010 for DBAs.  In the sections that follow the concepts presented in the earlier tip are fundamental to your understanding.
• A content database can either be attached to a web application or detached; where a cmdlet works with a content database that is detached you will see the -Name parameter; when a cmdlet works with a content database that is attached to a web application you will see the Identity parameter; the Identity parameter can be the database name, its Id (GUID or UNIQUEIDENTIFIER as returned by Get-SPContentDatabase), or a SPContentDatabase object in the PowerShell pipeline.
• Along the same lines you don't specify the DatabaseServer parameter when a content database is attached to a web application; SharePoint knows where it is.
• In the sample code when I use contentdb I mean the database name
• I'm running the PowerShell cmdlets on a Windows 2008 server that is connected to a SharePoint farm; I'm not going in to running the cmdlets remotely.

Get-SPContentDatabase

Get-SPContentDatabase returns information about one or more content databases in the SharePoint farm.  The server where you run the command is connected to a particular SharePoint farm so the available databases include only those in the farm. 

To show information about all databases in the farm, run the Get-SPContentDatabase cmdlet without any parameters.  The output is (only one database shown):

Id : 2fb2c72c-e098-4405-9d39-fefd3686fb67
Name : WSS_Content
WebApplication : SPWebApplication Name=SharePoint - 80
Server : DEV-FARM
CurrentSiteCount : 2

Server is the name of the database server; the current site count is the number of site collections in the content database.  To get information about a particular database you can add the -Identity contentdb parameter.

New-SPContentDatabase

To create a new content database run the New-SPContentDatabase cmdlet.  I would contend that this is the most important of the database cmdlets as it allows you to create content databases that you can then use for specific site collections.  A typical command to create a new content database would be:

New-SPContentDatabase -Name contentdb -DatabaseServer dbserver -WebApplication webappname `
  -MaxSiteCount 1 -WarningSiteCount 0

The funny character at the end of the first line above is the one to the left of the number 1 on your keyboard; it is the PowerShell line continuation character (a very poor choice in my opinion).  Note that the content database is associated with or owned by a web application.  You will likely need to get the web application name from your SharePoint administrator.  An example web application name would be http://intranet.yourdomainname.com. 

You can (and probably should) specify the MaxSiteCount parameter to limit the number of site collections that can be stored in this content database; the default value is 15,000.  I prefer to set the MaxSiteCount to 1 so that someone using SharePoint Central Administration to create a new site collection doesn't inadvertently put in in this content database.  The MaxSiteCount can be changed with the Set-SPContentDatabase cmdlet (described later) or in Central Administration.

Creating a content database is the first step in proactively managing your content databases.  The next step is to specify which site collection(s) are to be stored in the content database.  Your SharePoint administrator should use the New-SPSite cmdlet along with the ContentDatabase parameter when creating a new site collection in order to specify the content database for the site collection.  If you are using Central Administration to create site collections you cannot directly specify the content database for the site collection; you can make sure that only one content database is available by setting the MaxSiteCount or Database Status (described later).

Dismount-SPContentDatabase

Dismount-SPContentDatabase detaches a content database from the web application.  You would do this if you wanted to move the content database to a different web application and/or a different database server.  The operation updates the farm configuration database but otherwise leaves the content database intact in the SQL Server instance.   

A typical command to detach a database would be:

Dismount-SPContentDatabase -Identity contentdb

Mount-SPContentDatabase

Mount-SPContentDatabase is the inverse of Dismount-SPContentDatabase.  In this case you are attaching a content database to a web application.  This would be the case if you ran Dismount-SPContentDatabase and are now ready to attach the content database to a different web application or you moved a detached content database from another database server. 

A typical command to attach a content database would be:

Mount-SPContentDatabase -Name contentdb -DatabaseServer dbserver -WebApplication webappname

Mount-SPContentDatabase can also be used when you upgrade a SharePoint 2007 database to SharePoint 2010; this is referred to on TechNet as the database attach upgrade method  The database will be upgraded to the SharePoint 2010 schema and attached to the web application.  You will get a list of any errors or warnings generated by the upgrade.  Typical errors have to do with customizations referenced in the content database that are not in the web application.  I will not cover those issues here as the DBA isn't usually (hopefully) the person who needs to resolve them.

Remove-SPContentDatabase

Remove-SPContentDatabase detaches a content database from the web application and drops it from the SQL Server instance.  As a general rule I don't use this one.  Even if I do want to drop the database I use Dismount-SPContentDatabase then go to SQL Server Management Studio and drop the database. 

A typical command to detach a content database and drop it would be:

Remove-SPContentDatabase -Identity contentdb

Set-SPContentDatabase

Set-SPContentDatabase is used to set the properties of the content database.  Typical properties to set are:

• MaxSiteCount - the maximum number of site collections that can be stored in the content database
• WarningSiteCount - the number of site collections when a warning notification is sent
• Status - Disabled (no site collections can be added) or OnLine (site collections can be added as long as the MaxSiteCount hasn't been reached)

A typical command to disable new site collections from being added to the content database would be:

Set-SPContentDatabase -Identity contentdb -Status Disabled

A typical command to enable new site collections to be added to the content database would be:

Set-SPContentDatabase -Identity contentdb -Status OnLine

As an aside there is a little bit on inconsistency with the content database Status in Central Administration versus the Set-SPContentDatabase cmdlet.  If you select the Manage content databases option from Central administration, you will see a list of content databases with a Database Status column that has the values Started or Stopped.  When you select a database to view or modify its properties, you will see a Database Status dropdown with the options Ready and Offline.

Test-SPContentDatabase

Test-SPContentDatabase will check a content database against a web application to determine whether all of the customizations referenced in the content database are available in the web application.  This cmdlet can check a content database that is attached to a web application or one that is detached.  In either case the output of the command is a list of errors and warnings which hopefully will be resolved by someone other than the DBA.  This is the same list that Mount-SPContentDatabase generates when you are attaching a SharePoint 2007 content database to SharePoint 2010.

A typical command to test a content database that is attached to a web application would be:

Test-SPContentDatabase -Identity contentdb

A typical command to test a content database that is not attached to a web application would be:

Test-SPContentDatabase -Name contentdb -WebApplication webappname

Stop SharePoint Completely/Stopping Farm PowerShell

For Wss 3.0:

Stop the farm by following these steps:

1. in the Services snap-in, stop the following services:

• Windows SharePoint Services Administration service
• Windows SharePoint Services Search service
• Windows SharePoint Services Timer service
• Windows SharePoint Services Tracing service
• Windows SharePoint Services VSS Writer service

2.    at the command prompt, type iisreset /stop.

Do it for all SharePoint installed servers in your farm.
http://technet.microsoft.com/en-us/library/cc512723(v=office.12).aspx

For Moss 2007
Stop the farm by following these steps:

1.  in the Services snap-in, stop the following services:

• Microsoft Single Sign-On service
• Office Document Conversions Launcher service
• Office Document Conversions Load Balancer service
• Office SharePoint Server Search service
• Windows SharePoint Services Administration service
• Windows SharePoint Services Search service
• Windows SharePoint Services Timer service
• Windows SharePoint Services Tracing service
• Windows SharePoint Services VSS Writer service

2. On the server that is running the Central Administration Web site, at the command prompt, type iisreset /stop.
Do it for all SharePoint installed servers in your farm.
http://technet.microsoft.com/en-us/library/cc512725(v=office.12).aspx

For SharePoint Foundation 2010

1. in the Services snap-in, stop the following services:

• SharePoint 2010 Administration
• SharePoint 2010 Timer
• SharePoint 2010 Tracing
• SharePoint 2010 User Code Host
• SharePoint 2010 VSS Writer
• SharePoint Foundation Search V4
• SharePoint Server Search v4
• World Wide Web Publishing Service

2. at the command prompt, type iisreset /stop.
Do it for all SharePoint installed servers in your farm.
http://technet.microsoft.com/en-us/library/cc512723(v=office.14).aspx

For SharePoint Server 2010

1.  in the Services snap-in, stop the following services:

• SharePoint 2010 Administration
• SharePoint 2010 Timer
• SharePoint 2010 Tracing
• SharePoint 2010 User Code Host
• SharePoint 2010 VSS Writer
• SharePoint Foundation Search V4
• World Wide Web Publishing Service
• SharePoint Server Search 14
• Web Analytics Data Processing Service
• Web Analytics Web Service

Not:  The final two services are part of the Web Analytics service application. If you are running the Web Analytics service application and choose to rename your server, you must also reconfigure the Web Analytics database locations. For details, see To reconfigure Web Analytics database locations .

2. On the server at the command prompt, type iisreset /stop.
Do it for all SharePoint installed servers in your farm.
http://technet.microsoft.com/en-us/library/cc512725(v=office.14).aspx

For SharePoint 2013

1. in the Services snap-in, stop the following services:

• SharePoint 2013 Administration
• SharePoint 2013 Timer
• SharePoint 2013 Tracing
• SharePoint 2013 User Code Host
• SharePoint 2013 VSS Writer
• World Wide Web Publishing Service
• SharePoint Server Search

2. On the server that is running the SharePoint Central Administration website, at a command prompt, type iisreset /stop.
Do it for all SharePoint installed servers in your farm
http://technet.microsoft.com/en-us/library/cc512725(v=office.15).aspx

Source: Bugra Postaci's Blog

Map Network Drive to SharePoint Online O365

Map a Network Drive to SharePoint Online (O365)

The trick is to:

1. Copy the URL of the Document Library to the clipboard
2. Open Windows Explorer
3. Right-click "Network" and click "Map Network Drive" to display the dialog "Map Network Drive" 
4. In the dialog click "Connect to a Web site that you can use to store your documents and pictures" to display the dialog "Add Network Location"
5. In the dialog click "Next" and then "Next" again to display a new dialog
6. In the textbox "Internet or network address" paste the copied URL of the Document Library
7. Delete the "https:" part of the URL
8. Replace every forward slash with a backward slash
9. Insert "@ssl" after ".com"
10. Finish the dialogs

In my example the URL is "https://awesomesauce.sharepoint.com/Document%20Library" and should hence be "\\awesomesauce.sharepoint.com@ssl\Document%20Library" after the above changes.

You should now be able to access the Document Library from your Windows Explorer.

1. Open the document library in SharePoint Online
2. Copy the URL of the document library to the Clipboard (e.g https://awesomesauce.sharepoint.com/Docs/Forms/AllItems.aspx)
3. Open a new Word document
4. Click Save As to open the Save As dialog
5. Paste the URL from the clipboard into the Address text box at the top of the Save As dialog
6. Delete /Forms/AllItems.aspx from the URL in the text box
7. Press Enter to open the document library
8. Right-click Favorites (top left of the Save As dialog)
9. Click Add current location to Favorites
10. Rename the new shortcut (optional)

Add to Favorites 

SharePoint Online in Office 365 uses forms based authentication and the user is authenticated using a SAML token. This SAML token expires every 2 hours (some say longer though). After that the mapped drive for the SharePoint library cant access the library unless the user signs in to the SharePoint Online site again from the browser, even if the user tick off "Keep me signed in". As an alternative you can add the URL of the document library to Favorites in Windows Explorer. You'll need to do that via e.g. Word.

Now to ensure you do not lose connectivity to the library after your SAML authentication token expires:

1. Open the document library in SharePoint Online
2. Copy URL to clipboard ex: (https://awesomesauce.sharepoint.com/Docs/Forms/AllItems.aspx))
3. Open a new Word document
4. Click Save As to open the Save As dialog
5. Paste the URL from the clipboard into the Address text box at the top of the Save As dialog
6. Delete /Forms/AllItems.aspx from the URL in the text box
7. Press Enter to open the document library
8. Right-click Favorites (top left of the Save As dialog)
9. Click Add current location to Favorites
10. Rename the new shortcut (optional)

List All Nintex Workflows in SharePoint Farm PowerShell

List All Nintex Workflows in SharePoint Farm PowerShell

##########################################################################################                              

                                            ### Nintex Workflow Statistics Query ###
#
#                   This script will use the Nintex Assembilies to query the Nintex databases and find workflows.
#
#                   Please contact support@nintex.com for questions or more information on this script.

#

#        Please ensure you run this script as Administrative account that has rights to each Nintex database
#
###########################################################################################

#Adding SharePoint Powershell Snapin
Add-PSSnapin Microsoft.SharePoint.PowerShell -EA silentlycontinue

# The Line below will suppress error messages, uncomment if you are seeing errors but still receiving results.

#$ErrorAction = 'silentlycontinue'

# Loading SharePoint and Nintex Objects into the PS session
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
[void][System.Reflection.Assembly]::LoadWithPartialName("Nintex.Workflow")
[void][System.Reflection.Assembly]::LoadWithPartialName("Nintex.Workflow.SupportConsole")
[void][System.Reflection.Assembly]::LoadWithPartialName("Nintex.Workflow.Administration")
[void][System.Reflection.Assembly]::LoadWithPartialName("Nintex.Forms.SharePoint.Administration")

# Grab Nintex Config database name

$CFGDB = [Nintex.Workflow.Administration.ConfigurationDatabase]::OpenConfigDataBase().Database

# Creating instance of .NET SQL client
$cmd = New-Object -TypeName System.Data.SqlClient.SqlCommand

$cmd.CommandType = [System.Data.CommandType]::Text

# Begin SQL Query
$cmd.CommandText = "SELECT
               i.WorkflowName,
               i.SiteID,
               i.WebID,
               i.listid,
      pw.Author
FROM dbo.WorkflowInstance I
inner join WorkflowProgress P
               ON I.InstanceID = P.InstanceID
Inner join [$CFGDB].dbo.publishedworkflows pw
on i.WorkflowID = pw.WorkflowId
GROUP BY GROUPING SETS((i.siteid, i.webid, i.listid, i.workflowname, pw.Author), ());"

$indexes = @()

# Call to find all Nintex Content Databases in the Nintex Configuration Database, then execute the above query against each.
foreach ($database in [Nintex.Workflow.Administration.ConfigurationDatabase]::GetConfigurationDatabase().ContentDatabases)
{

$reader = $database.ExecuteReader($cmd)

# Creating a table
while($reader.Read())
{
$row = New-Object System.Object

if(![string]::IsNullOrEmpty($reader["SiteID"])){
$Site = $(Get-SPSite -identity $reader["SiteID"])
}

if(![string]::IsNullOrEmpty($reader["WebID"])){
$SubSite = $Site.Allwebs[[Guid]"$($reader["WebID"])"]
}
if(![string]::IsNullOrEmpty($reader["ListID"])){
$List = $SubSite.Lists[[Guid]"$($reader["ListID"])"]
}

#Adding Query results to table object
$row | Add-Member -MemberType NoteProperty -Name "Workflow Name" -Value $reader["WorkflowName"]
$row | add-member -MemberType NoteProperty -Name "Database" -value $Site.ContentDatabase.Name
$row | Add-Member -MemberType NoteProperty -Name "Site Collection" -Value $Site.Url
$row | Add-Member -MemberType NoteProperty -Name "Subsite" -Value $SubSite
$row | Add-Member -MemberType NoteProperty -Name "List" -Value $List.title
$row | Add-Member -MemberType NoteProperty -Name "Author" -Value $reader["Author"]

$indexes += $row
}
}

#Print results on screen
$indexes  | FT -autosize
Write-host "Total Workflows in all DataBases:" $indexes.Count