We are all making new code in an test- or development environment. And for that purpurse we need to establish some valid data in the environments.
So this serie will cover all the steps to create a complete script that can take the production data, and restore it on an test/dev server.
So we should first cover our steps, before we can make a script. And if you have any comments I really like to hear your points. This serie will target those that dont have a deep knowledge in PowerShell and are looking for a copy paste solution.
So the steps are as follow:
- Backup (copy style) of the production content database
- Check the backup files
- Dismount the content databases
- Restore the content databases
- Assign new site collection admins
- Migrate groups and users. (If your changing AD domain)
- Migrate service applications
- Manage metadata
- ... Please ask for a service application, you like me to script.
Lets make 3 files.
- RunningScript.ps1
- Functions.ps1
- Configuration.xml
So lets have a close look at how we build the XML file.
I have build mine with all the server settings.
<?xml version="1.0"?>
<Settings>
<ServerSettings>
<WebApp>http://contoso.com</WebApp>
<SQLServerFrom>SQLsServer\PRODinstance</SQLServerFrom>
<SQLServerTo>SQLsServer\TESTinstance</SQLServerTo>
<SiteColAdmin>contoso\administrator</SiteColAdmin>
<OldADDomain>ContosoPROD</OldADDomain>
<NewADDomain>ContosoTEST</NewADDomain>
<SQLBackupJob>SQL-Backup-JOB</SQLBackupJob>
<SQLRestoreJob>SQL-Restore-JOB</SQLRestoreJob>
</ServerSettings>
So the first step is to take the backup of the produktion database. This is bedst done by using the SMO PowerShell module.
To install the SMO please look at ...
The first part of the script we need to implement the use of the Functions.ps1 and Configuration.xml file. Note that I am using a "main" function. this is done to get an overview of the running script, and to be able to place it at the top of the script.
Using the RunningScript.ps1
function main
{
cls
Write-Host -ForegroundColor Blue "Starting AligmentScript"
$bits = pwd
. "$bits\Functions.ps1"
[xml]$xmlConfiguration = Get-Content "$bits\Configuration.xml"
Run-SQL-Job "$xmlConfiguration.Settings.ServerSettings.SQLServerFrom" "$xmlConfiguration.Settings.ServerSettings.SQLBackupJob"
{
After in adding the script path, I include the configuration file.
Note: There are many ways to include your script path, I use the "pwd" command, but this dose not always work.
after including the files, I use the function [Run-SQL-Job]. This function is used for both backup and restore. Do mind that the account running the script need access to the production server.
The function looks like this:
function Run-SQL-Job
(
$instancename,
$jobname
)
{
$db = "MSDB"
try
{
$sqlConnection = new-object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = 'server=' + $instancename + ';integrated security=TRUE;database=' + $db
$sqlConnection.Open()
Start-Sleep -s 1
$sqlCommand = new-object System.Data.SqlClient.SqlCommand
$sqlCommand.CommandTimeout = 120
$sqlCommand.Connection = $sqlConnection
$sqlCommand.CommandText = "EXEC dbo.sp_start_job N'$JobName'"
Write-Host "Executing Job => $jobname..."
$result = $sqlCommand.ExecuteNonQuery()
$sqlConnection.Close()
}
catch
{
Write-Eventlog -Logname 'Application' -Source 'Application' -EventID 19002 -EntryType Error -Message "Start SQL job :$jobname Failed"
}
# Preload Sql server SMO Assemblies
$null = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Connect ionInfo")
$null = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum ")
$null = [reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
# Initilize SMO Objects
$server = New-Object Microsoft.SqlServer.Management.Smo.Server $instancename
$i = 0
while ($i -ne 100)
{
$agent = $server.JobServer.Jobs
if($agent){$i = 100}
else
{
Start-Sleep -s 1
$i += 1
}
}
$Restore = $agent | Where-Object {$_.Name -eq $jobname}
$Today = Get-Date
$continue = $true
# Get Time Value to Start and Stop
$ActiveDateTime=[datetime]::Parse((Get-Date -Format 'HH:MM'))
$StopDateTime=$ActiveDateTime.addminutes(1)
$StopTime=$StopDateTime.toString("HH:MM")
if ($Restore -eq $null)
{
Write-host "$jobname does not exist on $instancename"
$continue = $false
}
if ($continue){write-host -ForegroundColor Blue "$jobname Job is Executing. Please Wait.." -NoNewline}
while ($continue)
{
$Restore.Refresh()
# if Job Completed Successfully
if ($Restore.CurrentRunStatus.value__ -eq 4 -and $Restore.LastRunDate -gt $Today.AddDays(-1) -and $Restore.LastRunOutcome -eq "Succeeded")
{
Write-host "$jobname complete."
$continue = $false
}
# if Job is curently executing
elseif ($Restore.CurrentRunStatus.value__ -eq 1)
{
Write-host -ForegroundColor Blue "." -NoNewline
start-Sleep -s 5
}
# if Job Suspended
elseif ($Restore.CurrentRunStatus.value__ -eq 5)
{
Write-host "$jobname Job has been suspended."
$continue = $false
}
# if Job Cancelled
elseif ($Restore.CurrentRunStatus.value__ -eq 4 -and $Restore.LastRunOutcome -eq "Cancelled")
{
Write-host "$jobname Job has been Cancelled"
$continue = $false
}
}
}
