Thursday, 27 October 2011

Copy SharePoint lists and document libraries from one site to another using PowerShell

This article is the first in a series on exporting and importing SharePoint objects using the Content Deployment API and PowerShell. I’m not going to cover the API itself, as there are lots of articles doing this already, notably this series from Stefan Go├čner and MSDN, which I would urge you to check out for more details.

You can achieve some of this functionality with the Export-SPWeb and Import-SPWeb cmdlets provided with SharePoint 2010, but a) these are not available with SharePoint 2007 and b) using the Content Management API directly provides access to extra capabilities. I have written these scripts to work on SharePoint 2007 and 2010 (WSS and Foundation included), although to date I have only tested them on SharePoint Server 2010.

This first article provides a couple of functions for exporting a list or document library and all items contained within it from one site and importing it into another site. The destination site can be in a different site collection, web application and even farm. I have provided command parameters for what I think will be the most popular configuration options, but as we have full access to the API, the scripts can be adapted to suit requirements.

Exporting a list or document library

The first function exports a list or document library from a SharePoint site into either a folder or compressed .cmp file on a network or local drive. Before you can run the command, save the following function as a .ps1 file:

function Export-List
{
    Param (
           [parameter(Mandatory=$true)][string]$WebUrl,
           [parameter(Mandatory=$true)][string]$ListName,
           [parameter(Mandatory=$true)][string]$Path,
           [parameter(Mandatory=$false)][switch]$ExcludeDependencies,
           [parameter(Mandatory=$false)][switch]$HaltOnWarning,
           [parameter(Mandatory=$false)][switch]$HaltOnNonfatalError,
           [parameter(Mandatory=$false)][switch]$AutoGenerateDataFileName,
           [parameter(Mandatory=$false)][switch]$TestRun,
           [parameter(Mandatory=$false)][string]$IncludeSecurity,
           [parameter(Mandatory=$false)][string]$IncludeVersions,
           [parameter(Mandatory=$false)][int]$FileMaxSize,
           [parameter(Mandatory=$false)][switch]$Overwrite,
           [parameter(Mandatory=$false)][switch]$SuppressLog
           )
   
    #Load SharePoint 2010 cmdlets
    $ver = $host | select version
    if ($ver.Version.Major -gt 1)  {$Host.Runspace.ThreadOptions = "ReuseThread"}
    Add-PsSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
    #Load assemblies (needed for SharePoint Server 2007)
    [void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
   
    #Check parameters have the correct values
    if (!$IncludeSecurity)
    {
        $IncludeSecurity = "None"
    }
    else
    {
        if (($IncludeSecurity -ne "All") `
        -and ($IncludeSecurity -ne "WssOnly") `
        -and ($IncludeSecurity -ne "None"))
        {
            Throw "The IncludeSecurity parameter must be set to All, WssOnly or None"
        }
    }
   
    if (!$IncludeVersions)
    {
        $IncludeVersions = "LastMajor"
    }
    else
    {
        if (($IncludeVersions -ne "All") `
        -and ($IncludeVersions -ne "CurrentVersion") `
        -and ($IncludeVersions -ne "LastMajor") `
        -and ($IncludeVersions -ne "LastMajorAndMinor"))
        {
            Throw "The IncludeVersions parameter must be set to All, CurrentVersion, LastMajorAndMinor or LastMajor"
        }
    }
   
    if (!$FileMaxSize)
    {
        $FileMaxSize = 0
    }
       
    $site = New-Object Microsoft.SharePoint.SPSite($WebUrl)
    $web = $site.OpenWeb()
    $list = $web.Lists[$ListName]
    [bool]$FileCompression = $false
   
    #Set file paths for the export file and logs
    [string]$exportPath = $Path.TrimEnd("\")
    if ($exportPath.EndsWith(".cmp"))
    {
        $FileCompression = $true
        $exportFile = $Path.Replace($Path.Remove($Path.LastIndexOf("\")+1),"")
        $exportPath = $Path.Remove($Path.LastIndexOf("\"))
    }
   
    $logFilePath = $exportPath + "\exportlog.txt"
    New-Item -Path $logFilePath -Type File -Force | Out-Null
    Write-Host "Export log file created at" $logFilePath
   
    $exportObject = New-Object Microsoft.SharePoint.Deployment.SPExportObject
    $exportObject.Id = $list.ID
    $exportObject.Type = [Microsoft.SharePoint.Deployment.SPDeploymentObjectType]::List
   
    #Create the export settings from the parameters specified
    $exportSettings = New-Object Microsoft.SharePoint.Deployment.SPExportSettings
    $exportSettings.SiteUrl = $site.Url
    $exportSettings.ExportMethod = [Microsoft.SharePoint.Deployment.SPExportMethodType]::ExportAll
    $exportSettings.FileLocation = $exportPath
    $exportSettings.FileCompression = $FileCompression
    if ($FileCompression) { $exportSettings.BaseFileName = $exportFile }
    $exportSettings.ExcludeDependencies = $ExcludeDependencies
    $exportSettings.OverwriteExistingDataFile = $Overwrite
    $exportSettings.IncludeSecurity = $IncludeSecurity
    $exportSettings.IncludeVersions = $IncludeVersions
    $exportSettings.LogFilePath = $logFilePath
    $exportSettings.HaltOnWarning = $HaltOnWarning
    $exportSettings.HaltOnNonfatalError = $HaltOnNonfatalError
    $exportSettings.AutoGenerateDataFileName = $AutoGenerateDataFileName
    $exportSettings.TestRun = $TestRun
    $exportSettings.FileMaxSize = $FileMaxSize
    $exportSettings.ExportObjects.Add($exportObject)

    #Write the export settings to a log file   
    Out-File -FilePath $logFilePath -InputObject $exportSettings -Append -Encoding utf8
   
    #Run the export procedure
    $export = New-Object Microsoft.SharePoint.Deployment.SPExport($exportSettings)
    $export.Run()
   
    #Load notepad showing the log file
    if (!$SuppressLog) { notepad $logFilePath }
   
    #Dispose of the web and site objects after use
    $web.Dispose()
    $site.Dispose()
}

To load the function in a PowerShell console, type . ‘C:\YourPath\YourFilename.ps1’. You should now be able to use the Export-List command to export your list. A list of the parameters available are shown in the following table. Much of this text is a straight copy from the MSDN article referenced at the start of this article.

Examples

Optional/ Mandatory

Description

-WebUrl “http://portal/”

Mandatory

Absolute URL of the site containing the list being exported

-ListName “Team Contacts” Mandatory Display name of the list to be exported

-Path “C:\Export”

-Path “C:\Export\List.cmp”

Mandatory

Location on the file system where the exported files will be copied. You can also specify a .cmp file if you want the export to be compressed into files

-ExcludeDependencies

Optional

Exclude dependencies from the export package. Generally, you should always include export dependencies to avoid breaking objects in the export target

-HaltOnWarning

Optional

Stop the export operation if a warning occurs

-HaltOnNonfatalError

Optional

Stop the export operation for a non-fatal error

-AutoGenerateDataFileName

Optional

The file name for the content migration package should be automatically generated. When the export generates multiple .cmp files, the file names are appended numerically. For example, where the file name is "MyList", and where the export operation produces multiple .cmp files, the migration packages are named "MyList1.cmp", "MyList2.cmp", and so forth

-TestRun

Optional

Complete a test run to examine the export process and log any warnings or errors

-IncludeSecurity All

Optional

Site security groups and group membership information is exported.

The enumeration provide three values:

All : Specifies the export of user memberships and role assignments such as out of the box roles like Web Designer, plus any custom roles that extend from the out of the box roles. The ACL for each object is exported to the migration package, as well as user information defined in the DAP or LDAP servers.

None : No user role or security group information is exported. This is the default.

WssOnly : Specifies the export of user memberships and role assignments such as out of the box roles like Web Designer, plus any custom roles that extend from the out of the box roles. The ACL for each object is exported to the migration package; however, user information defined in the DAP or LDAP servers is not exported.

The default value when no parameter is specified is None.

Note: Be careful with this parameter. When exporting objects smaller than a web (for example, a list or list item) you should set IncludeSecurity to None; otherwise, security group and membership information for the entire web is exported.

-IncludeVersions

Optional

Determines what content is selected for export based on version information.

There are four enumeration values:

All , which exports all existing versions of selected files.

CurrentVersion , which exports only the most recent version of selected files.

LastMajor , which exports only the last major version of selected files. This is the default value.

LastMajorAndMinor , which exports the last major version and its minor versions.

Note that LastMajor is the default value when no parameter is specified.

-FileMaxSize

Optional

Maximum size for a content migration package (.cmp) file that is outputted by the export operation.

By default, the .cmp files are limited to 24 MB in size. If set to zero, the value resets to the default.

When site data exceeds the specified limit, site data is separated in to two or more migration files. However, if a single site data file exceeds the maximum file size, the operation does not split the source file, but rather it resizes the .cmp file to accommodate the oversize file. You can have any number of .cmp files.

The range of allowable size values for the .cmp file is from 1 MB to 2GB. If you specify a value that is outside this range, the export operation reverts to the default value of 24 MB.

-Overwrite

Optional

Overwrite an existing content migration package file when running export. If this parameter is not specified, an exception is thrown if the specified data file already exists

-SuppressLog Optional By default, the command will open Notepad at the end of the export routine, showing an activity list and any warning and errors reported. You can use this switch to prevent the log file from opening in Notepad, which is useful when performing bulk operations

Here are a few examples of how the Export-List function can be used. First, this command will export a “Team Contacts” list and all list items from the site http://portal/sites/sales to the folder C:\Export\TeamContacts on the server, overwriting any existing export in that folder:

Export-List -WebUrl “http://portal/sites/sales” -ListName "Team Contacts" -Path "C:\Export\TeamContacts" -Overwrite

This one will export a “Team Documents” library and all documents contained within it from the site http://portal/sites/sales to the file C:\Export\TeamDocuments.cmp, overwriting any existing .cmp file with the same name. This time, we are also going to export permissions set on the library by using the IncludeSecurity parameter:

Export-List -WebUrl “http://portal/sites/sales” -ListName "Team Documents" -Path "C:\Export\TeamDocuments.cmp" -Overwrite -IncludeSecurity All

image

Importing a list or document library

Adding your exported list or document library to another SharePoint site can be achieved using the Import-List function below. As with the export function, add the import function to the same or different .ps1 file and load it into the PowerShell console in the same way:

function Import-List
{
    Param (
           [parameter(Mandatory=$true)][string]$WebUrl,
           [parameter(Mandatory=$true)][string]$Path,
           [parameter(Mandatory=$false)][switch]$HaltOnWarning,
           [parameter(Mandatory=$false)][switch]$HaltOnNonfatalError,
           [parameter(Mandatory=$false)][switch]$RetainObjectIdentity,
           [parameter(Mandatory=$false)][string]$IncludeSecurity,
           [parameter(Mandatory=$false)][string]$UpdateVersions,
           [parameter(Mandatory=$false)][switch]$SuppressLog
           )
   
    #Load SharePoint 2010 cmdlets
    Add-PSSnapin Microsoft.SharePoint.PowerShell -erroraction SilentlyContinue
    #Load assemblies (needed for SharePoint Server 2007)
    [void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
       
    #Check parameters have the correct values
    if (!$IncludeSecurity)
    {
        $IncludeSecurity = "None"
    }
    else
    {
        if (($IncludeSecurity -ne "All") `
        -and ($IncludeSecurity -ne "WssOnly") `
        -and ($IncludeSecurity -ne "None"))
        {
            Throw "The IncludeSecurity parameter must be set to All, WssOnly or None"
        }
    }
   
    if (!$UpdateVersions)
    {
        $UpdateVersions = "Overwrite"
    }
    else
    {
        if (($UpdateVersions -ne "Overwrite") `
        -and ($UpdateVersions -ne "Append") `
        -and ($UpdateVersions -ne "Ignore"))
        {
            Throw "The UpdateVersions parameter must be set to Overwrite, Append or Ignore"
        }
    }
   
    $site = New-Object Microsoft.SharePoint.SPSite($WebUrl)
    $web = $site.OpenWeb()
   
    $importSettings = New-Object Microsoft.SharePoint.Deployment.SPImportSettings
   
    #Set file paths for the import file and logs
    $fileName = ""
    if ($Path.EndsWith(".cmp"))
    {
        $fileName = $Path.Replace($Path.Remove($Path.LastIndexOf("\")+1),"")
        $importPath = $Path.Remove($Path.LastIndexOf("\"))
        $importSettings.FileCompression = $true
        $importSettings.BaseFileName = $fileName
    }
    else
    {
        $importPath = $Path.TrimEnd("\")
        $importSettings.FileCompression = $false
    }
   
    $logFilePath = $importPath + "\importlog.txt"
    New-Item -Path $logFilePath -Type File -Force | Out-Null
    Write-Host "Import log file created at" $logFilePath
   
    #Create the import settings from the parameters specified
    Write-Host "Configuring import settings"
    $importSettings.SiteUrl = $site.Url
    $importSettings.WebUrl = $web.Url
    $importSettings.FileLocation = $importPath
    $importSettings.IncludeSecurity = $IncludeSecurity
    $importSettings.UpdateVersions = $UpdateVersions
    $importSettings.LogFilePath = $logFilePath
    $importSettings.HaltOnWarning = $HaltOnWarning
    $importSettings.HaltOnNonfatalError = $HaltOnNonfatalError
    $importSettings.RetainObjectIdentity = $RetainObjectIdentity
    $importSettings.UserInfoDateTime = "ImportAll"
    if (!$SuppressLog) { $importSettings }
   
    #Write the import settings to a log file
    Out-File -FilePath $logFilePath -InputObject $importSettings -Append -Encoding utf8
   
    #Run the import procedure
    Write-Host "Import running, please wait..."
    $import = New-Object Microsoft.SharePoint.Deployment.SPImport($importSettings)
    $import.Run()
    Write-Host "Import from" $Path "complete"
   
    #Load notepad showing the log file
    if (!$SuppressLog) { notepad $logFilePath }
   
    #Dispose of the web and site objects after use
    $web.Dispose()
    $site.Dispose()
}

The parameters available for importing lists and document libraries are shown in the table below:

Example

Optional/ Mandatory

Description

-WebUrl “http://portal/”

Mandatory

Absolute URL of the site into which the list will imported

-Path “C:\Export”

-Path “C:\Export\List.cmp”

Mandatory

Folder location and file name (if applicable) on the file system where the export files have been copied.

If a folder only is specified then PowerShell will assume that compression was not used during the export process. If a file with the .cmp extension is specified then compression will be assumed and applied to the import process.

-HaltOnWarning

Optional

Stop the import operation if a warning occurs

-HaltOnNonfatalError

Optional

Stop the import operation for a non-fatal error

-RetainObjectIdentity

Optional

RetainObjectIdentity ensures the same list GUID and item ID’s are used when migrating a list from one environment to another. It is most commonly used when you have two Web sites that are mirror images of each other, or in a publishing scenario to indicate that a list in one database is the same list in another database. An exception error is raised if you try and use the RetainObjectIdentity property for importing a list that already exists within the same SharePoint Web application.

Note: Because databases do not permit duplicate GUIDs, you should be careful when implementing this property. It is only useful in selective migration scenarios

-IncludeSecurity All

Optional

Site security groups and group membership information is imported.

The enumeration provide three values:

All : Specifies the export of user memberships and role assignments such as out of the box roles like Web Designer, plus any custom roles that extend from the out of the box roles. The ACL for each object is exported to the migration package, as well as user information defined in the DAP or LDAP servers.

None : No user role or security group information is exported. This is the default.

WssOnly : Specifies the export of user memberships and role assignments such as out of the box roles like Web Designer, plus any custom roles that extend from the out of the box roles. The ACL for each object is exported to the migration package; however, user information defined in the DAP or LDAP servers is not exported.

The default value when no parameter is specified is None.

-UpdateVersions

Optional

Indicates how versioning is managed in the destination location.

Allowable values for specifying versioning information on import:

Append : Appends versioned items on import.

Overwrite : Deletes the existing item on import, then reinserts the imported version.

Ignore : Ignores versioning.

The default value when no parameter is specified is Overwrite.

-SuppressLog Optional By default, the command will open Notepad at the end of the import routine, showing an activity list and any warning and errors reported. You can use this switch to prevent the log file from opening in Notepad, which is useful when performing bulk operations

Here are a few examples of how the Import-List function can be used. First, this command will import the “Team Contacts” list and all list items from the exported files in the folder C:\Export\TeamContacts to the site http://intranet/sites/sales:

Import-List -WebUrl “http://intranet/sites/sales” -Path "C:\Export\TeamContacts"

This one will import the “Team Documents” library and all documents contained within it from the file C:\Export\TeamDocuments.cmp to the site http://intranet/sites/sales. This time, we are also going to import permissions set on the library by using the IncludeSecurity parameter:

Import-List -WebUrl "http://intranet/sites/sales” -Path "C:\Export\TeamDocuments.cmp" -IncludeSecurity All

image

Note by comparing the screenshot above with the one earlier that the last modified date, time and user of the document has been migrated across with the list, which is set by the $importSettings.UserInfoDateTime = "ImportAll" command in the script.

The Power in PowerShell

In addition to having full access to the Content Management API in SharePoint, one of the other advantages with using PowerShell to perform these tasks is the ability for you to reuse previously created scripts or perform operations in bulk to handle repetitive tasks.

For example, once we have exported our list or document library, we can use these few lines of PowerShell script (SharePoint 2010 only) to walk through every site in a site collection and import the list into each of those sites:

$site = Get-SPSite “http://intranet/sites/sales”
$site | Get-SPWeb -Limit All | ForEach-Object {
    Write-Host Importing list to site $_.Url
    Import-List -WebUrl $_.Url -Path "C:\Export\TeamContacts" -IncludeSecurity All -SuppressLog
}

Using the Content Management API in this way can be very useful for deploying or migrating lists and libraries that contain a pre-configured standard set of items, columns, permissions and other settings across one or more existing sites in different site collections or farms.

As always though, make sure you test these scripts in a development environment first before running them in your production farm!

Tuesday, 18 October 2011

Pre-create SharePoint 2010 databases in SQL Server 2008 using PowerShell

There are some scenarios where it is a good idea to create databases in SQL Server prior to installing SharePoint. Typically, this is where a DBA may want control over the database naming convention and configuration in advance of letting you loose on the install DVD!

There are other benefits with doing this, too. For example, you can set the database and log file location on the file system, configure recovery mode, pre-grow, autogrowth and maximum database sizes in advance, and other Microsoft recommendations listed in the Storage and SQL Server capacity planning and configuration article on TechNet.

The example here lists all the databases and their configuration settings in a CSV file. We then use a PowerShell script to import this data and use the Invoke-Sqlcmd cmdlet to run a .sql file that creates the databases, configures the properties set in the CSV file, and assigns the SharePoint install account database owner permissions.

Before doing this, I recommend that you look at a couple of articles on TechNet in addition to the one listed above. First, the Deploy by using DBA-created databases article, which covers the process of installing SharePoint 2010 on pre-created databases (note that you cannot pre-create the Search service application databases using the method shown in this article), and also the “Adding the SQL Server Snap-ins to Windows PowerShell” section of the Running SQL Server PowerShell article, which goes through how to add the SQL Server Provider to the regular PowerShell or PowerShell ISE consoles – you will need to do this before running the PowerShell script shown in this article. Don’t forget that you will also need to login as an account with the correct permissions in SQL to be able to create the databases and set the permissions required.

CSV File

I am showing an example CSV file below that includes all columns needed for the script used in this article, but of course, you can add or remove any columns required for your deployment:

DBNAME,SETUPUSER,DATAFILEPATH,DATASIZE,DATAMAXSIZE,DATAFILEGROWTH,LOGFILEPATH,LOGSIZE,LOGFILEGROWTH,RECOVERYMODE
SP_Test1,DOMAIN\SP_Admin,C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLINSTANCE\MSSQL\DATA,1024000KB,1024000KB,102400KB,C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLINSTANCE\MSSQL\LOGS,1024KB,10%,SIMPLE
SP_Test2,DOMAIN\SP_Admin,C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLINSTANCE\MSSQL\DATA,512000KB,UNLIMITED,20%,C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLINSTANCE\MSSQL\LOGS,1024KB,10%,FULL

Admittedly, it doesn’t look the easiest to read here, but if you copy the contents to a CSV file using Notepad it will look a lot clearer. Note that I am not specifying SharePoint databases in the CSV file – I’ll leave that one to you considering your naming conventions and database configuration will vary anyway.

SQL File

Next, we need a .sql file that will be used by the PowerShell script to create the databases. This file was created using the “Script Action” option in the SQL Management Studio console after creating a single database and assigning dbo permissions to the SharePoint install account.

Script Action

I then saved the contents of the script as a “Create SharePoint Database.sql” file and replaced the values configured in the console with variables – e.g., replacing the database name with $(DBNAME). The PowerShell script shown later will replace these variables with values from the CSV file:

/* Create SharePoint database */
CREATE DATABASE [$(DBNAME)] ON  PRIMARY
( NAME = N'$(DBNAME)', FILENAME = N'$(DATAFILEPATH)\$(DBNAME).mdf' , SIZE = $(DATASIZE) , MAXSIZE = $(DATAMAXSIZE) , FILEGROWTH = $(DATAFILEGROWTH) )
LOG ON
( NAME = N'$(DBNAME)_log', FILENAME = N'$(LOGFILEPATH)\$(DBNAME)_log.ldf' , SIZE = $(LOGSIZE) , FILEGROWTH = $(LOGFILEGROWTH))
COLLATE Latin1_General_CI_AS_KS_WS
GO
ALTER DATABASE [$(DBNAME)] SET COMPATIBILITY_LEVEL = 100
GO
ALTER DATABASE [$(DBNAME)] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [$(DBNAME)] SET ANSI_NULLS OFF
GO
ALTER DATABASE [$(DBNAME)] SET ANSI_PADDING OFF
GO
ALTER DATABASE [$(DBNAME)] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [$(DBNAME)] SET ARITHABORT OFF
GO
ALTER DATABASE [$(DBNAME)] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [$(DBNAME)] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [$(DBNAME)] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [$(DBNAME)] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [$(DBNAME)] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [$(DBNAME)] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [$(DBNAME)] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [$(DBNAME)] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [$(DBNAME)] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [$(DBNAME)] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [$(DBNAME)] SET  DISABLE_BROKER
GO
ALTER DATABASE [$(DBNAME)] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [$(DBNAME)] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [$(DBNAME)] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [$(DBNAME)] SET  READ_WRITE
GO
ALTER DATABASE [$(DBNAME)] SET RECOVERY $(RECOVERYMODE)
GO
ALTER DATABASE [$(DBNAME)] SET  MULTI_USER
GO
ALTER DATABASE [$(DBNAME)] SET PAGE_VERIFY CHECKSUM 
GO
USE [$(DBNAME)]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [$(DBNAME)] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO

/* Add SharePoint account as the database owner */
USE [$(DBNAME)]
GO
CREATE USER [$(SETUPUSER)] FOR LOGIN [$(SETUPUSER)]
GO
USE [$(DBNAME)]
GO
EXEC sp_addrolemember N'db_owner', N'$(SETUPUSER)'
GO

PowerShell Script

Finally, we need the PowerShell script. This walks through each line in the CSV file, reads the columns, and uses the Invoke-Sqlcmd cmdlet to run the .sql file - replacing the variables with the CSV column data:

$csvData = @(Import-Csv -Path C:\Scripts\SQL\Databases.csv)
foreach ($line in $csvData)
{
    $parameters = @()
    $line | Get-Member -MemberType NoteProperty | ForEach-Object {
       
        $parameters = $parameters + ($_.Name + "=" + $line.($_.Name))
    }   
    Invoke-Sqlcmd -InputFile "C:\Scripts\Create SharePoint Database.sql" -ServerInstance "SQLINSTANCE" -Variable $parameters
}

If you are using this method to pre-create SharePoint databases, don’t forget to apply the other recommendations listed in the Deploy by using DBA-created databases article.

You can also use the method of replacing variables shown in the PowerShell script above to manage any repeatable action in SQL – not just database creation. For example, you could keep a .sql file handy that adds an AD account as a SQL login and then use the Invoke-Sqlcmd command to add the SharePoint install account to SQL before creating any databases:

SQL File:

USE [master]
GO
CREATE LOGIN [$(USER)] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[British]
GO
EXEC master..sp_addsrvrolemember @loginame = N'$(USER)', @rolename = N'dbcreator'
GO
EXEC master..sp_addsrvrolemember @loginame = N'$(USER)', @rolename = N'securityadmin'
GO

PowerShell script:

Invoke-Sqlcmd -InputFile "C:\Scripts\Add Account to Logins.sql" -ServerInstance "SQLINSTANCE" -Variable "USER=DOMAIN\SP_Admin"

Monday, 10 October 2011

Windows PowerShell Command Builder for SharePoint 2010 and Office 365

A very handy Silverlight application has been released on Microsoft TechNet to provide a visual interface for assembling PowerShell commands to be used in SharePoint 2010 products and Office 365.

It uses a drag-and-drop interface to add the verbs and nouns of each cmdlet, and will automatically filter available options depending on which are selected. Once the cmdlet has been chosen, it will display the required and optional parameters associated with it so that you can complete the command.  The example below shows how it can be used to design a Move-SPSite command.

image

Using the drop-down options available at the top left corner of the interface, cmdlets can be filtered by selecting products SharePoint Foundation 2010, SharePoint Server 2010 or Office 365 and there are also a few scenarios that can be chosen in the “I need to” drop-down – e.g., “Add a user to a domain group”.

You can access the command builder here: http://www.microsoft.com/resources/TechNet/en-us/Office/media/WindowsPowerShell/WindowsPowerShellCommandBuilder.html. For more information from Microsoft on PowerShell and SharePoint in general, check out the Resource Center here: http://technet.microsoft.com/en-us/sharepoint/ff603532.aspx.