Backup-Zip-Copy-Unzip-Restore SQL Server Databases:
I am sure we all try to bring databases from One Environment to Another Environment whether its to accomplish testing, debugging or maintaining the parity between production and lab.
I used to spend little bit of time doing these tasks manually , after few attempts, it was apparent this is not the most enjoyable task. Its one of those things that needs to be done but nothing more than than.
I gathered Powershell scripts available online as well as made some modifications to suit my needs. My pain point was synching databases from Production / Staging Environment where the database needs to be copied across data centers.
Tasks I want to accomplish with this scripts are:
1. Backup the Databases from the Source Server.
2 . Zip the Backup Folder
3. Copy the Zipped Folder to Destination and unzip
4. Restore the databases on the Target Server.
I know there are lot of improvements can be made for this script but it does all the tasks I wanted to do.
Load the Assemblies. I was getting some erorrs with with Assembly references and I am loading all the assemblies.
$assemblylist =
"Microsoft.SqlServer.Management.Common",
"Microsoft.SqlServer.Smo",
"Microsoft.SqlServer.Dmf ",
"Microsoft.SqlServer.Instapi ",
"Microsoft.SqlServer.SqlWmiManagement ",
"Microsoft.SqlServer.ConnectionInfo ",
"Microsoft.SqlServer.SmoExtended ",
"Microsoft.SqlServer.SqlTDiagM ",
"Microsoft.SqlServer.SString ",
"Microsoft.SqlServer.Management.RegisteredServers ",
"Microsoft.SqlServer.Management.Sdk.Sfc ",
"Microsoft.SqlServer.SqlEnum ",
"Microsoft.SqlServer.RegSvrEnum ",
"Microsoft.SqlServer.WmiEnum ",
"Microsoft.SqlServer.ServiceBrokerEnum ",
"Microsoft.SqlServer.ConnectionInfoExtended ",
"Microsoft.SqlServer.Management.Collector ",
"Microsoft.SqlServer.Management.CollectorEnum",
"Microsoft.SqlServer.Management.Dac",
"Microsoft.SqlServer.Management.DacEnum",
"Microsoft.SqlServer.Management.Utility",
"System.IO.CoPRODression.ZipFile",
"System.IO.CoPRODression.FileSystem"
foreach ($asm in $assemblylist)
{
$asm = [Reflection.Assembly]::LoadWithPartialName($asm)
}
$dt = get-date -format yyyyMMddHHmmss
$PROD_to_STAGING = 1
$STAGING_To_PROD = 0
### Array of Databases needs to backed-up and compressed and copied over
$databases = "Database1","Database2","Datbase3"
if ( $PROD_to_STAGING -eq 1 )
{
$inst = "11.22.333.44,1433" ## Prod Instance
$bdir = "\\11.222.33.44\D$\ProdBackups\"+$dt
$copydestinationfolder = "\\DestinationShare\g$\Projects\ProdBackups\"+$dt
#### Restore Database Target Server
$sqlserver = "STAGINGINST\TEST2008R2"
$databasesuffix = "_Prod_"+$dt
}
if ( $STAGING_To_PROD -eq 1 )
{
$inst = "STAGINGINST\TEST2008R2"
$bdir = "\\STAGING_INST\g$\Projects\STAGINGBackups\"+$dt
$copydestinationfolder = "\\11.222.33.44\D$\StagingBackups\"+$dt
#### Restore Database Target Server
$sqlserver = "PRODUCTIONSERVER"
$databasesuffix = "_STAGING_"+$dt
}
$BackupFileFilter = "*.bak"
$BackupPath = $copydestinationfolder
### Create Backup Folder and Copy Folders
$check = Test-Path -PathType Container $bdir
if($check -eq $false){
New-Item $bdir -type Directory
}
$check = Test-Path -PathType Container $copydestinationfolder
if($check -eq $false){
New-Item $copydestinationfolder -type Directory
}
$mySrvConn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
$mySrvConn.ServerInstance=$inst
$mySrvConn.LoginSecure = $false
### Make sure these login Crednetials are setup correctly
$mySrvConn.Login = "username"
$mySrvConn.Password = "password"
### For large backups its better to configure timeouts
# $mySrvConn.ConnectTimeout=0
$mySrvConn.StatementTimeout = 0
$svr = New-Object 'Microsoft.SqlServer.Management.SMO.Server' $mySrvConn
foreach ( $database in $databases)
{
$db = $svr.Databases[$database]
$dbname = $db.Name
$dbbk = new-object ('Microsoft.SqlServer.Management.Smo.Backup')
$dbbk.Action = 'Database'
$dbbk.BackupSetDescription = "Full backup of " + $dbname
$dbbk.BackupSetName = $dbname + " Backup"
$dbbk.Database = $dbname
$dbbk.MediaDescription = "Disk"
$dbbk.Devices.AddDevice($bdir + "\" + $dbname + "_db_" + $dt + ".bak", 'File')
$dbbk.SqlBackup($svr)
$backupfilename = $bdir + "\" + $dbname + "_db_" + $dt + ".bak"
}
$srcdir = $bdir
$zipFilename = "Compressed.zip"
$zipFilepath = $bdir
$zipFile = "$zipFilepath$zipFilename"
#Prepare zip file
if(-not (test-path($zipFile))) {
set-content $zipFile ("PK" + [char]5 + [char]6 + ("$([char]0)" * 18))
(dir $zipFile).IsReadOnly = $false
}
$shellApplication = new-object -com shell.application
$zipPackage = $shellApplication.NameSpace($zipFile)
$files = Get-ChildItem -Path $srcdir | where{! $_.PSIsContainer}
foreach($file in $files) {
$zipPackage.CopyHere($file.FullName)
#using this method, sometimes files can be 'skipped'
#this 'while' loop checks each file is added before moving to the next
while($zipPackage.Items().Item($file.name) -eq $null){
Start-sleep -seconds 1
}
}
## Copy the files to the Destination Folder
Copy-Item $zipFile $copydestinationfolder
$zip_file = Get-Item ("$copydestinationfolder\*.zip")
#Start timer
#"Start (s)"
#“$(Get-Date -Uformat %s)"
$start_time = $(Get-Date -Uformat %s)
#Unzip
[System.IO.CoPRODression.ZipFile]::ExtractToDirectory($zip_file, $copydestinationfolder)
## Resore Database in the Target Server
#load assemblies
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null
$restoredatabases =@()
gci $BackupPath -Filter $BackupFileFilter | select fullname | % {
$backupFile = $_.FullName
$mySrvConn = new-object Microsoft.SqlServer.Management.Common.ServerConnection
$mySrvConn.ServerInstance=$sqlserver
$mySrvConn.LoginSecure = $false
### Make sure these login Crednetials are setup correctly
$mySrvConn.Login = "username"
$mySrvConn.Password = "password"
#we will query the database name from the backup header later
$server = New-Object ( "Microsoft.SqlServer.Management.Smo.Server" ) $mySrvConn
$backupDevice = New-Object( "Microsoft.SqlServer.Management.Smo.BackupDeviceItem" ) ($backupFile, "File")
$smoRestore = new-object( "Microsoft.SqlServer.Management.Smo.Restore" )
$backupDevice| FL *
#Get default log and data file locations http://sqlblog.com/blogs/allen_white/archive/2009/02/19/finding-your-default-file-locations-in-smo.aspx
$DataPath = if ($server.Settings.DefaultFile.Length -gt 0 ) { $server.Settings.DefaultFile } else { $server.Information.MasterDBLogPath }
$LogPath = if ($server.Settings.DefaultLog.Length -gt 0 ) { $server.Settings.DefaultLog } else { $server.Information.MasterDBLogPath }
#restore settings
$smoRestore.NoRecovery = $false;
$smoRestore.ReplaceDatabase = $true;
$smoRestore.Action = "Database"
$smoRestore.PercentCompleteNotification = 10;
$smoRestore.Devices.Add($backupDevice)
#get database name from backup file
$smoRestoreDetails = $smoRestore.ReadBackupHeader($server)
#display database name
"Database Name from Backup Header : " +$smoRestoreDetails.Rows[0]["DatabaseName"]
#give a new database name
$smoRestore.Database = $smoRestoreDetails.Rows[0]["DatabaseName"]+$databasesuffix
"Restored Database Name : " +$smoRestoreDetails.Rows[0]["DatabaseName"]+$databasesuffix
$restoredatabases += $smoRestoreDetails.Rows[0]["DatabaseName"]+$databasesuffix
#Relocate each file in the restore to the default directory
$smoRestoreFiles = $smoRestore.ReadFileList($server)
foreach ($File in $smoRestoreFiles) {
#Create relocate file object so that we can restore the database to a different path
$smoRestoreFile = New-Object( "Microsoft.SqlServer.Management.Smo.RelocateFile" )
#the logical file names should be the logical filename stored in the backup media
$smoRestoreFile.LogicalFileName = $File.LogicalName
$smoRestoreFile.PhysicalFileName = $( if($File.Type -eq "L") {$LogPath} else {$DataPath} ) + "\" + $databasesuffix+"_"+[System.IO.Path]::GetFileName($File.PhysicalName)
$smoRestore.RelocateFiles.Add($smoRestoreFile)
}
#restore database
$smoRestore.SqlRestore($server)
}
######### Send out email with all the information
Function sendEmail
{
param($from,$to,$subject,$smtphost,$body )
[string]$receipients=”$to”
$body = New-Object System.Net.Mail.MailMessage $from, $receipients, $subject, $body
$body.isBodyhtml = $true
$smtpServer = $MailServer
$smtp = new-object Net.Mail.SmtpClient($smtphost)
$validfrom= Validate-IsEmail $from
if($validfrom -eq $TRUE)
{
$validTo= Validate-IsEmail $to
if($validTo -eq $TRUE)
{
$smtp.Send($body)
write-output “Email Sent!!”
}
}
else
{
write-output “Invalid entries, Try again!!”
}
}
# Email our report out
function Validate-IsEmail ([string]$Email)
{
return $Email -match “^(?(“”)(“”.+?””@)|(([0-9a-zA-Z]((\.(?!\.))|[-!#\$%&'\*\+/=\?\^`\{\}\|~\w])*)(?<=[0-9a-zA-Z])@))(?(\[)(\[(\d{1,3}\.){3}\d{1,3}\])|(([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,6}))$”
}
$date = ( get-date ).ToString(‘yyyy/MM/dd’)
$bodymessage = "<b>Backup SQL Server Name :</b>$inst"+"`n"+""
$bodymessage += "<b>Restore SQL Server Name :</b>$sqlserver"+"`n"+""
$bodymessage += "<b>Backup Source Folder :</b>$bdir"+"`n" +""
$bodymessage += "<b>Restore Destination Folder : </b>$copydestinationfolder"+"`n" +""
$bodymessage += "<b>Databases Backed-up : </b> " + [system.String]::Join(" ", $databases) +"`n" +""
$bodymessage += "<b>Databases Restored :</b>" + [system.String]::Join(" ", $restoredatabases) +"`n"+""
$bodymessage += "Thanks,
Raju Venkataraman"
### Send Email Notifications
$emaillist = "[email protected]"
foreach ($email in $emaillist)
{
sendEmail -from "[email protected]" -to $email -subject “Backup Copy Restore – $Date” -smtphost "SMTPSERVER" -body $bodymessage -BodyAsHtml -Priority Normal
}