Wednesday 4 September 2013

Restore Content DB from .bak

This is a powershell script to restore a content database on a Dev Box, make a note that in the restoreDB function you have to specify your path to the .mdf and .ldf files on your local Dev box

LoadSnapIn "Microsoft.Sharepoint.PowerShell"
LoadSnapIn "SQLServerProviderSnapin100"
LoadSnapIn "SQLServerCmdletSnapin100"
   CCW Function to locally replace SP database
   City of Windsor Fucntion used to replace a SharePoint Content Database with a previously created back up
   Restore-ccwSpContentDB -DataBaseName Test_DB -DatabaseServer SSRAP1 -DBFilePath c:\Test_DB.bak
   Restore-ccwSpContentDB -DataBaseName Test_DB -DatabaseServer SSRAP1 -DBFilePath c:\Test_DB.bak -SecondarySiteAdministrator windsor\pricer
   Restore-ccwSpContentDB -DataBaseName Test_DB -DatabaseServer SSRAP1 -DBFilePath c:\Test_DB.bak -SecondarySiteAdministrator windsor\pricer -PrimarySiteAdministrator windsor\ciuciap
   Inputs to this cmdlet (if any)
   General notes
   The role this cmdlet belongs to
   The functionality that best describes this cmdlet
function Restore-ccwSpContentDB
        # XML file containing deploymnet
        [Parameter(Position=0, Mandatory=$true, ParameterSetName="XML")]
        [string] $xmlPackage,

        # Database name
        [Parameter(Position=0, Mandatory=$true, ParameterSetName="CommandLine")]
            [ValidateScript({$dbName = $_; Invoke-SQLCmd -Query "sp_databases" | ?{$_.DataBase_Name -eq $dbName}})]
        [string] $DataBaseName,

        # Backup file path
        [Parameter(Position=1, Mandatory=$true, ParameterSetName="CommandLine")]
            [ValidateScript({Test-Path $_ -pathtype leaf -Include "*.bak"})]
        [string] $DBFilePath,

            # Site Url to replace the database on with a backup
        [Parameter(Position=2, ParameterSetName="CommandLine")]
            [ValidateScript({Get-SPSite -Identity $_ | Select-Object -Property Exists -ErrorAction SilentlyContinue})]
        [string[]] $SiteUrl,
            # DatabaseServer
        [Parameter(Position=3, Mandatory=$true, ParameterSetName="CommandLine")]
            [ValidateScript({Test-Connection $_})]
        [String] $DatabaseServer,
        # Primary Site Admin (Domain/User)
        [Parameter(Position=4, ParameterSetName="CommandLine")]
        $PrimarySiteAdministrator = $null,

        # Secondary Site Admin (Domian/User)
        [Parameter(Position=5, ParameterSetName="CommandLine")]
        $SecondarySiteAdministrator = $null

            [string[]] $spServices = "SPAdminV4", "SPTimerV4"
        #if site app url is not proviede as a parameter, prompt the user to pick one from the current farm
            if($SiteUrl -eq $null -or $SiteUrl -eq "")
                  $myFarmServices = (Get-SPFarm).services | Where {$_.typename -like "*Web Application"}
                  $selection = -1
                  [string[]]$Apps = @()
                        $count = 0
                        foreach($webApp in $myFarmServices.WebApplications){
                              Write-output ("{0}) {1}" -f $count++, $webApp.url)
                              $Apps += $webApp.url
                        Write-output ("{0}) All" -f $count)
                        if(($selection = Read-Host "Please select the web app you'd like to deploy to, -1 to quit") -eq -1){
                              write-error ("Terminated cmdlet, user request") -erroraction stop
                  }while($selection -lt 0 -or $selection -gt $count)
                  if($selection -eq $count){
                        $SiteUrl = $Apps
                        $SiteUrl = $Apps[$selection]
        if ($pscmdlet.ShouldProcess("dataBase $DataBase_Name for site $SiteUrl", "Restore"))
                  Write-Progress -Activity "Restoring $newDBName" -Status "dismounting $newDBName from $SiteUrl" -PercentComplete 0
                  Get-SPContentDatabase $DataBaseName | Dismount-SPContentDatabase
                  Write-Progress -Activity "Restoring $newDBName" -Status "Stoping services: $spServices" -PercentComplete 20
                  Stop-Service $spServices
                  Write-Progress -Activity "Restoring $newDBName" -Status "Stoping IIS" -PercentComplete 30
                  iisReset -Stop
                  Write-Progress -Activity "Restoring $newDBName" -Status "Restoring Database (this may take up to 10 min)" -PercentComplete 40
                  RestoreDB $DataBaseName $DBFilePath
                  Start-Sleep -s 10
                  Write-Progress -Activity "Restoring $newDBName" -Status "Starting IIS" -PercentComplete 75
                  iisReset -Start
                  Write-Progress -Activity "Restoring $newDBName" -Status "Starting services: $spServices" -PercentComplete 80
                  Start-Service $spServices
                  Write-Progress -Activity "Restoring $newDBName" -Status "Mounting $DataBaseName on $SiteUrl" -PercentComplete 90
                  Mount-SPContentDatabase $DataBaseName -DatabaseServer $DatabaseServer -WebApplication "$SiteUrl"
                  if($PrimarySiteAdministrator -ne $null -or $SecondarySiteAdministrator -ne $null)
                        Write-Progress -Activity "Restoring $newDBName" -Status "Changing site admin(s)" -PercentComplete 95
                        ChangeSiteCollectionAdministrators $PrimarySiteAdministrator $SecondarySiteAdministrator $SiteUrl
                  Write-Progress -Activity "Restoring $newDBName" -Status "Complete!" -PercentComplete 100

function LoadSnapIn($snapIn){
      if (!(Get-PSSnapin | ?{$ -eq $snapIn}))
            if(Get-PSSnapin -registered | ?{$ -eq $snapIn})
                  Add-PsSnapin $snapIn
                  Write-Output "$snapIn has been added to this session"
                  write-output "$snapIn is not registered with the system."
            write-output "$snapIn has already been added to this session"

function get-SiteUrl([string]$webUrl){
        return $webUrl.Substring(0, $webUrl.IndexOf('/',8))
        return $webUrl.Substring(0, $webUrl.IndexOf('/',7))

function RestoreDB ([string] $newDBName, [string] $backupPath){
    [string] $dbCommand_SingleUser = "ALTER DATABASE $newDBName SET SINGLE_USER WITH Rollback Immediate "
      Invoke-Sqlcmd -Query $dbCommand_SingleUser

      [string] $dbCommand_FileListOnly = "RESTORE fileListOnly FROM disk = '$backupPath'"
      Invoke-SQLCmd -Query $dbCommand_FileListOnly -ServerInstance "SSRAP1" -OutVariable t

      $NameData = ($t | where {$_.Type -eq "D"} | select LogicalName).LogicalName 
      $NameLog = ($t | where {$_.Type -eq "L"} | select LogicalName).LogicalName
      [string] $dbCommand_Restore = "RESTORE DATABASE $newDBName FROM DISK = '$backupPath' WITH MOVE '$NameData ' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.INSTANCEID\MSSQL\DATA\$newDBName.mdf', MOVE '$NameLog ' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.INSTANCEID\MSSQL\DATA\$newDBName.LDF', REPLACE"

      Invoke-Sqlcmd -Query $dbCommand_Restore  -querytimeout ([int]::MaxValue)
      [string] $dbCommand_MultiUser = "ALTER DATABASE $newDBName SET Multi_User"
      Invoke-Sqlcmd -Query $dbCommand_MultiUser 

function ChangeSiteCollectionAdministrators([string] $primarySiteAdmin, [string] $secondarySiteAdmin, [string] $siteUrl){
    Write-output "Changing the site collection administrators "
      $site = Get-SPSite $siteUrl
    If ($site -ne $null)
            if($PrimarySiteAdministrator -ne $null -and $SecondarySiteAdministrator -ne $null)
            Set-SPSite -Identity $site.Url -OwnerAlias $PrimarySiteAdministrator -SecondaryOwnerAlias $SecondarySiteAdministrator
            Write-Output "Primary Administrators for '$site' is $PrimarySiteAdministrator"
                  Write-Output "Secondary Administrators for '$site' is $SecondarySiteAdministrator"
            elseif($PrimarySiteAdministrator -ne $null)
                  Set-SPSite -Identity $site.Url -OwnerAlias $PrimarySiteAdministrator
            Write-Output "Primary Administrators for '$site' is $PrimarySiteAdministrator"
            elseif($SecondarySiteAdministrator -ne $null)
                  Set-SPSite -Identity $site.Url -SecondaryOwnerAlias $SecondarySiteAdministrator
            Write-Output "Secondary Administrators for '$site' is $SecondarySiteAdministrator"