SharePoint 2013 alignment (1/?)

I have wondering on what I should write next on my blog, and came on a little idea on a serie on how to align two SharePoint 2013 environments.
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:

1. Backup (copy style) of the production content database
2. Check the backup files
3. Dismount the content databases
4. Restore the content databases
5. Assign new site collection admins
6. Migrate groups and users. (If your changing AD domain)
7. Migrate service applications
1. Manage metadata
2. ... Please ask for a service application, you like me to script. 

So the way I like to make my scripts is making a master script and a funktion script. But it could be the same. I also like to make a configuration file with all my variables.

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
  }
 }
}