Home > PowerShell, SharePoint 2010, SharePoint 2010 Maintenance > Mirror Staging SharePoint 2010 Environment with SharePoint 2010 Production Environment by Moving Over Content Databases using PowerShell

Mirror Staging SharePoint 2010 Environment with SharePoint 2010 Production Environment by Moving Over Content Databases using PowerShell

To those that maintain both a Production and a Staging SharePoint 2010 environment know the importance of trying to have both environments balanced (mirrored) to allow for a “true” production type environment in a staging environment.  This allows for “true” testing of new services/deployments/solutions, patching before  deploying to production.

Of course you can implement 3rd party solutions (Metalogix, Doc Ave, Syntergy) to allow for a more scheduled (automation) technique to maintain your staging enviornment to reflect production, if you choose to go that direction that is fine.  However I will be blogging on how you can accomplish the same thing with the use of PowerShell.

This will require four separate PowerShell scripts.

These scripts will *assume* that your SharePoint 2010 environment is using SQL Server 2008 (or) R2 and that your SharePoint 2010 production SQL databases have a scheduled backup job.

This also *assumes* that both your Production and Staging environment are separate and have their own farms and have different SQL Servers, but are configured exactly the same.

The 1st script will  copy the latest FULL Production SharePoint 2010 Content Databases backups,  and move these backup files to a destination on the SharePoint 2010 Staging SQL Server.

copy-dbbackups.ps1
__________________________________________________________________________________________________________________

1.  Log into your SharePoint 2010 Production SQL Server.

2.  Copy script below, and change the parameters in Red that correspond to your enviornment, and paste into notepad and save file as copy-dbbackups.ps1
3.  Open up SQL Server Managment Studio (SSMS) on your Production SQL server
4.  Expand SQL Server Agent
5.  Expand Jobs
6.  Right click Jobs and select “New Job”
7.  Under the General tab Give Job a name i.e. (“copy dbbackups”)
8.  Under Steps tab click “New..” button
9.  Give step name i.e. (“copy dbbackups”)
10.  Under Type: select “PowerShell” from dropdown
11.   Under Run As:  Select “SQL Server Agent Service Account”
12.  Under Command:  Click Open… button and browse to the location where you saved copy-dbbackups.ps1 
13.  Under Schedules Tab click the “New..” button
14.  Give Schedule Name:  i.e. (copy dbbackups)
15.  Schedule your job sometime after your FULL backups.  i.e. (If your SQL full backups occur on a Sunday night, schedule your job to happen sometime on Monday after all backups have been completed).
16.  Save your job.
17 .  When this job runs, this may take a long time depending on your database backup file sizes.  My suggestion is to schedule this for overnight in a time where there is less network traffic.

====================================================================

#Remove old backup files on the SQL Server Staging server
get-childitem
\\staging SQL server\m`$\restore\prod2stg | remove-item -force

#Grab backup files from backup location on Production SQL Server
$files = get-childitem
\\backup location\backup\sql_backups\SP2010\FULL | where {$_.FullName -like “*SPContent*_backup_*”  -and $_.CreationTime -ge (get-date).adddays(-4)}

#Copy backup files from backup location on Proction SQL Server to a location on the Staging SQL Server
$files | copy-item -dest
\\staging SQL server\m`$\restore\prod2stg

#Remove databases not needed i.e. Admin and Mysites from Staging SQL Server
get-childitem
\\staging SQL Server\m`$\restore\prod2stg | { if $_.Name -like “*_Admin_*” or $_.Name -like “*_MySites_*“) { $_.Delete() } }

=====================================================================

exec-query.ps1
restore-dbbackups.ps1

This will require two scripts.  1 script will execute a SQL query to establish SQL connection strings and create the datasets, the second script will actually restore the backup databases into SQL.

1.  Open up notepad and copy the following script below and save as exec-query.ps1.
Nothing should need to be changed in this script, just copy as is. 

exec-query.ps1

=====================================================================

function exec-query($sql,$parameters=@{},$conn,$timeout=0,[switch]$help)
{
if($help)
{

$msg = @” Execute a sql statement. Parameters are allowed.  Input parameters should be a dictionary of parameter names and values.  Return value will usually be a list of datarows.”@

Write-Host $msg
return

}
$cmd=new-object system.Data.SqlClient.SqlCommand($sql, $conn)
$cmd.CommandTimeOut = $timeout
foreach($p in $parameters.Keys)
{
[void] $cmd.Parameters.AddWithValue(“@$p”, $parameters[$p])
}

$ds=New-Object  System.Data.DataSet
$da=New-Object System.Data.SqlClient.SqlDataAdapter($cmd)
$da.fill($ds) | Out-Null

return $ds

}
========================================================================

restore-dbbackups.ps1

This script will call the exec-query script, and then restore the backup of the content databases moved over from your Production SQL environment into the Staging SQL server.

Copy the script below and change the parameters in Red with your environment information.  Save file as restore-dbbackups.ps1

1.  Open up SQL Server Managment Studio (SSMS) on your Staging SQL Server
2.  Expand SQL Server Agent
3.  Expand Jobs
4.  Right click Jobs and select “New Job”
5.  Under the General tab Give Job a name i.e. (“restore dbbackups”)
6.  Under Steps tab click “New..” button
7.  Give step name i.e. (“restore dbbackups”)
8.  Under Type: select “PowerShell” from dropdown
9.   Under Run As:  Select “SQL Server Agent Service Account”
10.  Under Command:  Click Open… button and browse to the location where you saved restore-dbbackups.ps1 
11.  Under Schedules Tab click the “New..” button
12.  Give Schedule Name:  i.e. (restore dbbackups)
13.  Schedule your job sometime after your FULL backups from Production have been moved over to your Staging environment
14.  Save your job.
15.  When this job runs, this may take a long time depending on your database backup file sizes.  My suggestion is to schedule this for overnight in a time where there is less network traffic.  If this is staging are you are not concerned about network traffic schedule it when you like

=========================================================================

#Load the exec-query.ps1 script
. M:\restore\exec-query.ps1

#Grab the database backup files that were moved over from your Production SQL environment.
$backups = get-childitem M:\restore\prod2stg

#Loop through the backup files and trim/truncate the database name by removing unwanted characters in the backup dbname file name.  For example my backup database files names were in this format (SPContent_Site1_backup_2013_02_22_200009_6652162.bak).  I wanted to remove every character after _backup*.  You will have to play with the parameters here a little to get this to work for your database name by messing with the substring parameters ($_.name.substring(0,$_.name.lastindexof(“_”)-13), $dbname.substring(0,$dbname.lastindexof(“_”), and $dbname.trimend(“_backup”)).

$backups | % {

$dbname = $_.name.substring(0,$_.name.lastindexof(“_”)-13)  ; $dbname = $dbname.substring(0,$dbname.lastindexof(“_“)); $dbname = $dbname.trimend(“_backup“)

#Restore backup database files to Staging SQL Server
restore-SQLdatabase -SQLServer “SQL SERVER” -SQLDatabase $dbname -Path $($_.FullName) -TrustedConnection $true

#Establish a SQL connection
$conn = new-object data.sqlclient.sqlconnection “Server=SQL SERVER;Integrated Security=true”

$conn.open()

#Set database files to Simply Recovery and execute the exec-query.ps1 script
$sqlcmd1 = “ALTER DATABASE [$dbname] SET RECOVERY SIMPLY WITH NO_WAIT”
exec-query $sqlcmd1 -conn $conn

}

=============================================================================

This last script is not really necessary but I did run into some problems after doing restores where SharePoint did not recognize the databases in the farm.  To overcome this I just dismounted and the remounted the databases in my staging environment by running this script.

Copy script below and change the parameters in “Red” to reflect your environment.  Save file as dismount-mount-dbs.ps1

If you use Task Scheduler on your SharePoint Server (CA).  Then just add this script as a scheduled task on that server.   Schedule it to run after all databases have been restored to your Staging SQL environment.

dismount-mount-dbs.ps1

==============================================================================

#Get databases currently mounted in your SharePoint 2010 staging farm
$databaseName = Get-SPDatabase

#foreach to loop through the content databases in your staging farm dismount them, and then re-mount them.
foreach($database in $databaseName)
{
if ($database -like “*SPContent_*)
{
Write-Host “Dismounting database ” $database.Name -foreground “green”
Dismount-SPContentDatabase -Identity $database.Name -confirm:$false
Start-Sleep -s 10
Write-Host “Mounting database ” $database.Name -foreground “green”
Mount-SPContentDatabase -name $database.Name -DatabaseServer STAGING SQL SERVER -WebApplication STAGING WEB APPLICATION URL -confirm:$false
Start-Sleep -s 10
}
}
=================================================================================

Advertisements
  1. Mac
    August 20, 2015 at 11:56 am

    Hi, super article! It is waht excatly I need but I can’t download powershell scripts. Where I can found this?

  1. August 19, 2015 at 11:33 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: