Azure DR Part 2: Backup Database to VM

Aim: Schedule daily backups of an on-premises SQL Server database & copy it to an Azure virtual machine as part of a Disaster Recovery process.

In the previous post, I set up a virtual network & machine in Azure that on-premises SQL Server databases can be backed up to as part of a Disaster Recovery process. If the on-premises machine is not already connected to the Azure virtual network, connect following the instructions at the end of the previous post.

This method uses Windows scheduled tasks & will therefore work for versions of SQL Server where the Agent is not available (e.g. Express).

Instead of running the daily backup process under the Microsoft Account, create a new organisation user (e.g. testAzureUser) in the Default Directory in the Active Directory section of the Azure portal. Set the organisation role to Service Admin. Take note of the temporary password & change it to something secure. In the Settings section of the portal, set the new user as a Co-Administrator of the subscription.

Download & install Azure Powershell. Launch a Powershell window & add an Azure Account using:

Add-AzureAccount

When the login window pops up, enter the username & password for the newly created organisation user. Note: Internet Explorer must be set as the default browser for this step to succeed.

Test the connection by starting or stopping a VM using:

Get-AzureVM
Start-AzureVM
Stop-AzureVM

To backup the database, follow the instructions on https://support.microsoft.com/en-gb/kb/2019698 to create a stored procedure (sp_BackupDatabases) on the SQL Server:

=============================================
-- Author: Microsoft
-- Create date: 2010-02-06
-- Description: Backup Databases for SQLExpress
-- Parameter1: databaseName
-- Parameter2: backupType F=full, D=differential, L=log
-- Parameter3: backup file location
-- =============================================

CREATE PROCEDURE [dbo].[sp_BackupDatabases]
   @databaseName sysname = null,
   @backupType CHAR(1),
   @backupLocation nvarchar(200)
AS

SET NOCOUNT ON;

DECLARE @DBs TABLE
(
   ID int IDENTITY PRIMARY KEY,
   DBNAME nvarchar(500)
)

-- Pick out only databases which are online in case ALL databases are chosen to be backed up
-- If specific database is chosen to be backed up only pick that out from @DBs
INSERT INTO @DBs (DBNAME)
SELECT Name FROM master.sys.databases
where state=0
   AND name=@DatabaseName
   OR @DatabaseName IS NULL
ORDER BY Name

-- Filter out databases which do not need to backed up
IF @backupType='F'
BEGIN
   DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks')
END
ELSE IF @backupType='D'
BEGIN
   DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
END
ELSE IF @backupType='L'
BEGIN
   DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks')
END
ELSE
BEGIN
   RETURN
END

-- Declare variables
DECLARE @BackupName varchar(100)
DECLARE @BackupFile varchar(100)
DECLARE @DBNAME varchar(300)
DECLARE @sqlCommand NVARCHAR(1000)
DECLARE @dateTime NVARCHAR(20)
DECLARE @Loop int

-- Loop through the databases one by one
SELECT @Loop = min(ID) FROM @DBs

WHILE @Loop IS NOT NULL
BEGIN

   -- Database Names have to be in [dbname] format since some have - or _ in their name
   SET @DBNAME = '['+(SELECT DBNAME FROM @DBs WHERE ID = @Loop)+']'

   -- Set the current date and time n yyyyhhmmss format
   SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),101),'/','') + '_' + REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')

   -- Create backup filename in path\filename.extension format for full,diff and log backups
   IF @backupType = 'F'
      SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_FULL_'+ @dateTime+ '.BAK'
   ELSE IF @backupType = 'D'
      SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_DIFF_'+ @dateTime+ '.BAK'
   ELSE IF @backupType = 'L'
      SET @BackupFile = @backupLocation+REPLACE(REPLACE(@DBNAME, '[',''),']','')+ '_LOG_'+ @dateTime+ '.TRN'

   -- Provide the backup a name for storing in the media
   IF @backupType = 'F'
      SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' full backup for '+ @dateTime
   IF @backupType = 'D'
      SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' differential backup for '+ @dateTime
   IF @backupType = 'L'
      SET @BackupName = REPLACE(REPLACE(@DBNAME,'[',''),']','') +' log backup for '+ @dateTime

   -- Generate the dynamic SQL command to be executed

   IF @backupType = 'F'
   BEGIN
      SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
   END
   IF @backupType = 'D'
   BEGIN
      SET @sqlCommand = 'BACKUP DATABASE ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH DIFFERENTIAL, INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
   END
   IF @backupType = 'L'
   BEGIN
      SET @sqlCommand = 'BACKUP LOG ' +@DBNAME+ ' TO DISK = '''+@BackupFile+ ''' WITH INIT, NAME= ''' +@BackupName+''', NOSKIP, NOFORMAT'
   END

   -- Execute the generated SQL command
   EXEC(@sqlCommand)

   -- Goto the next database
   SELECT @Loop = min(ID) FROM @DBs where ID>@Loop

END
GO

Create a batch file (BackupTest01.bat) containing the following script to backup the database Test01 on SQLServer01:

sqlcmd -S SQLServer01 -E -Q "EXEC sp_BackupDatabases @backupLocation='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\', @databaseName='Test01', @backupType='F'"

Before creating the PowerShell script to copy the backup to the Azure VM, the Azure portal login & VM credentials must be securely saved to text files that can be read by the PowerShell file. To save the Azure portal credentials, run the following in PowerShell & input the Azure portal login password associated with the testAzureUser@<organisation> organisation user account. The characters of the password will appear as asterisks as you type them.

read-host -assecurestring | convertfrom-securestring | out-file C:\AzureCredentials\cred.txt

To save the Azure VM credentials, run the following in PowerShell & input the VM password associated with the TestRestore01\<username> login:

read-host -assecurestring | convertfrom-securestring | out-file C:\AzureCredentials\credVM.txt

Create a PowerShell file (CopyBackupFile_test01.ps1) containing the following script to copy the Test01 backup file from SQLServer01 to the Azure VM with internal IP address 10.0.0.4. Replace testAzureUser@<organisation> with the name of the organisation user created in an earlier step & TestRestore01\<username> with the name of the Azure VM login.

# Copy Backup File Test 01

# Verify the Azure module is available and import it if not
if (!(Get-Module -Name "Azure")) {
   import-Module -Name Azure
}

# Create logging file
$logfile = "C:\Logging\"+(Get-Date -format "yyyy-MM-dd")+"_CopyBackupFiles.log"
(Get-Date -format "yyyy-MM-dd HH-mm")+" Starting backup copy" | out-file -filepath $logfile -append

# Create a credential object for connecting to Azure remotely
$username="testAzureUser@<organisation>"
$pass=Get-Content C:\AzureCredentials\cred.txt | ConvertTo-SecureString
$cred=New-Object -TypeName System.Management.Automation.PSCredential -Argumentlist $username,$pass

# Create a credential object for connecting to the VM remotely
$usernameVM="TestRestore01\<username>"
$passVM=Get-Content C:\AzureCredentials\credVM2.txt | ConvertTo-SecureString
$credVM=New-Object -TypeName System.Management.Automation.PSCredential -Argumentlist $usernameVM,$passVM

# Connect to Azure
Login-AzureRmAccount -Credential $cred

# Mount target directory
New-PSDrive -root "\\10.0.0.4\c$\SQL Backups" -Credential $credVM -name G -psprovider FileSystem

# Test that the remote location is accessible and copy the file
if (Test-Path G:) {
   "Remote path found, proceeding." | out-file -filepath $logfile -append
try {
   (Get-Childitem "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup" | Sort-Object -Descending -Property LastWriteTime)[0] | Copy-Item -Destination G:
   "Copy completed successfully." | out-file -filepath $logfile -append
} catch {
   "Copy process failed, error message was:`n" | out-file -filepath $logfile -append
}
} else {
   "Remote path not found, exiting." | out-file -filepath $logfile -append
}

Create a PowerShell file (DeleteOldBackupFile_test01.ps1) containing the following script to delete the oldest Test01 backup file from SQLServer01 as part of the backup clean-up process:

# Delete Old Backup File Test 01

# Create logging file
$logfile = "C:\Logging\"+(Get-Date -format "yyyy-MM-dd")+"_CopyBackupFiles.log"
(Get-Date -format "yyyy-MM-dd HH-mm")+" Starting file delete" | out-file -filepath $logfile -append

# Delete the oldest file in the folder
try {
   (Get-Childitem "C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup" | Sort-Object -Property LastWriteTime)[0] | Remove-Item
   "Old file deleted successfully." | out-file -filepath $logfile -append
} catch {
   "Delete process failed, error message was:`n" | out-file -filepath $logfile -append
}

To run the PowerShell scripts successfully from a scheduled task, they must be called from batch files. Therefore, create two batch files (CopyBackupFile_test01.bat & DeleteOldBackupFile_test01.bat), each containing the following script. These batch files must be created with the same names & in the same folder as the Powershell files.

@ECHO OFF
PowerShell.exe -Command "& '%~dpn0.ps1'"
PAUSE

Create a new task on SQLServer01 to run the database backup, copy the backup file to the Azure VM & delete the oldest backup file

In the General tab, set the task to run whether the user is logged on or not. This will require you to enter the user account credentials for running the task at the end of the setup.

In the Triggers tab, set a suitable schedule (e.g. daily).

In the Actions tab, set three actions to call each of the batch files in the following order:

1. BackupTest01.bat
2. CopyBackupFiles_test01.bat
3. DeleteOldBackupFiles_test01.bat

Save the task & enter the required credentials.

To test, run the scheduled task & monitor the backup file folders on the on-premises server & Azure VM.