Home > Uncategorized > Restoring SharePoint 2010 Content Databases to SQL 2012 SP2 Utilizing PowerShell

Restoring SharePoint 2010 Content Databases to SQL 2012 SP2 Utilizing PowerShell

Working on my SharePoint 2010 COOP Environment I needed to copy over and restore backups of all databases files from my SharePoint 2010 Production environment to COOP after all full backup jobs were complete.  This happens once a week in my environment, usually on Saturdays.  I know this can easily be done with 3rd party tools like Snap Manager for SQL, however that solution is not yet available in my environment so I had to turn to PowerShell to help accomplish what i needed.

I’ve already blogged about how to do this for mirroring a Staging environment with Production here: https://shipoint.com/2013/01/22/moving-production-sharepoint-2010-content-databases-to-sharepoint-2010-staging-environment-using-powershell/

However, that blog talks about how to backup and restore with SQL 2008.  The SQL restore steps however change just a little bit for SQL 2012 SP2.  Below is the PowerShell script I generated to help me accomplish this in my environment.  Of course you will have to modify this script to work in yours.

First the naming convention my SQL DB choose for the backup files I had to do a little parsing to get the name correct to restore correctly within SQL 2012.  For instance the naming convention is as such:

SERVERNAME$INSTANCE_SPContent_NAMEOFSITECOLLECTION_FULL_DATETIME.bak

Since the naming convention used an escape reserved character ($) I had to do a little renaming mining.

————————————————————————————————————-

#Get-ChildItem from database backup directory and rename file to remove the Server Name.
Get-ChildItem U:\Backup -Filter “*SERVER NAME*” | Rename-Item -NewName {$_.Name -Replace ‘SERVER NAME‘,”}

#Get-ChildItem from database backup directory and rename file to remove the reserved character ($) and the Instance.
Get-ChildItem U:\Backup -Filter “*$INSTANCE_*” | Rename-Item -NewName {$_.Name -Replace [regex]::Escape(‘$INSTANCE_‘),”}

#Get the backups of all files after name change
$backups = Get-ChildItem U:\Backup

#Store SQL Server Name and Instance in $sqlServer variable
$sqlServer = “SERVERNAME\INSTANCE

#Start a new instance of the Microsoft.SqlServer.Managment.Smo.Server Namesspace Class, and pass it the SQL Servername and Instance.  This allows you to do the restore procedure.  To see what other things you can visit here: https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo%28v=sql.110%29.aspx
$dst = New-Object (‘Microsoft.SqlServer.Management.Smo.Server’) $sqlServer

#Set the Connection Timeout to 0.  By default the timeout Session in SQL 2012 SP2 is set to 10 minutes.  If your job takes longer than 10 minutes your script will be killed.  Setting the timeout to 0 will allow for no timeout.
$dst.ConnectionContext.StatementTimeout = 0

#Loop through all database backup files, remove all unnecessary characters in the file name; Kill the current processes of the existing database so a restore can happen; perform restore importing the $dst parameters and utilizing the -ReplaceDatabase switch.
$backups | % {

$dbname = $_.name.substring(0,$_name.lastindexof(“_”)-14);$dbname.Replace(‘$INSTANCE_‘,”);$dbname = $dbname.trimend(“_FULL”)
$dst.KillProcesses($dbname)
Restore-SQLDatabase -InputObject $dst -Database $dbname -BackupFile $($_.Fullname) -ReplaceDatabase
}

————————————————————————————————————–

 

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

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: