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
