Remote Session, invoke-sqlcmd, File Transfer Script (Powershell)

# Disclosure: I used Find/Replace to mask identifiers, so this may no longer work without fiddling.

# Constraints given for this script:
# *Manual script, to be run on the first of the month.
# *Transfer the nightly SQL backup file to the Dev SQL server,
# Backup and save the current Dev DB, then import (replace) the new DB
# *Checks the age of the backup file, in case the SQL backup process is not yet complete
# *Use controlled (monitored) service accounts for automated actions
# *Backup filename will be in the format [dbname_backup_YYYY_MM_DD_*.bak]
# *Only the backup from the morning of the 1st is to be sent
# *Compress the file to reduce WAN bandwidth usage

# Before running script, run “set-executionpolicy unrestricted” to enable
# This script is run from our RDS server, and starts a remote session on the Dev SQL server
# To use LZMA2 compression in remote session, following GPO must be set:
#     CompConfig>Policies>AdminTemplates>WindowsComponents>WindowsRemoteShell>SpecifyMaximumMemoryMBperShell
#     (I used 6144 ram and 16 available threads, but -mmt6 on 7z)
# There are stubs for Bits-Transfer commented out. BT would be preferred over robocopy, but
# the end-user was unable to initiate BT and script errored out.
# Param validates and initializes variables. More variables declared in remote session script below!
Param(
[string] $BackupDay = “01”,
[string] $BackupYYMM = “{0:yyyy_MM_}” -f (get-date),

$StorageUser = “Domain\SvcAcct1”,
$StoragePassText = ‘Pass1’,
$SqlSrvUser = ‘Domain\SvcAcct2’,
$SqlSrvPassText = ‘Pass2’,

[string] $DBBak = “dbname_backup_” + $BackupYYMM + $BackupDay + “*”,
[string] $DBBakPath = “C:\SQLBak\$DBBak”,
[string] $DevZipFile = “DevDBbak_” + $BackupYYMM + $BackupDay + “.7z”,
[string] $DevZipFilePath = “C:\temp\$DevZipFile”,

[string] $StorageTargetShort = “\\[Storage IP]\ITshare”,
[string] $StorageTarget = “\\[Storage IP]\ITshare\ProdSqlBackups\” + $BackupYYMM + $BackupDay + “\”,
[string] $SQLTransferTargetShort = “\\[SQL IP]\datashare”,
[string] $SQLTransferTarget = “\\[SQL IP]\datashare\”,
[string] $SQLserver = “[SQLServerName.domain]”,

[datetime]$FileTimeStamp,
[datetime]$ServerTimeStamp,

$StoragePass = $(ConvertTo-SecureString ‘$StoragePassText’ -AsPlainText -Force),
$StorageCredentials = $(new-object -typename System.Management.Automation.PSCredential -argumentlist $StorageUser,$StoragePass),
$SqlSrvPass = $(ConvertTo-SecureString -String $SqlSrvPassText -AsPlainText -Force),
$SqlSrvCredentials = $(New-Object System.Management.Automation.PSCredential -ArgumentList @($SqlSrvUser,$SqlSrvPass)),

[string] $FileName = “”
)

# for portability, add 7zip to the Path from inside the script
$env:Path += “;C:\Program Files\7-Zip”
# Import-Module BitsTransfer

# if today isn’t the first, why are they running the script?
if (($(get-date).day) -ne 1){
Write-Host -nonewline “Today is not the first.” -foreground red
$message = “Are you sure you wish to continue?”
$yes = New-Object System.Management.Automation.Host.ChoiceDescription “&Yes”, “Runs the script.”
$no = New-Object System.Management.Automation.Host.ChoiceDescription “&No”, “Exits the script.”
$options = [System.Management.Automation.Host.ChoiceDescription[]]($yes, $no)
$result = $host.ui.PromptForChoice($title, $message, $options, 0)
switch ($result)
{ 0 {“You selected Yes. `r`n”}
1 {“Thank you, have a nice day.”
Exit }
}
}

# Prompt for write time compare (show the age of the backup)
if (Test-Path $DBBakPath){
$DBBak = $(gci $DBBakPath -name)
$DBBakPath = “C:\SQLBak\$DBBak”
Write-Host “File Found: $DBBak”
} else {Write-Warning “$DBBakPath not found”; exit}
$FileTimeStamp = $(Get-Item $DBBakPath).LastWriteTime
Write-Host “File Timestamp: $FileTimeStamp”
$ServerTimeStamp = (get-date -format G)
Write-Host “Srvr Timestamp: $ServerTimeStamp”
if ($ServerTimeStamp -lt $FileTimeStamp.AddMinutes(30)){
Write-Warning “The time difference is less than 30 minutes!”
}
Write-Host “”
$message = “Do you want to continue zipping and transferring this file?”
$yes = New-Object System.Management.Automation.Host.ChoiceDescription “&Yes”, “Runs the script.”
$no = New-Object System.Management.Automation.Host.ChoiceDescription “&No”, “Exits the script.”
$options = [System.Management.Automation.Host.ChoiceDescription[]]($yes, $no)
$result = $host.ui.PromptForChoice($title, $message, $options, 0)
switch ($result)
{ 0 {“Hold onto your hats, it’s gonna get windy!”}
1 {“Thank you, have a nice day.”
Exit }
}
# Write-Host -nonewline “Transfers” -foreground magenta
# Write-Host -nonewline ” Zips” -foreground cyan
# Write-Host -nonewline ” SQL” -foreground green
# Write-Host ” Session” -foreground gray

# Must cache credentials for network transactions
net use $StorageTargetShort $StoragePassText /USER:$StorageUser | Out-Null
net use $SQLTransferTargetShort $SqlSrvPassText /USER:$SqlSrvUser | Out-Null

$sw = [Diagnostics.Stopwatch]::StartNew()
if (Test-Path $DBBakPath){
Write-Host -nonewline “MGT: ” -foreground DarkCyan
Write-Host “zipping $DBBak” -foreground cyan
& 7z.exe a -mmt=on -m0=lzma2 $DevZipFilePath $DBBakPath
## & 7z.exe a -mmt=on -tbzip2 $DevZipFilePath $DBBakPath
Write-Host -nonewline “MGT: ” -foreground DarkCyan
Write-Host -nonewline “done in” -foregroundcolor cyan
} else {Write-Warning “$DBBakPath not found”; exit}
$sw.Stop(); $elapse = $sw.Elapsed
write-host $([string]::Format(” {0:00}mins {1:00}secs”, $elapse.Minutes, $elapse.Seconds)) -foreground cyan

$sw.Restart()
if (Test-Path $DevZipFilePath){
Write-Host -nonewline “MGT: ” -foreground DarkCyan
Write-Host -nonewline “Transferring $DevZipFilePath …” -foregroundcolor magenta
robocopy “C:\temp\” $SQLTransferTarget $DevZipFile /Z /ETA /NP | Out-Null
# robocopy D:\datashare\ $SendTarget “$DevDBbak.7z” /Z /ETA /NP | Out-Null
# Start-BitsTransfer -Credential $SqlSrvCredentials -Source $DevZipFilePath -Destination $SQLTransferTarget
Write-Host -nonewline “done in” -foregroundcolor magenta
} else { Write-Warning “$DevZipFilePath not found”; exit}
$sw.Stop(); $elapse = $sw.Elapsed
write-host $([string]::Format(” {0:00}mins {1:00}secs”, $elapse.Minutes, $elapse.Seconds)) -foreground magenta

# storage server target
if (!(Test-Path $StorageTarget)){
new-item $StorageTarget -itemtype directory | Out-Null
Write-Host -nonewline “MGT: ” -foreground DarkCyan
Write-Host “Created storage directory”
} else { Write-Host -nonewline “MGT: ” -foreground DarkCyan
Write-Host “Storage Directory Found”
}

# Start session on MN-SQL01
Write-Host “Starting Remote session” -foregroundcolor gray
$SQLsession = new-pssession -computername $SQLserver -credential $SqlSrvCredentials
# Param args taken from -Argumentlist at end of scriptblock
Invoke-Command -Session $SQLsession -Scriptblock {
param($RemoteZipFile, $RemoteDBFile, $SendUser,$SendPW, $SendTargShort, $SendTarget)
$env:Path += “;C:\Program Files\7-Zip”
# Import-Module BitsTransfer
$SendPass = $(ConvertTo-SecureString ‘$SendPW’ -AsPlainText -Force)
$SendCreds = $(new-object -typename System.Management.Automation.PSCredential -argumentlist $SendUser,$SendPass)
$DevDBbak = “DevDBbak_” + $(“{0:yyyy_MM_dd}” -f (get-date).AddDays(-1))
$SQLadmin = “sa” # If it wasn’t Dev, I wouldn’t use sa
$SQLpw = “[Wouldn’t you like to know]”
$dbName = “LocalDBname”
$dbMDF = “D:\DATA\LocalDBname.mdf”
$dbLDF = “D:\DATA\LocalDBname.ldf”
$sqlcmdBAK = “BACKUP DATABASE [$dbName] TO DISK=N’D:\datashare\$DevDBbak.bak’ WITH NOFORMAT, NOINIT, NAME=N’$($dbName) FullBackup’, SKIP, NOREWIND, NOUNLOAD, STATS=10”
$sqlcmdIMPORT = “ALTER DATABASE $dbName SET SINGLE_USER WITH ROLLBACK AFTER 30 RESTORE DATABASE [$dbName] FROM DISK=N’D:\datashare\$RemoteDBFile’ WITH FILE=1, MOVE N’CityWatch_Data’ TO N’$dbMDF’, MOVE N’CityWatch_Log’ TO N’$dbLDF’, NOUNLOAD, REPLACE, STATS=10 ALTER DATABASE $dbName SET MULTI_USER”

# cache credentials for network transactions
net use $SendTargShort $SendPW /USER:$SendUser | Out-Null

Write-Host -nonewline “SQL: ” -foreground gray
Write-Host -nonewline “Checking for the import file…”
if (Test-Path D:\datashare\$RemoteZipFile){
Write-Host “I got it!” -foregroundcolor magenta
} else {Write-Warning “D:\datashare\$RemoteZipFile NOT FOUND”; exit}

$sw = [Diagnostics.Stopwatch]::StartNew()
Write-Host -nonewline “SQL: ” -foreground gray
Write-Host -nonewline “Exporting DB…”
try { invoke-sqlcmd -Username $SQLadmin -Password $SQLpw -query “$sqlcmdBAK” -QueryTimeout 65534
} catch { throw “Something went wrong, check the backup of LocalDBname!”; exit }
Write-Host -nonewline “done in” -foregroundcolor green
$sw.Stop(); $elapse = $sw.Elapsed
write-host $([string]::Format(” {0:00}mins {1:00}secs”, $elapse.Minutes, $elapse.Seconds)) -foreground green

$sw.Restart()
if (Test-Path D:\datashare\$RemoteZipFile){
Write-Host -nonewline “SQL: ” -foreground gray
Write-Host -nonewline “Unzipping $RemoteZipFile…”
& 7z.exe e -mmt=on D:\datashare\$RemoteZipFile -oD:\datashare | Out-Null
Write-Host -nonewline “done in” -foregroundcolor cyan
} else {Write-Warning “D:\datashare\$RemoteZipFile NOT FOUND”; exit}
$sw.Stop(); $elapse = $sw.Elapsed
write-host $([string]::Format(” {0:00}mins {1:00}secs”, $elapse.Minutes, $elapse.Seconds)) -foreground cyan

$sw.Restart()
Write-Host -nonewline “SQL: ” -foreground gray
Write-Host -nonewline “Importing DB…”
try { invoke-sqlcmd -Username $SQLadmin -Password $SQLpw -query “$sqlcmdIMPORT” -QueryTimeout 65534
} catch { throw “Something went wrong, check the IMPORT of LocalDBname!”; exit }
Write-Host -nonewline “done in” -foregroundcolor green
$sw.Stop(); $elapse = $sw.Elapsed
write-host $([string]::Format(” {0:00}mins {1:00}secs”, $elapse.Minutes, $elapse.Seconds)) -foreground green
Write-Host -nonewline “SQL: ” -foreground gray
Write-Host “The DB is now ready for use” -foregroundcolor yellow

$sw.Restart()
if (Test-Path D:\datashare\$DevDBbak.bak){
Write-Host -nonewline “SQL: ” -foreground gray
Write-Host -nonewline “zipping $DevDBbak.7z…”
& 7z.exe a -mmt6 -m0=lzma2 D:\datashare\$DevDBbak.7z D:\datashare\$DevDBbak.bak | Out-Null
Write-Host -nonewline “done in” -foregroundcolor cyan
} else {Write-Warning “D:\datashare\$DevDBbak.bak NOT FOUND”; exit}
$sw.Stop(); $elapse = $sw.Elapsed
write-host $([string]::Format(” {0:00}mins {1:00}secs”, $elapse.Minutes, $elapse.Seconds)) -foreground cyan

$sw.Restart()
if (Test-Path D:\datashare\$DevDBbak.7z){ #-And (Test-Path D:\datashare\$RemoteZipFile)){
Write-Host -nonewline “SQL: ” -foreground gray
Write-Host -nonewline “Transferring $DevDBbak.7z…” # -foregroundcolor magenta
robocopy D:\datashare\ $SendTarget “$DevDBbak.7z” /Z /ETA /NP | Out-Null
Write-Host -nonewline “done in” -foregroundcolor magenta
} else { Write-Warning “D:\datashare\$DevDBbak.7z NOT FOUND”; exit}
$sw.Stop(); $elapse = $sw.Elapsed
write-host $([string]::Format(” {0:00}mins {1:00}secs”, $elapse.Minutes, $elapse.Seconds)) -foreground magenta

Write-Host -nonewline “SQL: ” -foreground gray
Write-Host -nonewline “Cleaning up files…”
if(Test-Path “$SendTarget\$DevDBbak.7z”){
Remove-Item D:\datashare\$DevDBbak.7z
}
Remove-Item D:\datashare\$DevDBbak.bak
Remove-Item D:\datashare\$RemoteDBFile
Remove-Item D:\datashare\$RemoteZipFile
Write-Host “done”
Write-Host “Finished with Remote Session” -foreground gray
net use $SendTargShort /delete | Out-Null

} -ArgumentList $DevZipFile, $DBBak, $StorageUser, $StoragePassText, $StorageTargetShort, $StorageTarget
# done here, clean up the session
Remove-PSSession $SQLsession
Write-Host -nonewline “MGT: ” -foreground DarkCyan
Write-Host “Session cleared”

Write-Host -nonewline “MGT: ” -foreground DarkCyan
Write-Host -nonewline “File Cleanup…”
if (Test-Path $DevZipFilePath){
Remove-Item $DevZipFilePath
}
Write-Host “done”

net use $StorageTargetShort /delete | Out-Null
net use $SQLTransferTargetShort /delete | Out-Null

Advertisements


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