I created a series of batch files and SQL scripts to do the steps below. I only included the scripts for the mirroring stuff but the other referenced SQL scripts are pretty basic SQL commands (except the restore database script, that one is kind of tricky due to how I've implemented it) and creating them will give you an opportunity to change the SQLCMD mode variables. If you want to Direct Message me on twitter (@jcumberland) with your email address I can send you the full set of scripts. I have removed the full paths on the file names as well.
- sets recovery mode for database
- removes mirroring
- remove mirror database
- backup database
- backup log
- copies bak file
- copies trn file
- restores database backup
- restores log backup
- deletes bak file
- deletes trn file
- set up mirroring between the principal and mirror
/*------------------------------------------------------*/ -- REPLACE THESE VALUES /*------------------------------------------------------*/ /*------------------------------------------------------*/ -- BATCH FILE 1, CALLS UTILITY BATCH FILE /*------------------------------------------------------*/ @echo off set PRIMARYHOST= set MIRRORHOST= set INST= set DRIVE= set DRTARGETDIR="%DRIVE%:\MSSQL.1\MSSQL\DATA\" set LOGDIR="%DRIVE%:\MSSQL.1\MSSQL\DATA\" set PORT= set PRIMARYINST=%PRIMARYHOST%\%INST% set MIRRORINST=%MIRRORHOST%\%INST% @echo on call drdbsetup.bat %PRIMARYHOST% %MIRRORHOST% %PRIMARYINST% %MIRRORINST% %DRIVE% %DRTARGETDIR% %PORT% %LOGDIR% pause /*------------------------------------------------------*/ -- BATCH FILE 2 - CALLS THE SQL SCRIPTS /*------------------------------------------------------*/ rem -- set recovery mode to full sqlcmd /E /S%3 -i SetRecoveryMode.sql -v DATABASE="%8" rem -- remove mirroring sqlcmd /E /S%3 -i MirroringRemove.sql -v DATABASE="%8" sqlcmd /E /S%4 -i MirroringRemoveMirror.sql -v DATABASE="%8" rem -- full backup sqlcmd /E /S%3 -i BackupDatabaseFull.sql -v BACKUPPATH="%5:\" -v DATABASE=%8 rem -- log backup sqlcmd /E /S%3 -i BackupDatabaseLog.sql -v BACKUPPATH="%5:\" -v DATABASE=%8 rem -- copy backup files to mirror copy \\%1\%5$\*.bak %6 /y copy \\%1\%5$\*.trn %6 /y rem -- remove backup files from primary del \\%1\%5$\*.bak del \\%1\%5$\*.trn rem -- restore database from backup directory sqlcmd /E /S%4 -i RestoreDatabase.sql -v BKDIR=%6 -v DATADIR=%6 -v LOGDIR=%9 rem -- restore database log sqlcmd /E /S%4 -i RestoreLog.sql -v BACKUPPATH=%6 -v DATABASE=%8 rem -- remove backup files from mirror del %6\*.bak del %6\*.trn rem -- set up mirroring sqlcmd /E /S%4 -i MirroringSetup.sql -v PRINCIPAL="%3" -v MIRROR="%4" -v PRINCIPAL_DNS="%1." -v MIRROR_DNS="%2.yourdomain.local" -v DATABASE_NAME="%8" -v PORT=%7 /*------------------------------------------------------*/ -- MirroringSetup.sql /*------------------------------------------------------*/ /*-----------------------------------------------------*/ -- run on principal /*-----------------------------------------------------*/ :connect $(PRINCIPAL) GO -- creates endpoint if not exists (select * from sys.endpoints where name = 'Mirroring') begin CREATE ENDPOINT Mirroring AUTHORIZATION [] STATE = STARTED AS TCP ( LISTENER_PORT = $(PORT) ,LISTENER_IP = ALL ) FOR DATA_MIRRORING ( ROLE = PARTNER ,AUTHENTICATION = WINDOWS NEGOTIATE ,ENCRYPTION = REQUIRED ALGORITHM RC4 ) end go /*-----------------------------------------------------*/ -- run on mirror /*-----------------------------------------------------*/ :connect $(MIRROR) GO -- creates endpoint if not exists (select * from sys.endpoints where name = 'Mirroring') begin CREATE ENDPOINT Mirroring AUTHORIZATION [] STATE = STARTED AS TCP ( LISTENER_PORT = $(PORT) ,LISTENER_IP = ALL ) FOR DATA_MIRRORING ( ROLE = PARTNER ,AUTHENTICATION = WINDOWS NEGOTIATE ,ENCRYPTION = REQUIRED ALGORITHM RC4 ) end go -- Sets up mirror partnership ALTER DATABASE [$(DATABASE_NAME)] SET PARTNER = N'TCP://$(PRINCIPAL_DNS):$(PORT)' go -- Removes mirror monitoring if exists (select * from msdb..sysjobs where [name] ='Database Mirroring Monitor Job') begin EXEC sp_dbmmonitordropmonitoring end go -- Sets up mirror monitoring EXEC sys.sp_dbmmonitoraddmonitoring -- default is 1 minute go /*-----------------------------------------------------*/ -- run on principal /*-----------------------------------------------------*/ :connect $(PRINCIPAL) GO -- Sets up mirror partnership ALTER DATABASE [$(DATABASE_NAME)] SET PARTNER = N'TCP://$(MIRROR_DNS):$(PORT)' go -- Removes mirror monitoring if exists (select * from msdb..sysjobs where [name] ='Database Mirroring Monitor Job') begin EXEC sp_dbmmonitordropmonitoring end go -- Sets up mirror monitoring EXEC sys.sp_dbmmonitoraddmonitoring -- default is 1 minute go /*------------------------------------------------------*/ -- DRMirroringRemove.sql /*------------------------------------------------------*/ if exists ( select * from sys.database_mirroring where db_name(database_id) = '$(DATABASE)' and mirroring_guid is not null ) begin ALTER DATABASE [$(DATABASE)] SET PARTNER OFF end go WAITFOR DELAY '00:00:10' go /*------------------------------------------------------*/ -- DRMirroringRemoveMirror.sql /*------------------------------------------------------*/ if exists ( select * from sys.database_mirroring where db_name(database_id) = '$(DATABASE)' and mirroring_guid is not null ) begin ALTER DATABASE [$(DATABASE)] SET PARTNER OFF end go WAITFOR DELAY '00:00:10' go DROP DATABASE [$(DATABASE)] go
