Using SQLCMD to Automate Mirroring Databases to the Cloud

Interesting project I’ve been involved in this week – scripting the process of Mirroring On-Premise databases up into the cloud (on Windows Azure – not SQL Azure) for DR purposes.

Once the Windows Azure VM was built, we installed SQL Server 2012 SP1 to match our production systems and constructed the required mirroring endpoints. After that (given that I had quite a few databases to mirror) I set about generating a script to automate the process (I am lazy after all!). This is the result of it all – a fully functional mirroring script :-

 

— Setup Local Variables for the Database Mirroring
— Make sure the MirrorServer is the same SQL Server Version
— *OR HIGHER* than the PrincipalServer – otherwise the
— Database Restore will fail.
—
— Also make sure that the restore logical filenames and
— restore physical filenames are set appropriately from the
— RESTORE FILELISTONLY command….

:setvar SQLCMDLOGINTIMEOUT 60
:SETVAR PrincipalServer
:SETVAR PrincipalHost
:SETVAR PrincipalServerListenerPort
:SETVAR PrincipalBackupPath

:SETVAR MirrorServer
:SETVAR MirrorHost
:SETVAR MirrorServerListenerPort
:SETVAR MirrorRestorePath
:SETVAR MirrorDatabaseMDFPath “path to mirror ldf file”
:SETVAR MirrorDatabaseLDFPath “path to mirror mdf file”

:SETVAR Database2Mirror

GO

:ON ERROR EXIT
GO

SET NOCOUNT ON

— Connect to the Principal Server and Check the Database we
— intend to mirror is in the FULL recovery mode
:CONNECT $(PrincipalServer) -l $(SQLCMDLOGINTIMEOUT)

— Mirroring ONLY Supports the FULL recovery Model
PRINT ‘Checking RECOVERY Mode on Database $(Database2Mirror)’
GO

IF DATABASEPROPERTYEX(‘$(Database2Mirror)’, ‘Recovery’) != N’FULL’
ALTER DATABASE $(Database2Mirror) SET RECOVERY FULL
GO

— Now Backup the Database from the Principal and restore
— it onto the Mirror Server
PRINT ‘Backing Up Database $(Database2Mirror) On $(PrincipalServer) to $(PrincipalBackupPath)’
GO

— COPY_ONLY so as to not upset the backup LSN chain
BACKUP DATABASE $(Database2Mirror)
TO DISK = N’$(PrincipalBackupPath)\$(Database2Mirror).bak’
WITH COPY_ONLY, INIT, FORMAT
GO

PRINT ‘Backing Up Database $(Database2Mirror) Log On $(PrincipalServer) to $(PrincipalBackupPath)’
GO

— COPY_ONLY so as to not upset the backup LSN chain
BACKUP LOG $(Database2Mirror)
TO DISK = N’$(PrincipalBackupPath)\$(Database2Mirror).trn’
WITH COPY_ONLY, INIT, FORMAT
GO

— At this point, copy the FULL backup and the transaction log
— over to the Mirror Server (or use a UNC path)
— Alternatively, make sure the backup path and the restore path are the same
— and the UNC location can be seen by both principal and mirror

:CONNECT $(MirrorServer) -l $(SQLCMDLOGINTIMEOUT)
PRINT ‘ Test RESTORE FILELISTONLY to ensure we can see the backup’
GO

DECLARE @FileList TABLE
(
LogicalName NVARCHAR(128) NOT NULL,
PhysicalName NVARCHAR(260) NOT NULL,
Type CHAR(1) NOT NULL,
FileGroupName NVARCHAR(120) NULL,
Size NUMERIC(20, 0) NOT NULL,
MaxSize NUMERIC(20, 0) NOT NULL,
FileID BIGINT NULL,
CreateLSN NUMERIC(25,0) NULL,
DropLSN NUMERIC(25,0) NULL,
UniqueID uniqueidentifier NULL,
ReadOnlyLSN NUMERIC(25,0) NULL ,
ReadWriteLSN NUMERIC(25,0) NULL,
BackupSizeInBytes BIGINT NULL,
SourceBlockSize INT NULL,
FileGroupID INT NULL,
LogGroupGUID UNIQUEIDENTIFIER NULL,
DifferentialBaseLSN NUMERIC(25,0)NULL,
DifferentialBaseGUID UNIQUEIDENTIFIER NULL,
IsReadOnly BIT NULL,
IsPresent BIT NULL,
TDEThumbprint VARBINARY(32) NULL
);

DECLARE @RestoreStatement NVARCHAR(max)
DECLARE @BackupFile NVARCHAR(max);
DECLARE @logical_data NVARCHAR(max)
DECLARE @logical_log NVARCHAR(max);

SET @BackupFile = N’$(MirrorRestorePath)\$(Database2Mirror).bak’

— RESTORE FILELISTONLY FROM DISK = N’$(MirrorRestorePath)\$(Database2Mirror).bak’
SET @RestoreStatement = N’RESTORE FILELISTONLY FROM DISK=N”’ + @BackupFile + ””

INSERT INTO @FileList EXEC(@RestoreStatement);

SET @logical_data = (SELECT LogicalName FROM @FileList WHERE Type = ‘D’ AND FileID = 1)
SET @logical_log = (SELECT LogicalName FROM @FileList WHERE Type = ‘L’ AND FileID = 2)

:SETVAR LogicalData @logical_data
:SETVAR LogicalLog @logical_log

— This step will need to be altered depending upon the logical filenames within
— the backup file and the intended restore location
PRINT ‘RESTORE – ing the $(Database2Mirror) Database from $(MirrorRestorePath) to $(MirrorServer) with NORECOVERY’

RESTORE DATABASE $(Database2Mirror)
FROM DISK = N’$(MirrorRestorePath)\$(Database2Mirror).bak’ WITH
MOVE $(LogicalData) TO ‘$(MirrorDatabaseMDFPath)\$(Database2Mirror).MDF’,
MOVE $(LogicalLog) TO ‘$(MirrorDatabaseLDFPath)\$(Database2Mirror).LDF’,
REPLACE, NORECOVERY
GO

PRINT ‘RESTORE – ing the $(Database2Mirror) Database Log from $(MirrorRestorePath) to $(MirrorServer) with NORECOVERY’
GO

RESTORE LOG $(Database2Mirror)
FROM DISK = N’$(MirrorRestorePath)\$(Database2Mirror).trn’
WITH NORECOVERY
GO

— Now Initialise the mirroring partnership
PRINT ‘Now Initialising the Mirror from $(MirrorServer) (Mirror) to $(PrincipalServer) (Principal)’
GO

ALTER DATABASE $(Database2Mirror)
SET PARTNER = ‘TCP://$(PrincipalHost):$(PrincipalServerListenerPort)’
GO

:CONNECT $(PrincipalServer) -l $(SQLCMDLOGINTIMEOUT)
PRINT ‘Now Initialising the Mirror from $(PrincipalServer) (Principal) back to $(MirrorServer) (Mirror)’
GO

ALTER DATABASE $(Database2Mirror)
SET PARTNER = ‘TCP://$(MirrorHost):$(MirrorServerListenerPort)’
GO

PRINT ‘Tidying Up the Backup Files’
GO

DECLARE @DeleteTime DATETIME
SET @DeleteTime = DATEADD(MINUTE, -10, GETDATE())

EXECUTE master.dbo.xp_delete_file 0, N’\\RIDGIANDC1\Downloads’, N’Bak’, @DeleteTime, 1
EXECUTE master.dbo.xp_delete_file 0, N’\\RIDGIANDC1\Downloads’, N’Trn’, @DeleteTime, 1

PRINT ‘All DONE!!’
GO

— Finally, check it is all working
SELECT
DB_NAME(sd.[database_id]) AS [Database Name],
sd.mirroring_role_desc,
sd.mirroring_partner_name,
sd.mirroring_state_desc,
sd.mirroring_witness_name,
sd.mirroring_witness_state_desc,
sd.mirroring_safety_level_desc
FROM
sys.database_mirroring AS SD
WHERE
mirroring_partner_name IS NOT NULL

After the usual 30 minutes of figuring out how to move variables between T-SQL and SQLCMD, it all started working like a charm.
So…. Enjoy… Please feel free to use / dissect / re-write to whatever end you need.