Tag: PowerShell

SQL Server PowerShell & SharePoint – Set Autogrowth on Content DB’s

​The SQL Server provider for Windows PowerShell exposes the hierarchy of SQL Server objects in paths similar to file system paths. You can use the paths to locate an object, and then use methods from the SQL Server Management Object (SMO) models to perform actions on the objects.

For the TechNet reference, check here: http://msdn.microsoft.com/en-us/library/cc281947.aspx

For a video walkthrough and great summary of it, check here: http://sqlserverpedia.com/blog/sql-server-bloggers/scripting-db-objects-in-powershell-video-transcript/

In this scenario, we’re going to use it for a repetitious task in most  SharePoint installations, which is, to assign more sensible Autogrowth settings to your Content DB’s.

For the full rundown on why we need to modify SQL Server settings (and can’t just do it from SharePoint directly), check around the 20 minute mark on this video: http://channel9.msdn.com/Series/Tuning-SQL-Server-2012-for-SharePoint-2013/Tuning-SQL-Server-2012-for-SharePoint-2013-02-Best-Practices-for-SQL-Server-Database-Settings#time=20m24s

1. Open up a PowerShell instance on your SQL Server.

2. Since our task is to set the FileGrowth properties of all the content DB’s, let’s first check out the FileGrowthType Enumeration: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.filegrowthtype.aspx
Your options are:

​Member ​Description
​KB ​File growth occurs in the specified number of KB.
​Percent ​File growth occurs as an amount that is a specified percentage of the existing file size
​None ​The file will not grow automatically.

The FileGrowthType enumeration class is served by the (datafile) GrowthType property and the (logfile) GrowthType property.

Modify your server instance names and autogrowth preferences in the following script, and execute! The filter displayed on it filters to apply only to databases and logfiles with “Content” in the name – the typical naming scheme to indicate SharePoint content databases. Modify to taste:

$Server="SQLSRV2012"      
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $Server
$databases = $SMOserver.Databases;
foreach ($DB in $databases | where{$_.Name -like '*Content*'}) {
    #Set Log File growth
 foreach ($DBLF in $DB.logfiles) {
  $DBLF.set_GrowthType("KB"); 
  $DBLF.set_Growth("51200"); #50mb 
  $DBLF.Alter();
        } 
 #set File Growth 
 $DBFG = $DB.FileGroups;
    foreach ($DBF in $DBFG.Files) {
  $DBF.set_GrowthType("KB"); 
  $DBF.set_Growth("102400"); #100mb 
  $DBF.Alter();
        }
    }

Print all Documents in SharePoint Library PowerShell

I put together the following script to help out someone on the internets, the requirement was to print all documents in a SP library. This PowerShell iterates through a library (folders included), downloads a local copy, then prints to the default printer on the machine it is executed on.

Swap $destination, $webURL and $listUrl parameters as needed.

Fun for the whole family and keeping our paper mill workers gainfully employed!

 

######################## Start Variables ########################
$destination = "C:"
$webUrl = "https://mysharepoint/sites/sitename/"
$listUrl = "https://mysharepoint/sites/sitename/Shared%20Documents/"
##############################################################
$web = Get-SPWeb -Identity $webUrl
$list = $web.GetList($listUrl)

function ProcessFolder {
    param($folderUrl)
    $folder = $web.GetFolder($folderUrl)
    foreach ($file in $folder.Files) {
        #Ensure destination directory
        $destinationfolder = $destination + "/" + $folder.Url 
        if (!(Test-Path -path $destinationfolder))
        {
            $dest = New-Item $destinationfolder -type directory 
        }
        #Download file
        $binary = $file.OpenBinary()
        $stream = New-Object System.IO.FileStream($destinationfolder + "/" + $file.Name), Create
        $writer = New-Object System.IO.BinaryWriter($stream)
        $writer.write($binary)
        $writer.Close()
		Start-Process -FilePath ($destinationfolder + "/" + $file.Name) -Verb Print
        }
}

#Download root files
ProcessFolder($list.RootFolder.Url)
#Download files in folders
foreach ($folder in $list.Folders) {
    ProcessFolder($folder.Url)
}

Delete All Items from a SharePoint List – PowerShell

In experimenting with the upper limit thresholds of just how many items you can create in a list, I ran into a little problem in that the web UI went mental and would cause IE to crash (this was with 21,000 list items). Trying to access the files via Webdav was equally non-workable. I needed to get rid of these list items to even be able to roll back the crazy test.

PowerShell to the rescue. Just replace the “http://serverurl” and “ENTER LIST NAME HERE” values with your web URL and List Name.

[System.Reflection.Assembly]::Load("Microsoft.SharePoint, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c")
[System.Reflection.Assembly]::Load("Microsoft.SharePoint.Portal, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c")
[System.Reflection.Assembly]::Load("Microsoft.SharePoint.Publishing, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c")
[System.Reflection.Assembly]::Load("System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")

# "Enter your site URL here"
$SITEURL = "http://serverurl"

$site = new-object Microsoft.SharePoint.SPSite ( $SITEURL )
$web = $site.OpenWeb()
"Web is : " + $web.Title

# Enter name of the List below
$oList = $web.Lists["ENTER LIST NAME HERE"];

"List is :" + $oList.Title + " with item count " + $oList.ItemCount

$collListItems = $oList.Items;
$count = $collListItems.Count - 1

for($intIndex = $count; $intIndex -gt -1; $intIndex--)
{
        "Deleting : " + $intIndex
        $collListItems.Delete($intIndex);
} 

Note that the assembly references at the top refer to SharePoint 2007 assemblies – not to worry, settings in the out-of-the-box SP 2010 Web.config will automagically bump up the references to the current SharePoint 2010 versions – there’s no burning need to go around adjusting assembly references if you come across PoweShell examples like this.

Syntax Formatting for PowerShell in Visual Studio 2010

PowerShell is an essential tool for advanced SharePoint development & administration. Unfortunately Visual Studio doesn’t offer PowerShell code syntax highlighting or Intellisense natively. You can however use Adam Driscoll’s plugin PowerGUI VSX from Codeplex, which runs on top of PowerGUI :

PowerGUI V requires the free, standalone application PowerGUI. Please download the correct version:

Some useful sources for SharePoint PowerShell scripts and advice:
http://sharepointpsscripts.codeplex.com/releases/view/40582
http://powergui.org/servlet/KbServlet/download/2812-102-4534/SharePoint2010PowerShell.pdf
http://blog.falchionconsulting.com/

[learn_more caption=”From TechNet: ” state=”open”] Windows PowerShell™ command-line interface is a new command-line tool and supporting scripting language from Microsoft that complements Cmd.exe in the Windows administration context. In the SharePoint administration context, Windows PowerShell supersedes the Stsadm.exe administration tool. Moving forward, you should use Windows PowerShell scripting technology to develop any new command-line scripts in SharePoint Foundation 2010.[/learn_more]