Friday, January 29, 2016

How to Set SharePoint 2013 Content Database to Read Only Mode using PowerShell

Requirement: Set content database to read only in SharePoint 2013 using PowerShell.

Solution:  We may have to set SharePoint databases to read-only mode in scenarios such as: upgrades, disaster recovery / highly available maintenance, patching, etc. Setting SharePoint content database in read-only mode from SQL Server Management Studio is explained in my another article: How to make SharePoint database read only. This time, Lets make content database read only using PowerShell.

Tips: Use Get-SPContentDatabase | Select Name to get all SharePoint content databases!

PowerShell to set content database read only SharePoint
Prerequisites: Make sure you have SQL Server Management studio installed in your server/workstation to use below script!
#You may have to adjust the below path according to your SSMS version!
Add-Type -Path "C:\Program Files\Microsoft SQL Server\130\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"

#Configuration variables 
$SQLInstance ="Abjsvrv9"
$DatabaseName = "SP16_Content_Crescent_Intranet"

Try {
    #Set the Error Action
    $ErrorActionPreference = "Stop"

    #Get the Server
    $Server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $SQLInstance
      
    #Get the Database
    $Database = $Server.Databases[$DatabaseName]

    $Server.KillAllProcesses($DatabaseName)
    #Set Read only option to True
    $Database.DatabaseOptions.ReadOnly = $true
    $Database.Alter()
    
    Write-host "$($DatabaseName) Database made Read-only" -f Green
}
catch {
 Write-Host "Operation Failed. Find the Error Message below:" -ForegroundColor Red
 Write-Host $_.Exception.Message -ForegroundColor Red
}
finally {
 #Reset the Error Action to Default
 $ErrorActionPreference = "Continue"
}
This makes read only content database in SharePoint. To verify, You can navigate to SharePoint 2013 central Administration >> Application Management >> Manage Content Databases >> Check the " Database Read-Only " flag!
sharepoint content database read only

and if you navigate to any of the site collection which lives in the above content database, you'll find the maintenance mode notification in SharePoint 2016 sites!
sharepoint 2013 read only content database


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Move SharePoint Database from One Drive to Another

Requirement: Move SharePoint database from one drive to another in SQL Server! By default, SharePoint creates its databases in the default SQL Server location: "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA" folder (SQL Server 2016). We don't have any option from SharePoint side to specify where the data and log files for the database to be created.

In my scenario, our SQL Server uses SAN storage and have a different drives, Say: E:\ and F:\ and we wanted all data files to go to E:\ and LOGS into F:\

Important: As always, Backup your databases before performing any of this activity!

Solution:
In short, stop SharePoint services, Detach the database from SQL Server, Move database files to a different location, Attach the database back and then start SharePoint services!

Step 1: Stop All SharePoint Services
Prior moving databases, we have to stop all SharePoint services and applications which are accessing the databases. So, stop all SharePoint services first! Follow this article to stop or start all SharePoint services: How to Stop All SharePoint 2013 Services

If you want to move a specific content database, you can either set the database status to "Offline" from SharePoint 2013 Central Administration or:
  • Dismount the database (Dismount-SPContentDatabase -Name "Content-database-name")
  • Detach the Database from SQL Server - Move the database to new location - Attach the database again
  • Mount the database back to SharePoint: Mount-SPContentDatabase -Name "Content-database-name" -DatabaseServer "db-server-name" -WebApplication "Name-or-URL-of-the-webapp"
Step 2: Detach the database from SQL Server:
Lets move SharePoint SQL Server Database Files to different drive location. First, make a note of all database files associated with the database such as MDF, NDF & LDF.

Make a note of the Database Files:
  • Open SSMS >>  Right click on the target database >> Properties
  • Go to Files tab >> Make note of the Path and File Names
    moving a sharepoint database to another drive
You can get the files and locations using SQL Query:
USE "SP16_Content_Crescent_Intranet"
   
SELECT   
        DB_NAME(database_id) AS [Database Name],  
        physical_name AS [Path],  
        type_desc AS [Type]  
    FROM sys.master_files WHERE database_id = DB_ID()  

Detach the databases using SQL Management studio
Make sure you stopped SharePoint from accessing the database. If you don't stop SharePoint services or dismount the database from SharePoint side, You'll get "Cannot detach the database 'Content-Database-Name' because it is currently in use." Error!
  • Right click the database >> Tasks >> Detach
    move sharepoint database to different drive
  • Check "Drop Connections" >> Click OK to detach the database.
You can detach a database using SQL Script it as:
use master
go
Exec sp_detach_db 'SP16_Content_Crescent_Intranet'
go

Step 3: Move database files to new location
Once you detached the databases, You can move your database files to a new location (just a Cut-Paste from Windows Explorer) wherever required. Its a best practice to place DATA and LOG files to a different location.

Step 4: Attach the databases from new location:
Once the database files are moved to a new location, Lets attach it to SQL Server again.
  • Open SQL Management studio >> Right click on Database node >> Select Attach
  • Click on Add >> Browse to the new location for the databases and select the MDF file. All associated NDF and LOG files will be attached automatically. Click OK to attach the database to SQL Server.
    move sharepoint database to different drive
You can script the above with SQL Query:
CREATE DATABASE [SP16_Content_Crescent_Intranet] ON
( FILENAME = N'D:\SQL Server\Data\SP16_Content_Crescent_Intranet.mdf' ),
( FILENAME = N'D:\SQL Server\Data\SP16_Content_Crescent_Intranet_log.ldf' )
FOR ATTACH
GO
Its important to keep the same NAME for the database as its referenced in SharePoint using the database name (regardless of the database file names!)

Step 5: Start all SharePoint Services
Use the article to start all SharePoint services again: How to Start all SharePoint 2013 Services. That's all! We moved SharePoint database from one drive to another!! Browse to your SharePoint site and verify all works correctly!

How to Change the default location for SQL Server Databases?

Well by following above steps we can move existing SharePoint database to different drive. How about new databases we are going to create in future?
  • Login to your SQL Server >> Open SQL Server Management Studio >> Go to server properties
  • Under "Database Settings" tab, Specify the locations for Data, LOG and Backup.
  • Hit save to commit your changes. Restart your SQL Server Service for the changes to take effect.
    move sharepoint database from one drive to another
Related Post: How to move SharePoint databases to new SQL server

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Friday, January 22, 2016

Replace "Edit" Permissions with "Contribute" in SharePoint 2013-2016 using PowerShell

Problem: Prior to SharePoint 2013, Members group of the site has "Contribute" permission. From SharePoint 2013, there was a new permission level "Edit" introduced with more rights. This introduced additional issue like members can delete lists and libraries!

Solution: Remove Edit permissions and add contribute permissions to all users and group of the site! Here is how: Navigate to:
  • Site Settings >> Site permissions
  • Select the person or group with edit permissions, you want to change >> Click on "Edit User Permissions" ribbon button sharepoint powershell change group permissions
  • In Edit Permissions page uncheck "Edit" permission and select "Contribute"
    replace Edit Permission with Contribute in SharePoint
  • Click "OK" to save changes. Now the Members group has contribute permissions instead of Edit.
Editing the "Edit" permission level and removing "Add, Edit and Delete Lists" permission from it - also solves the problem, but its not recommended to change OOTB permission levels in SharePoint!

But wait! who can go to each site of the web application and repeat above steps? Tedious! isn't it? So, lets use PowerShell to re-assign permissions to Contribute from Edit.

PowerShell to replace edit permissions of member group to contribute access rights:
This PowerShell script changes the permission level for all users and groups from Edit to Contribute.
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Web Application URL
$WebAppURL="http://intranet.crescent.com/"

#Get all webs from the web application
$WebsCollection = Get-SPWebApplication $WebAppURL | Get-SPSite -Limit All | Get-SPWeb  -limit All

#Iterate through each web and replace "Edit" to "Contribute"
Foreach ($web in $WebsCollection)
{
    #Get Edit and Contribute permission levels
    $ContributePermission = $web.RoleDefinitions["Contribute"]
    $EditPermission = $web.RoleDefinitions["Edit"]

    Write-host "Processing:" $web.Url

    If (!$web.HasUniquePerm)
    {
        Write-host -f Yellow "Web is inheriting permissions..."
        continue
    }

    #Get all users and groups with Edit permissions
    $RoleAssignmentsColl = $web.RoleAssignments | where {$_.RoleDefinitionBindings -eq $EditPermission}
    
    #Loop through each user/group with Edit permission level
    foreach($RoleAssignment in $RoleAssignmentsColl)
    { 
        #Add Contribute Permissions
        if(!$RoleAssignment.RoleDefinitionBindings.Contains($ContributePermission))
        {
            $RoleAssignment.RoleDefinitionBindings.Add($ContributePermission)
            $RoleAssignment.Update()
            Write-host -f Green "Contribute Permission Added to the User/Group:" $RoleAssignment.Member.Name
        }
 
        #Remove Edit permissions
        if($RoleAssignment.RoleDefinitionBindings.Contains($EditPermission))
        {
            $RoleAssignment.RoleDefinitionBindings.Remove($EditPermission)
            $RoleAssignment.Update()
            Write-host -f Green "Edit Permission removed from the User/Group:" $RoleAssignment.Member.Name
        }
    }
}


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Tuesday, January 19, 2016

Fix "MissingAssembly" Errors of Test-SPContentDatabase in SharePoint 2013 Migration

Problem:
During SharePoint 2013 migration, we ran Test-SPContentDatabase cmdlet and the output report contains several MissingAssembly errors!

Category        : MissingAssembly
Error           : True
UpgradeBlocking : False
Message         : Assembly [DealEventEventhandler, Version=1.2.6.0, Culture=neutral, PublicKeyToken=b4122bae67581526] is referenced in the database [wss_content], but is not installed on the current farm. Please install any feature/solution which contains this assembly.
Remedy          : One or more assemblies are referenced in the database [wss_content], but are not installed on the current farm. Please install any feature or solution which contains these assemblies.
Root Cause:
Usually the MissingAssembly errors arises on event receiver which are not installed & deployed to the farm and still referenced in the SharePoint list or libraries!

Solution: Install the missing feature/solution which contains the missing assembly to your SharePoint Farm! Simple, Huh? but wait, You may not be always in a position to do this. You may proceed find and removing the missing assemblies from SharePoint at times.

Find all locations where the missing assembly is being referenced using PowerShell:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

Function Run-SQLScript($SQLServer, $SQLDatabase, $SQLQuery)
{
    $ConnectionString = "Server =" + $SQLServer + "; Database =" + $SQLDatabase + "; Integrated Security = True"
    $Connection = new-object system.data.SqlClient.SQLConnection($ConnectionString)
    $Command = new-object system.data.sqlclient.sqlcommand($SQLQuery,$Connection)
    $Connection.Open()
    $Adapter = New-Object System.Data.sqlclient.sqlDataAdapter $Command
    $Dataset = New-Object System.Data.DataSet
    $Adapter.Fill($Dataset) 
    $Connection.Close()
    $Dataset.Tables[0]
}

#Define configuration parameters
$Server="Abj-SQL-001"
$Database="WSS_Content_KM"
$AssemblyInfo="DealEventHandler, Version=1.2.6.0, Culture=neutral, PublicKeyToken=d256f51c842671b3"           

#Query SQL Server content Database to get information about the MissingAssembly
$Query = "SELECT distinct Id, SiteId, WebId, HostId, HostType from EventReceivers where Assembly = '"+$AssemblyInfo+"'"
$QueryResults = Run-SQLScript -SQLServer $Server -SQLDatabase $Database -SQLQuery $Query # | select Id, Name, SiteId, WebId, HostId, HostType

#Iterate through results
foreach ($Result in $QueryResults)
{
    if($Result.id -ne $Null)
    {
        #Get the location where the event receiver is referred
        if ($Result.HostType -eq 0) #Site Event Receiver
        {
            $Site = Get-SPSite -Limit all | where {$_.Id -eq $Result.SiteId}
            $EventReceiver = $Site.EventReceivers | where {$_.Id -eq $Result.Id}
            #To Delete the Event Receiver
            #$EventReceiver.Delete()
            write-host "Site Event Receivers Found at $($Site.URL)" -foregroundcolor green
        }
        if ($Result.HostType -eq 1) #Web Event Receiver
        {
            $Site = Get-SPSite -Limit all | where {$_.Id -eq $Result.SiteId}
            $Web = $Site | Get-SPWeb -Limit all | where { $_.Id -eq $Result.WebId }
            $EventReceiver = $Web.EventReceivers | where {$_.Id -eq $Result.Id}
            #To Delete the Event Receiver
            #$EventReceiver.Delete()
            write-host "Web Event Receivers Found at $($web.URL)" -foregroundcolor green
        }
        if ($Result.HostType -eq 2) #List Event Receiver
        {
            $Site = Get-SPSite -Limit all | where {$_.Id -eq $Result.SiteId}
            $Web = $Site | Get-SPWeb -Limit all | where { $_.Id -eq $Result.WebId }
            $List = $web.Lists | where {$_.Id -eq $Result.HostId}
            $EventReceiver = $List.EventReceivers | where {$_.Id -eq $Result.Id}
            #To Delete the Event Receiver
            #$EventReceiver.Delete()
            write-host "List Event Receivers Found at $($web.url)/$($List.RootFolder.Url)" -foregroundcolor green
        }
    }
} 
This script finds all locations where the particular assembly is referenced. Un-comment the EventReceiver.delete() line to delete the event receiver.

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Monday, January 18, 2016

Remove a Web Part from All Pages in SharePoint Site Collection using PowerShell

Requirement:
After the SharePoint migration from SharePoint 2013 to SharePoint 2016, decided to remove a broken web part from all pages wherever its being used.

PowerShell Script to
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
 
#Configuration parameters
$SiteURL = "http://intranet.crescent.com"
$WebPartTypeName="*BambooCalendar*"

#Get All Subsites in a site collection and iterate through each
$Site = Get-SPSite $SiteURL
ForEach($Web in $Site.AllWebs)
{

    Write-host Processing $Web.URL
    # If the Current Web is Publishing Web
    if ([Microsoft.SharePoint.Publishing.PublishingWeb]::IsPublishingWeb($Web))
    {
        #Get the Publishing Web 
        $PubWeb = [Microsoft.SharePoint.Publishing.PublishingWeb]::GetPublishingWeb($Web)
                   
        #Get the Pages Library
        $PagesLib = $PubWeb.PagesList  #Pages Library
     }
     else
     {
        $PagesLib = $Web.Lists["Site Pages"]
     } 

        #Iterate through all Pages  
        foreach ($Page in $PagesLib.Items | Where-Object {$_.Name -match ".aspx"}) 
        {
            $WebPartsToDelete = @()
            $PageURL=$web.Url+"/"+$Page.File.URL
            write-host $pageurl
            $WebPartManager = $Page.File.GetLimitedWebPartManager([System.Web.UI.WebControls.WebParts.PersonalizationScope]::Shared)
                 
            #Get All Web Parts of given type
            foreach ($WebPart in $WebPartManager.WebParts | Where-Object { $_.GetType().ToString() -like $WebPartTypeName} )
            {
                write-host "Found the Web Part at "$PageURL -f Green
                $WebPartsToDelete += $WebPart.ID
            }
            #If the web part to delete is found
            If($WebPartsToDelete)
            {
                #Checkout if required
                if ($Page.File.RequiresCheckout) 
                { 
                    if ($Page.File.CheckOutStatus -ne "None") 
                    {  
                        write-host "Overriding Checkout..."
                        $Page.File.UndoCheckOut()
                    }
                    write-host "Checking Out..."
                    $Page.File.CheckOut()
                }

                #Remove the web part from page          
                foreach ($WebPart in $WebPartsToDelete)
                {
                   Write-Host "Deleting Web Part on $($web.Url)/$($page.Url)"
                   $WebPartManager = $Page.File.GetLimitedWebPartManager([System.Web.UI.WebControls.WebParts.PersonalizationScope]::Shared)       
                   $WebPartManager.DeleteWebPart($WebPartManager.webParts[$webPart])
                }
                
                if ($Page.File.CheckOutStatus -ne "None") 
                { 
                    Write-host "CheckIn-In..."
                    $Page.File.CheckIn("Deleted web part")
                    $Page.File.Publish("")
                }

                if ($Page.ParentList.EnableModeration) 
                { 
                    write-host "Approving..."
                    $Page.File.Approve("") 
                }
            }
        }
}
This script iterates through all sites and pages in the entire site collection and removes the given web part.

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Sunday, January 17, 2016

Test-SPContentDatabase: Fix MissingWebPart Issue in SharePoint 2013 Migration

Problem: MissingWebPart Issues when running Test-SPContentDatabase cmdlet on SharePoint content database during SharePoint 2013 migration project.
Category                : MissingWebPart
Error                      : True
UpgradeBlocking  : False
Message                : WebPart class [9a43cf43-4500-d748-6385-09e622f28c01] is referenced [3] times in the database [WSS_Content_Database], but is not installed on the current farm. Please install any feature/solution which contains this web part.
Remedy                 : One or more web parts are referenced in the database [WSS_Content_Database], but are not installed on the current farm. Please install any feature or solution which contains these web  parts.

sharepoint 2013 test-spcontentdatabase missingwebpart
Root cause: MissingWebPart issue is because, the migrated SharePoint 2013 sites contains references to custom Web Parts which are not installed in the target Farm.

Solution:
Install & deploy the solution package (WSP) which contains the webpart to your new SharePoint 2013 farm to fix this issue! Well, that wouldn't be a case always, What if you don't really need the webpart anymore in your SharePoint and want to get rid it?

Find all pages referring the Web Part using PowerShell:
Use this PowerShell script to find all pages which are using the particular web part in your environment.
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

Function Run-SQLScript($SQLServer, $SQLDatabase, $SQLQuery)
{
    $ConnectionString = "Server =" + $SQLServer + "; Database =" + $SQLDatabase + "; Integrated Security = True"
    $Connection = new-object system.data.SqlClient.SQLConnection($ConnectionString)
    $Command = new-object system.data.sqlclient.sqlcommand($SQLQuery,$Connection)
    $Connection.Open()
    $Adapter = New-Object System.Data.sqlclient.sqlDataAdapter $Command
    $Dataset = New-Object System.Data.DataSet
    $Adapter.Fill($Dataset) 
    $Connection.Close()
    $Dataset.Tables[0]
}

#Define configuration parameters
$Server="Ab-SQL-001"
$Database="WSS_Content_KM_New"
$webPartID="90F70A36-4EC6-167D-792C-31B7EA83201B"

#Query SQL Server content Database to get information about the MissingFiles
$Query = "SELECT distinct ID,SiteId,DirName, LeafName, WebId, ListId from dbo.AllDocs where id in (select tp_PageUrlID from dbo.AllWebParts where tp_WebPartTypeId ='$($webPartID)')"
$QueryResults = Run-SQLScript -SQLServer $Server -SQLDatabase $Database -SQLQuery $Query | select Id , SiteId, DirName, LeafName, WebId, ListId 

#Iterate through results
foreach ($Result in $QueryResults)
{
    if($Result.id -ne $Null)
    {
        $Site = Get-SPSite -Limit all | where { $_.Id -eq $Result.SiteId }
        $Web = $Site | Get-SPWeb -Limit all | where { $_.Id -eq $Result.WebId }

        #Get the URL of the file which is referring the web part
        $File = $web.GetFile([Guid]$Result.Id)
        write-host "$($Web.URL)/$($File.Url)" -foregroundcolor green
    }
}
This script gets you the list of pages where the missing web part is being referenced from.

How to Delete Web Part from SharePoint Page? 
So you decided that the web part is no longer necessary and want to remove the web part from page? Well:
  • Get into WebPart Maintenance page by appending the query string "?contents=1" to the URL
  • In the web part maintenance page, Select and delete the unwanted web part!
How about removing a web part from the entire site collection or web application rather one by one? Well, PowerShell is your friend! Here is the PowerShell script to remove a web part from SharePoint site collection: Remove a web part from page using PowerShell in SharePoint.

Remember, as Microsoft says, even reading from SharePoint content databases isn't supported, use this PowerShell script to get all locations where a particular web part is being used. PowerShell to find the web part's usage in SharePoint

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Fix MissingSetupFile Error of Test-SPContentDatabase in SharePoint Migration

Problem: Getting "MissingSetupFile" errors when you run the "Test-SPContentDataBase" cmdlet on SharePoint content databases during a migration process.
[MissingSetupFile] File [Features\Feature-folder\feature-artifact] is referenced [1] times in the database [SharePoint_Content_database], but is not installed on the current farm. Please install any feature/solution which contains this file. One or more setup files are referenced in the database [SharePoint_Content_database], but are not installed on the current farm. Please install any feature or solution which contains these files.
sharepoint test-spcontentdatabase missingsetupfile
Root Cause:
In SharePoint migration, The source SharePoint 2010 environment sites contains references to some of the Feature files and those features are not installed in the target SharePoint 2013 Farm.

Solution:
Well, The resolutions is: Install & Deploy all solutions from the source farm to the destination farm. Your organization may have built & deployed several customization such as 3rd party products (such as bamboo solutions), custom WSP solutions, site definitions, Features, event receivers, Web Parts, user controls, etc. in the SharePoint 2010 environment and you have to install and deploy all these binaries & solutions to your destination SharePoint 2013 environment prior testing content databases for upgrade.

Deploy Solutions from Source Farm to the Destination farm:
  • Go to SharePoint Central Administration >> Navigate to "Solution Management" >> Manage Farm Solutions from System Settings of your SharePoint 2010 Farm (or any SharePoint source farm).
  • Make an inventory of all your WSP Solutions deployed to the farm. Identify the Web Applications to which these solutions are deployed to.
  • Download all WSP files and port it to the target farm. Deploy all of them in the target as per the source farm.
Instead of manually doing above steps, you can use PowerShell to automate:
  1. How to Extract & Download All WSP Solution Files in SharePoint
  2. How to Install & Deploy Multiple WSP Solutions using PowerShell in SharePoint
What if you don't need those missing feature artifacts such as web parts in your Target farm and cleanup missing setup files from the content database?

PowerShell script to find & delete missing setup files:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

Function Run-SQLScript($SQLServer, $SQLDatabase, $SQLQuery)
{
    $ConnectionString = "Server =" + $SQLServer + "; Database =" + $SQLDatabase + "; Integrated Security = True"
    $Connection = new-object system.data.SqlClient.SQLConnection($ConnectionString)
    $Command = new-object system.data.sqlclient.sqlcommand($SQLQuery,$Connection)
    $Connection.Open()
    $Adapter = New-Object System.Data.sqlclient.sqlDataAdapter $Command
    $Dataset = New-Object System.Data.DataSet
    $Adapter.Fill($Dataset) 
    $Connection.Close()
    $Dataset.Tables[0]
}

#Define configuration parameters
$Server="Ab-SQL-001"
$Database="SP10_Content_KM"
$SetupFile="Features\Crescent.Intranet.WebParts\WEFnews\WEFnews.dwp"

#Query SQL Server content Database to get information about the MissingFiles
$Query = "SELECT * from AllDocs where SetupPath like '"+$SetupFile+"'"
$QueryResults = @(Run-SQLScript -SQLServer $Server -SQLDatabase $Database -SQLQuery $Query | select Id, SiteId, WebId)

#Iterate through results
foreach ($Result in $QueryResults)
{
    if($Result.id -ne $Null)
    {
        $Site = Get-SPSite -Limit all | where { $_.Id -eq $Result.SiteId }
        $Web = $Site | Get-SPWeb -Limit all | where { $_.Id -eq $Result.WebId }

        #Get the URL of the file which is referring the feature
        $File = $web.GetFile([Guid]$Result.Id)
        write-host "$($Web.URL)/$($File.Url)" -foregroundcolor green
        
        #$File.delete()
    }
}
This script gets you all locations where the missing setup file is referenced (and optionally delete them!). Don't forget to set the variables $Server, $Database and $SetupFile appropriately!

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Saturday, January 16, 2016

Test-SPContentDatabase: Cannot open database [Database Name] requested by the login. The login failed.

Problem: 
In a SharePoint 2013 to SharePoint 2016 migration, Got this error message on testing the content database with Test-SPContentDatabase cmdlet  to identify possible issues:
"Cannot open database [Database Name] requested by the login. The login failed"
Test-SPContentDatabase: Cannot open database [Database Name] requested by the login. The login failed.
Troubleshooting:
  • If your databases are hosted on a different SQL Server or instance, other than the default SharePoint database server as its config database: Use -ServerInstance parameter with Test-SPContentDatabase cmdlet, because SharePoint always tries to find the database on the default database Server. E.g.
    Test-SpContentDatabase -name "WSS_Content_KM_New" -webapplication http://portal.crescent.com -ServerInstance "SQL001\SharePoint"
  • Test-SPContentDatabase is running from the context of the SharePoint Farm Administrator account. So make sure the Farm Account has enough access to the content database! Go to SQL Server >> Security >> Under Logins right click on your Farm Admin Account and select Properties. Under "User Mapping" tab >> Select your SharePoint content database >> Check the db_owner role.
  • Try running the PowerShell as an administrator.
If you get this "Cannot open database 'Database Name' requested by the login. The login failed" issue in case of orphaned databases. Follow this article to fix it: Cannot open database 'WSS_Content' Requested by the login. The login failed.

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Tuesday, January 12, 2016

Power Query to Extract SharePoint List Data using Excel

Microsoft Power Query is a Free add-in for Excel - Part of PowerBI that provides self-service Business Intelligence features. Lets see how to extract SharePoint list data using Power Query for Excel.

Step 1: Download & Install Power Query Add-in
To start with, Download the Power Query Add-in for Excel from: https://www.microsoft.com/en-us/download/details.aspx?id=39379

Once installed, You'll find a new tab "Power Query" tab added to Microsoft Excel.
power query and sharepoint 2013
Step 2: Connect to SharePoint List
To extract SharePoint list data, Click on "From Other Sources" button from the ribbon and choose "From SharePoint List"
power query excel 2013 sharepoint
Provide the URL of your SharePoint site and choose Authentication method accordingly - I Selected Windows. For Office 365, choose organizational credentials.

Once you are successfully logged in,  You'll get the Navigator page to choose your source list and click on "Load". By default, Only Lists appear in the Navigator.  Pick any list and use "Advanced Editor", change List name & "SharePoint.Tables" to "SharePoint.Contents" to get data from any library! power query connect to sharepoint list

Step 3: Remove Unnecessary columns, Perform ETL
This loads all columns from the SharePoint list. However, we may not be interested to fetch all columns. Also removing columns which are not required speeds up our data retrieval. So, Right click on the Query from Right side pane, Choose Edit.
power query sharepoint 2010 list
You'll get Power Query Editor window where you can:
  •  Add remove columns to your query -  in order to focus only on what we need
  • Merge-Append (Join-Union) from other lists
  • Perform ETL operations such as replace values, Split Columns, Group by etc.
power query for SharePoint
To Remove unnecessary columns, click on "Choose Columns" button and select only the columns that you need. Once done, click on "Load & Close" button from the ribbon. All activities inside the query editor are recorded as Steps. We can remove/Re-order them as required.
excel power query SharePoint list
To get actual values of Lookup columns, You'll have to "Expand" them. Once ready, click "Close and Load" from the Query Editor ribbon, and the list data will load to your workbook,
Although "Export to Excel" can retrieve data from SharePoint list, Power Query has much more capabilities. 

So next time, without the need to open SharePoint site in browser, You can just open the Excel file and Hit refresh! Once the data is loaded, We can utilize advanced features such as: Pivot Table, Pivot Chart, etc to output a variety of visual data in Excel worksheet.

I used this Power Query tool with SharePoint 2010 and SharePoint 2013.

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Monday, January 11, 2016

Find the Last Login Time of a SharePoint User from AD with PowerShell

Requirement: Find the last login time of all SharePoint users of  the farm to find out inactive users.

Solution: We can retrieve the user's last login time from Active directory (if the authentication provider is AD) using PowerShell.
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
Import-Module ActiveDirectory

$UserAccount="vani"
$User = Get-ADUser -Filter {SamAccountName -like $UserAccount} | Get-ADObject -Properties lastLogon 
  
$LogonTime=[DateTime]::FromFileTime($User.LastLogon)
Write-host $LogonTime
Lets add some more error handling and find last logon time of all users of SharePoint.

PowerShell Script to Find the SharePoint user last login time:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
Import-Module ActiveDirectory

#Custom Function Get the Last Logon Time of the User from AD 
Function Get-ADUserLastLoginTime($UserAccount)
{
    Try {
        #Set the Error Action
        $ErrorActionPreference = "Stop"

        $User = Get-ADUser -Filter {SamAccountName -like $UserAccount} | Get-ADObject -Properties lastLogon 
  
           if($user.LastLogon) 
           {
               return([DateTime]::FromFileTime($User.LastLogon))
           }
           else
           {
             return "Not Found!"
           }
        }
    catch {
          Write-Host $_.Exception.Message -ForegroundColor Red
        }
    finally {
          #Reset the Error Action to Default
          $ErrorActionPreference = "Continue"
        }
}
#Get All User Profiles
$SiteURL="https://intranet.crescent.com"
$ServiceContext  = Get-SPServiceContext -site $SiteURL
$UserProfileManager = New-Object Microsoft.Office.Server.UserProfiles.UserProfileManager($ServiceContext)
#Get All User Profiles
$UserProfilesColl = $UserProfileManager.GetEnumerator()

Foreach($UserProfile in $UserProfilesColl)
{
    $UserAccount=$UserProfile["UserName"]
    $LogonTime = Get-ADUserLastLoginTime -UserAccount $UserAccount
    Write-host "Last Logon Time of the User $UserAccount is $LogonTime"
}
Please note, SharePoint doesn't store Last Login time stamp. The above script pulls data from Active directory. So, data may be bit inconsistent - because if the user logon to any other systems like Exchange Server Email, AD will have that time stamp, doesn't matters if the user has logged on to SharePoint or not!

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Get All Web Applications in SharePoint using PowerShell

Web Applications are the top level containers in SharePoint. Each Web Application we create in SharePoint central administration is associated with site in IIS. 

To get a List of all available web applications in SharePoint, Navigate to:
  • SharePoint 2016 Central Administration >> Application Management 
  • Manage Web Application Link.
    how to get all web applications sharepoint using powershell
How to get all web applications SharePoint using PowerShell:
Get-SPWebApplication cmdlet retrieves all the Web Applications in the Current farm.
Get-SPWebApplication
get all web applications sharepoint powershell
The above PowerShell cmdlet gives DisplayName, Url of all SharePoint web applications.To get a specific web application, use:
Get-SPWebApplication http://yourwebappURL.com
You can Iterate through web applications in PowerShell, as:
#Get all web applications sharepoint using powershell
$WebAppColl = Get-SPWebApplication

#Iterate through each web application
Foreach ($WebApp in $WebAppColl)
{
    $Webapp.Url
}
#Or a one liner to loop through web applications
#Get-SPWebApplication | Select URL

Get all web application details in SharePoint using PowerShell
Lets audit all web applications in SharePoint with additional details such as Number of content databases, authentication type, application pool name, etc. and generate a report:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Configuration Parameters
$ReportOutput= "C:\WebApplications-Report.csv"
$DataCollection = @()

#Get all web applications sharepoint using powershell
$WebAppColl = Get-SPWebApplication
Foreach ($WebApp in $WebAppColl)
{
    #Determine the Authentication Type of the web application
    if ($WebApp.UseClaimsAuthentication) { $AuthticationTYpe = "Claims"} else {$AuthticationTYpe = "Classic" }

    #Get All Managed Paths of the web application
    $ManagedPaths =(Get-SPManagedPath -WebApplication $WebApp | Select -ExpandProperty Name) -join ","

    $WebAppData = new-object PSObject
    $WebAppData | add-member -membertype NoteProperty -name "Web Application Name" -Value $WebApp.Name
    $WebAppData | add-member -membertype NoteProperty -name "URL" -Value $WebApp.URL
    $WebAppData | add-member -membertype NoteProperty -name "No.of Content Databases" -Value $WebApp.ContentDatabases.Count 
    $WebAppData | add-member -membertype NoteProperty -name "Authentication Type" -Value $AuthticationTYpe
    $WebAppData | add-member -membertype NoteProperty -name "Application Pool" -Value $WebApp.ApplicationPool.DisplayName
    $WebAppData | add-member -membertype NoteProperty -name "Outgoing E-mail" -Value $WebApp.OutboundMailServiceInstance[0].Server.Address
    $WebAppData | add-member -membertype NoteProperty -name "Managed Paths" -Value $ManagedPaths

    $DataCollection += $WebAppData
}

#Export Results to a CSV File
$DataCollection | Export-csv $ReportOutput -notypeinformation
Write-Host "Web Application Audit Report has been Generated!" -f Green
This script generates the CSV report:
how to get all web applications sharepoint


You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Current Number of Site Collections is 0 in SharePoint After Database Attachment

Problem:
During a production SharePoint site cloning into UAT using Database attachment method, after attaching the content database to the web application in UAT environment, found the current current number of site collections is 0 (zero). All site collections missing! Tried "View All Site Collections"  from SharePoint 2013 Central Administration site, found nothing! No site collections listed.
sharepoint 2013 current number of site collections 0

Troubleshooting:
  1. As a first step in troubleshooting, installed patches to the UAT environment to bring it on the same patch level.
  2. Tried upgrading the database: Upgrade-SPContentDatabase <database_name>
  3. Tried refreshing the site map in the config db:
    $Database = Get-SPContentDatabase -Identity "Database_Name"
    $Database.RefreshSitesInConfigurationDatabase()
  4. None of the above helped! Finally, tried running Test-SPContentDatabase and found the answer!
    Test-SpContentDatabase -name "WSS_Content_KM_New" -webapplication http://intranet.crescent.com -ServerInstance "Abj-SQL-001" > Result.txt
    and the output of the above was the answer:
    current number of site collections 0 sharepoint
Root cause:
Site Collection GUIDs are unique to the SharePoint farm.  So, If a content database is restored even to a different Web Application on the same farm - Site collections will not appear if the farm has a duplicate GUID of the site collection. In my case, this issue was raised because of the errored content database in a previous attempt!

Solutions are:
  • Delete the existing site collections which are causing the conflicting GUIDs issue from the LOG file above.
  • Detach the existing content database which is triggering the duplicate issue.
  • Take the backup of the site collection and  restore it a different web application using again PowerShell/STSADM. How to Copy site collection in SharePoint
Also, Make sure while mounting the database, you specify right SQL server in case of multiple database servers in your environment. E.g. Mount-SPContentDatabase "WSS_Content_KM" -DatabaseServer "abs-SQL1" -WebApplication http://portal.crescent.com

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Sunday, January 10, 2016

Find All InfoPath List Forms in SharePoint using PowerShell

Requirement: Find All Customized InfoPath List Forms in SharePoint. Tasked to report a listing all the InfoPath List forms customized in SharePoint web application during a migration project.

PowerShell Script to Find All InfoPath List Forms in SharePoint
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue 

#Configuration parameters
$WebAppURL="https://intranet.crescent.com"
$ReportOutput="C:\InfoPath-ListForms.csv"
#Array to Hold Result - PSObjects
$ResultColl = @()

#Get All Webs of the Web Application
$WebsColl = Get-SPWebApplication $WebAppURL | Get-SPSite -Limit All | Get-SPWeb -Limit All 

#Iterate through each web
Foreach($Web in $WebsColl)
{ 
 #Get All Lists with InfoPath List Forms in use
 Foreach ($List in $web.Lists | Where { $_.ContentTypes[0].ResourceFolder.Properties["_ipfs_infopathenabled"]})
    {
            Write-Host "Found an InfoPath Form at: $($Web.URL), $($List.Title)"
            $Result = new-object PSObject
            $Result | add-member -membertype NoteProperty -name "Site URL" -Value $web.Url
            $Result | add-member -membertype NoteProperty -name "List Name" -Value $List.Title
            $Result | add-member -membertype NoteProperty -name "List URL" -Value "$($Web.Url)/$($List.RootFolder.Url)"
            $Result | add-member -membertype NoteProperty -name "Template" -Value $list.ContentTypes[0].ResourceFolder.Properties["_ipfs_solutionName"]
            $ResultColl += $Result
    }
} 
#Export Results to a CSV File
$ResultColl | Export-csv $ReportOutput -notypeinformation
Write-Host "InfoPath Lists Forms Report has been Generated!" -f Green
This script generates a CSV output report as below:
infopath list forms report in sharepoint

Related Post: Find all InfoPath Form Libraries in SharePoint

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Thursday, January 7, 2016

SharePoint Online: How to Import Term Set from CSV File

How to Import Term set from CSV file in SharePoint Online:
Here is how to import term sets from a CSV file.
  • Open SharePoint Online Admin center as Tenant administrator or SharePoint online Admin.  (E.g. https://yourdomain-admin.sharepoint.com)
  • Click on the "Term Store" link on the left navigation menu. 
  • On the Term store Management page, under General tab, click on "View a Sample Import File" link in "Sample Import" section.
    sharepoint online import termset terms from CSV
  • This gets you a sample CSV file. Open it in Excel, Populate the file as per your requirement. 
    sharepoint online term store import from csv
  • Once done, Import the CSV by clicking "Import Term Set" from any of the Term Set Group.
    import term set sharepoint online
    Browse and locate the updated CSV file and click "OK" to start importing term set.
  • That's all. Now your term store data should be updated from the CSV file.
    sharepoint online term set import




You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Wednesday, January 6, 2016

PowerShell to Add Entry in Web.config AppSettings

SharePoint Web applications (or any web site built on ASP.NET) uses Web.config file to store and retrieve web site configurations such as database connection strings, security, etc. web.config file is actually a XML file with .config extension, typically located at the root of your web application.

At times, You may have to add/update web.config entries. we can automate changes to SharePoint web application's web.config file using PowerShell. This PowerShell script gives you the ability to update web.config file.

PowerShell to Add entry in web.config's AppSettings section
AppSettings section in web.config can be utilized to read/update configuration values for your SharePoint customization.
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Config Parameters
$WebAppURL = "http://intranet.crescent.com"
$KeyName = "LicenseKey"
$KeyValue = "91e727402aa24e769e2779c155168e3"

#Get the web application
$WebApp = Get-SPWebApplication $WebAppURL

#Create new object for Web.config settings
$WebConfigModification = New-Object Microsoft.SharePoint.Administration.SPWebConfigModification
$WebConfigModification.Path = "configuration/appSettings"
$WebConfigModification.Name = "add[@key='$KeyName'][@value='$KeyValue']"
$WebConfigModification.Value = "<add key='$KeyName' value='$KeyValue' />"
$WebConfigModification.Sequence = 0
$WebConfigModification.Type = 0       
$WebConfigModification.Owner = "SharePointChartKey"
 
#Add "AppSettings" Entry to Web application's web.config
$WebApp.WebConfigModifications.add($WebConfigModification)
$WebApp.Update()
$WebApp.Parent.ApplyWebConfigModifications() 
This adds new entry to AppSettings section.
update web.config using powershell
Web.config changes doesn't require server reboot!

Please note, web.config file changes are always subject to risky! They may get lost if you stop "Microsoft SharePoint Foundation Web Application" service. As its machine specific, you'll have to repeat the changes in every WFE in a multi server environment.

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Monday, January 4, 2016

How to Move SharePoint Databases to a New SQL Server

Requirement: Move SharePoint databases to new SQL Server! You'll have to move SharePoint databases including all content databases, configuration databases and service application databases to an another SQL Server at least in a couple of circumstances:
  • Upgrading SQL Server to new version. E.g. Upgrading SQL  Server 2012 to SQL Server 2016. 
  • Upgrading Servers to new hardware, moving servers from data centers, etc.
  • Moving from single standalone server instances to highly available cluster servers - AlwaysOn 
Solution:
In a nutshell, Moving SharePoint database from one server to another can be achieved with following steps:
  • Step 1: Stop All SharePoint Services:
  • Step 2: Backup & Restore All SharePoint Databases to the new SQL Server
  • Step 3: Change the SQL Alias to Point to the new SQL Server
  • Step 4: Start All SharePoint Services
Step 1: Stop All SharePoint Services:
To start with moving SharePoint databases to new server, Stop all SharePoint related services on all SharePoint servers of the farm, So that no connection are made to SQL server during the migration. Use this PowerShell script to stop all SharePoint 2013 services: How to Stop all SharePoint 2013 Services using PowerShell

Step 2: Backup & Restore All SharePoint Databases to the new SQL Server
Use the PowerShell cmdlet "Get-SPDatabase" to get all SharePoint databases and make a note of them. Proceed with backup each database.

Backup SharePoint Databases in SQL Server: 
  • Go to SQL Server Management Studio >> Right click on the relevant SharePoint database >> Tasks > Back Up.
  • Provide necessary inputs to the backup dialog window and complete backup for all SharePoint databases.
Restore SharePoint Database backups to the New database server: 
You have to make sure your Farm access account & service accounts have access to the target SQL Server! so before restore, Transfer all Logins from old server to new server: https://support.microsoft.com/en-us/kb/918992. This is important as the user access mappings are within the backup we made and should be restored.
  • I'm assuming SQL Server is installed on your new SQL Server, Login to your new SQL Server Database Server, Transfer all Backup files from Old SQL Server to your new SQL Server.
  • Go to SQL Server management studio >> Right click on Databases node > Restore Database >> Pick the database backup. Repeat this step for all database backups.
Other than backup-restore, You can also use copy database wizard to copy databases from one SQL server to another. Right click the source database from SSMS >> Tasks >> Copy Database >> Run through the Wizard to copy database between SQL Servers. Alternatively, you can detach and attach to move SQL Databases between servers.
Its a best practice to use SQL Alias to connect SharePoint to SQL Server!

Step 3: Create/Change the SQL Alias to Point to the new SQL Server 
Once SharePoint databases are restored into the target SQL server, the next step is to create or change SQL Server Alias on All SharePoint Servers of the Farm. Basically, we will redirect from the original SQL server to the new SQL server.
  • Open the cliconfg.exe tool >> Under Alias tab, add a new alias or change existing alias for your SharePoint farm (If your SharePoint farm uses SQL Alias already).  
  • In my case: My old SQL Server is 'AzSvrV18' and I want to move all Databases to 'SP16-SQL'. After restoring all SharePoint Databases on SP16-SQL, I created an alias on all SharePoint servers to make 'AzSvrV18' point to 'SP16-SQL'.
    Move SharePoint database from one server to another
Here, we have created an alias for the new SQL instance and gave this alias the same name as the current instance of SQL Server that the SharePoint databases are hosted. Please note, alias can only be configured after the original SQL instance is stopped or Turned OFF to avoid any conflicts. Refer this article to configure SQL alias for SharePoint: How to Create SQL Server Alias for SharePoint

Step 4: Start All SharePoint Services 
Start all the services that we stopped. Use the article at Step-1 to start all SharePoint services.

That's it! We have moved SharePoint database to new server. Pretty straight forward, huh! Next time you want to change your Database server, Just update the Server name in SQL Alias! No need to run SharePoint configuration wizard and disconnect from farm and so on! You can use this approach for other requirements such as: move SharePoint database from express to SQL server, move SharePoint database from express to standard. Technet article on moving SharePoint 2013 databases: https://technet.microsoft.com/en-us/library/cc512725.aspx

Related post: Move SharePoint 2013 database to another drive

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Sunday, January 3, 2016

SharePoint Content Databases - Top 10 Frequently Asked Questions

Content databases are the single most significant artifacts in SharePoint. Content databases are the repository where all SharePoint content lives. Let me summarize some of the frequently asked questions on SharePoint content databases.

1: Can a Site Collection spitted into one or more Content Databases?
No! This is by design limitation. Any SharePoint content database can have one or more site collection, but the reverse is not possible. One Site collection cannot be spitted into more than one content database.

2: How to get all content Databases of a my SharePoint Farm?
To get all content databases which are in use:
Get-SPDatabase | Sort-Object Name | Format-Table Name 
Get All SharePoint Databases - including which are not currently attached to SharePoint web apps:
Get-SPContentDatabase | Sort-Object Name | Format-Table Name 

3. SharePoint content database recommended size?
Recommended size: 100 GB. 200GB Max for SharePoint 2016! In special scenarios, up to 4TB! Refer: https://technet.microsoft.com/en-us/library/cc262787(v=office.16).aspx#ContentDB. and you can have upto 500 content databases per SharePoint 2016 farm!

4. How to Get the Content Database Size?
Content database size can be retrieved using this PowerShell:
[Math]::Round(((Get-SPContentDatabase "SP16_Content_Crescent_Intranet").DiskSizeRequired)/1GB,2)
To get all Database sizes in E-mail, use this PowerShell script: SharePoint Content Databases Size Report

5. How to get all Content Databases of a SharePoint Web Application?
Go to: SharePoint 2016 Central Administration >> Application Management >> Manage Content databases >> Pick your web application to get list of content databases associated.
You can get all content databases of a web application using PowerShell:
(Get-SPWebApplication "http://intranet.crescent.com/").ContentDatabases | Select -ExpandProperty Name

6. How to Find in which content database a specific site collection is stored?
To get in which content database site collection lives, Go to SharePoint 2013 Central Administration >> Application Management >> View all site collections >> Pick your site collection. Now, you'll find the content database of the site collection as in below screen:
Use this PowerShell script to find the content database of the site collection:
(Get-SPSite "http://intranet.crescent.com").ContentDatabase.Name
To get the content database for each site collection, refer: Get Content Database for All Site collections

7. How to get all Site collections in a content database?
To find all SharePoint site collections which are stored inside a content database, use this PowerShell script:
(Get-SPContentDatabase "SP16_Content_Crescent_Intranet").Sites | Select URL
Refer this post to get all site collections of all content databases: Get All SharePoint Site Collections in All Content Databases

8. Can I create a Site Collection in a particular Content Database?
Yes! You can. By default, SharePoint uses the logic: Largest difference between the Maximum Number of Site Collections and the Current Number of Site Collections to create site collections. You can't specify a particular content database through SharePoint Central Administration. However, Using PowerShell, You can create a site collection in a specific content database: Create Site Collection in New/Specific Content Database

9. How to move a Site collection from one Content Database to Another?
Use: Move-SPSite cmdlet to move a SharePoint site collection from one content database to another. Refer this post for more detailed instructions: Move site collection from One Content Database to Another

10. How to set SharePoint content database to read-only?
You can set SharePoint content database to read only either from SQL Server Management Studio or PowerShell. Refer: How to Set SharePoint 2013 Content Database to Read Only Mode

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


Saturday, January 2, 2016

Fix SharePoint Lookup Column Values Blank Issue using PowerShell

Problem: SharePoint lookup columns are broken in below scenarios:
  • If the parent lookup list is deleted and recreated - Then the connection between lookup list and the lookup column will be broken and you'll get empty values!
  • When you export-import a subsite or lists, lookup fields become orphaned from its parent list!
  • When you save a lists as list templates and use it in another site, the lookup column no longer works - even though you created the parent lookup list in the target site!
Root Cause: SharePoint lookup columns are connected with its parent lookup list column through WebID and ListID GUIDs which are generated every time when the object is recreated. So, when you export-import or re-create the parent lookup lists, this connectivity is broken.
sharepoint lookup column empty value
Solution:
If you come across any of the above scenario and your lookup column is broken, you have to re-link the lookup column with its parent list. Use this PowerShell script to fix the lookup columns in SharePoint.

PowerShell Script to fix SharePoint 2013 Lookup Column Blank Values:
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue

#Configuration Parameters
$SiteURL="http://intranet.crescent.com/"
$ListName="Project Milestones" 
$LookupColumnName="Parent Project"
$LookUpListName="Projects" #Parent List 

#Get the Objects
$Site = Get-SPWeb $SiteURL
$List = $web.Lists[$ListName]
$LookupList = $web.Lists[$LookUpListName]
$Column = $List.Fields[$LookupColumnName]

#Update column schema
$Column.SchemaXml = $Column.SchemaXml.Replace($Column.LookupWebId.ToString(), $Web.ID.ToString())
$Column.SchemaXml = $Column.SchemaXml.Replace($Column.LookupList.ToString(), $LookupList.ID.ToString())
$Column.Update()

Write-host "Lookup column fixed!" -f Green

This fixes SharePoint 2013 lookup column blank values issue.

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.
*Sponsored


You might also like:

Related Posts Plugin for WordPress, Blogger...