Currently I’m working on a web project with a few environments like Dev, POC, QA, UAT, Staging, etc. Once per sprint we have to synchronize our DBs from Staging to other servers to make sure all environments are on the same page.

To achieve this, a new build definition was created in TFS which is similar to the one described in my “Run Batch File From TFS” post. All that build does is invoke a PowerShell script.

I’ll describe the restore process for our QA server. The PowerShell script has to do the following things:

1. Call the “iisreset /stop” command on the QA server

2. Restore DBs on the SQL server

3. Call the “iisreset /start” command on the QA server

Let’s take a closer look at these steps.

Steps 1 and 3 simply invoke commands.

The challenge is to call commands on a remote server while the script is running on a local build server. PowerShell allows remote calls to be made using the
Invoke-Command cmdlet.

Now the ‘iisreset’ command will look like this:

# credentials
$userName = "user_name"
$password = convertto-securestring "user_passwd" -asplaintext -force
# servers
$QAserverName = "QAServwrName"
$cred = new-object -typename System.Management.Automation.PSCredential -argumentlist $userName, $password

# should be invoked on QA server
Invoke-Command -computername $QAserverName -Credential $Cred -Authentication Credssp -ScriptBlock {

    write-host "Stopping QA server"
    iisreset /stop

}

Unfortunately, my first remote run failed and displayed this message:

Connecting to remote server failed with the following error
message : The WinRM client cannot process the request. A computer policy does n
ot allow the delegation of the user credentials to the target computer. Use gpe
dit.msc and look at the following policy: Computer Configuration -> Administrat
ive Templates -> System -> Credentials Delegation -> Allow Delegating Fresh Cre
dentials. Verify that it is enabled and configured with an SPN appropriate for
the target computer. For example, for a target computer name "myserver.domain.
com", the SPN can be one of the following: WSMAN/myserver.domain.com or WSMAN/*
.domain.com. For more information, see the about_Remote_Troubleshooting Help to

To fix this problem, I enabled credential delegation on the build server:

PS C:\> enable-wsmancredssp -role client -delegatecomputer server_name

PS C:\> enable-wsmancredssp -role server

2. DB Restore

I used Microsoft.SqlServer.Management.Smo for the DB restore and had to call the script on the remote server by again using the Invoke-Command cmdlet.

# should be invoked on QA server
Invoke-Command -computername $QAserverName -Credential $Cred -Authentication Credssp -ScriptBlock {

# SQL credentials 
 $userNameSQL = "user"
 $passwordSQL = "passwd"

 # paths
 $backupsFromAdminDir = "\\some_backup_dir"
 $tempSQLServerDir = "D:\Temp\"

 # master database info
 $masterDBName = "QA_Master" 
 $masterMDFLogicalFileName = "Sitecore.Master.Data"
 $masterMDFPhysicalFileName = "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\QA_Master.mdf"
 $masterLDFLogicalFileName = "Sitecore.Master.Log"
 $masterLDFPhysicalFileName = "D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\QA_Master_1.ldf"


 $adminMasterDBNameMask = "ADMIN_Master*.bak"
 $adminWebDBNameMask = "ADMIN_Web*.bak"

 [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
 [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
 [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
 [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum")

 # copy find te latest bkp file
 write-host "Selecting BAK files on Admin server"
 # master
 $MasterDB = Get-ChildItem -Path $backupsFromAdminDir -Filter $adminMasterDBNameMask | Sort CreationTime -Descending | Select -First 1
 $MasterDBLocalPath = $tempSQLServerDir+$MasterDB.Name
 write-host "--Master DB: " $MasterDB.Name
 # web

 #copy bkp file to local SQL server
 write-host "Copying BAK files to SQL server"
 # create temp dir
 New-Item -ItemType Directory -Force -Path $tempSQLServerDir | Out-Null
 Copy-Item -Path $MasterDB.FullName -Destination $tempSQLServerDir -Force 

 
 # connecting to SQL server
 write-host "Connecting to SQL server"
 $server = new-object ("Microsoft.SqlServer.Management.Smo.Server") "(local)"
 $server.ConnectionContext.LoginSecure=$false;
 $server.ConnectionContext.set_Login($userNameSQL);
 $server.ConnectionContext.set_Password($passwordSQL)

 # restore master DB
 $server.KillAllProcesses($masterDBName)
 $Database = $server.Databases.Item($masterDBName)
 write-host "Restoring " $Database.Name " database on SQL server..."
 $dbRestore = new-object ("Microsoft.SqlServer.Management.Smo.Restore")
 $dbRestore.Database = $masterDBName
 $dbRestore.Devices.AddDevice($MasterDBLocalPath , "File")
 $dbRestoreFile = new-object("Microsoft.SqlServer.Management.Smo.RelocateFile")
 $dbRestoreLog = new-object("Microsoft.SqlServer.Management.Smo.RelocateFile")
 $dbRestoreFile.LogicalFileName = $masterMDFLogicalFileName
 $dbRestoreFile.PhysicalFileName = $masterMDFPhysicalFileName 
 $dbRestoreLog.LogicalFileName = $masterLDFLogicalFileName
 $dbRestoreLog.PhysicalFileName = $masterLDFPhysicalFileName
 $dbRestore.RelocateFiles.Add($dbRestoreFile)
 $dbRestore.RelocateFiles.Add($dbRestoreLog)
 $dbRestore.SqlRestore($server)
 write-host "Restored " $Database.Name " database on SQL server...."

 #Disconnect explicitly
 $server.ConnectionContext.Disconnect() 

 # delete master temp DB
 If ((Test-Path $MasterDBLocalPath) -and ($MasterDBLocalPath -like '*'+$adminMasterDBNameMask))
 { Remove-Item $MasterDBLocalPath -Force -Recurse}


}