This is a powershell script to restore a content database on a Dev Box, make a note that
LoadSnapIn "Microsoft.Sharepoint.PowerShell"
LoadSnapIn "SQLServerProviderSnapin100"
LoadSnapIn "SQLServerCmdletSnapin100"
<#
.Synopsis
CCW Function to locally replace SP database
.DESCRIPTION
City of Windsor Fucntion used to replace a SharePoint Content Database
with a previously created back up
.EXAMPLE
Restore-ccwSpContentDB -DataBaseName Test_DB -DatabaseServer SSRAP1
-DBFilePath c:\Test_DB.bak
.EXAMPLE
Restore-ccwSpContentDB -DataBaseName Test_DB -DatabaseServer SSRAP1
-DBFilePath c:\Test_DB.bak -SecondarySiteAdministrator windsor\pricer
.EXAMPLE
Restore-ccwSpContentDB -DataBaseName Test_DB -DatabaseServer SSRAP1
-DBFilePath c:\Test_DB.bak -SecondarySiteAdministrator windsor\pricer
-PrimarySiteAdministrator windsor\ciuciap
.INPUTS
Inputs to this cmdlet (if any)
.OUTPUTS
None
.NOTES
General notes
.COMPONENT
Pawel
.ROLE
The role this cmdlet belongs to
.FUNCTIONALITY
The functionality that best describes this cmdlet
#>
function Restore-ccwSpContentDB
{
[CmdletBinding(DefaultParameterSetName='CommandLine',
ConfirmImpact='High')]
Param
(
# 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")]
[ValidateNotNullOrEmpty()]
$PrimarySiteAdministrator = $null,
# Secondary Site Admin (Domian/User)
[Parameter(Position=5,
ParameterSetName="CommandLine")]
[ValidateNotNullOrEmpty()]
$SecondarySiteAdministrator = $null
)
Begin
{
Clear-Host
[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 = @()
do{
$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
}
else{
$SiteUrl = $Apps[$selection]
}
}
}
Process
{
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
}
}
End
{
}
}
function LoadSnapIn($snapIn){
if (!(Get-PSSnapin | ?{$_.name -eq $snapIn}))
{
if(Get-PSSnapin -registered | ?{$_.name -eq $snapIn})
{
Add-PsSnapin $snapIn
Write-Output "$snapIn
has been added to this session"
}
else
{
write-output "$snapIn
is not registered with the system."
break
}
}
else
{
write-output "$snapIn
has already been added to this session"
}
}
function get-SiteUrl([string]$webUrl){
if($webUrl.StartsWith("https")){
return $webUrl.Substring(0, $webUrl.IndexOf('/',8))
}
elseif($webUrl.StartsWith("http")){
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"
}
}
$site.Dispose()
}
