:::: MENU ::::

Powershell copy database backups

Needed to setup an automatic copy of latest SQL Server database backup from production database to test server. A twist was that the production database was setup with AlwaysOn database mirroring with backups taken on the secondary replica. So, how do we find the latest database backup? I solved it by querying both msdb’s and comparing the backup dates. Ugly but it works…

# Script to setup automatic copy of database backup from AlwaysOn
# production servers to test server
# I use it on a separate admin server
# Run this script as a Scheduled Task or on demand,
# the user need to be able to access the backup files remotely
# and also on the destination server catalog
# This is just an example script, needs to be edited to suit your environment
# It works by logging in to both AlwaysOn instances and
# checking the file path and time stamp
# for the latest backup of the database you want to copy.
# You need a simple user to log in to the production instances,
# no special rights granted,
# public can read this information in msdb, I use the login RemoteFilecopy
# I use diskshares for backups in the form of \\servername\drive$
# eg. \\192.168.0.2\z$
# edit to suit your need

########## Common variables for all copying ##########

$password = "YOUR PASSWORD FOR THIS LOGIN"
$db = "msdb"
$DestinationCatalog = "\\TESTSERVER\DISKSHARE$\DESTINATIONCATALOG\"

########## GET LATEST PRODUCTION DATABASE BACKUP ##########

$ServerInstance1 = "PRODUCTION_SERVER_1"
$ServerInstance2 = "PRODUCTION_SERVER_2"
$SourceDatabase = "DATABASE_NAME"

# Check latest backup from each server and get the latest one
$sqlstring = @"
SELECT TOP 1 bmf.physical_device_name [Filename], bs.backup_finish_date [BackupDate]
FROM msdb.dbo.backupmediafamily bmf
INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id
WHERE bs.type = 'D'
AND bs.database_name = '$SourceDatabase'
ORDER BY bs.backup_finish_date DESC
"@

# I use a simple dummy date below just to have a non-null date to compare to
# If your backups are older than 2010, than you have other problems :-)

$Filename1 = (Invoke-Sqlcmd -Username $user -Password $password -Serverinstance $ServerInstance1 -Database $db -Query $sqlstring).Filename
$BackupDate1 = Get-Date "1/1/2010"
if($Filename1.length -gt 0 ) {
$BackupDate1 = (Invoke-Sqlcmd -Username $user -Password $password -Serverinstance $ServerInstance1 -Database $db -Query $sqlstring).BackupDate
}

$Filename2 = (Invoke-Sqlcmd -Username $user -Password $password -Serverinstance $ServerInstance2 -Database $db -Query $sqlstring).Filename
$BackupDate2 = Get-Date "1/1/2010"
if($Filename2.length -gt 0 ) {
$BackupDate2 = (Invoke-Sqlcmd -Username $user -Password $password -Serverinstance $ServerInstance2 -Database $db -Query $sqlstring).BackupDate
}

if ( $BackupDate1 -gt $BackupDate2 ) {
$SourceBaseCatalog = "\\PRODUCTION_SERVER_1\DISKSHARE$\"
$Filename = $SourceBaseCatalog + $Filename1.Remove(0,3)
}
if ( $BackupDate1 -lt $BackupDate2 ) {
$SourceBaseCatalog = "\\PRODUCTION_SERVER_2\DISKSHARE$\"
$Filename = $SourceBaseCatalog + $Filename2.Remove(0,3)
}
if ( $BackupDate1 -eq $BackupDate2 ) { Write-Host "Found no backup or same date, break!!"; Break; }

################### START COPYING BACKUP ################################

# Change from sqlps to normal filesystem usage
Set-Location C:

# Create destination filename, I use the original name plus "FULL_LATEST"
$DestinationFilename = "$DestinationCatalog" + $SourceDatabase + "_FULL_LATEST.bak"

# Check if file exists, exit otherwise
IF(!(Test-Path -path $Filename))a
{Write-Host "$Filename not found!"; exit}

# Check if destination catalogexists, exit otherwise
IF(!(Test-Path -path $DestinationCatalog)) {
Write-Host "$DestinationCatalog not found, exiting`r`n"
exit
}

# Copy file from PROD server to TEST server
Write-Host "Start copying file $Filename to $DestinationFilename"

# Uncomment line below to actually copy the file, but ONLY after testing, there are numerous possibilities to screw up here :-)
# Copy-Item -Path $Filename -Destination $DestinationFilename
########## COPY BACKUP DONE ##########


So, what do you think ?