Generate SQL Server Mirroring quick hacks

generate-mirroring-backup-ops.sql

--
-- generates T-SQL commands for full and log backups
--
DECLARE	@BackupPath sysname = N'\\192.168.1.2\SyncMirror\';

WITH cte AS
(
	-- use databases already configured for mirroring
	SELECT	DB_NAME(database_id) AS database_name
	FROM	sys.database_mirroring
	WHERE	mirroring_state IS NOT NULL
	-- or use a specific list
	-- SELECT	database_name
	-- FROM	(VALUES ('database1'), ('database2')) AS T(database_name)
)
SELECT
		'BACKUP DATABASE [' + database_name + '] TO DISK = '''
	+	@BackupPath + database_name + '_init_mirror.bak'' WITH INIT, COMPRESSION, CHECKSUM;'
	+	CHAR(10)
	+	'BACKUP LOG [' + database_name + '] TO DISK = '''
	+	@BackupPath + database_name + '_init_mirror.trn'' WITH INIT, COMPRESSION, CHECKSUM;'
	+	CHAR(10) + 'GO'
FROM	cte
ORDER BY database_name
GO

generate-mirroring-common-ops.sql

--
-- generates T-SQL commands for common database mirroring
-- operations across all databases configured for mirroring
--
SELECT
	'ALTER DATABASE [' + DB_NAME(database_id) + '] SET PARTNER SUSPEND;' AS sql_stmt_partner_suspend
,	'ALTER DATABASE [' + DB_NAME(database_id) + '] SET PARTNER RESUME;' AS sql_stmt_partner_resume
,	'ALTER DATABASE [' + DB_NAME(database_id) + '] SET PARTNER OFF;' AS sql_stmt_partner_off
,	'ALTER DATABASE [' + DB_NAME(database_id) + '] SET PARTNER FAILOVER;' AS sql_stmt_partner_failover
,	'ALTER DATABASE [' + DB_NAME(database_id) + '] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS;' AS sql_stmt_partner_allow_data_loss
FROM	sys.database_mirroring
WHERE	mirroring_state IS NOT NULL
ORDER BY DB_NAME(database_id)
GO

generate-mirroring-restore-ops.sql

--
-- generates T-SQL commands for full and log backups of
--
DECLARE	@BackupPath sysname = N'\\192.168.1.2\SyncMirror\';

WITH cte AS
(
	-- use databases already configured for mirroring
	SELECT	DB_NAME(database_id) AS database_name
	FROM	sys.database_mirroring
	WHERE	mirroring_state IS NOT NULL
	-- or use a specific list
	-- SELECT	database_name
	-- FROM	(VALUES ('database1'), ('database2')) AS T(database_name)
)
SELECT
		'RESTORE DATABASE [' + database_name + '] FROM DISK = '''
	+	@BackupPath + database_name + '_init_mirror.bak'' WITH FILE = 1, CHECKSUM, NORECOVERY;'
	+	CHAR(10)
	+	'RESTORE LOG [' + database_name + '] FROM DISK = '''
	+	@BackupPath + database_name + '_init_mirror.trn'' WITH FILE = 1, CHECKSUM, RECOVERY;'
	+	CHAR(10) + 'GO'
FROM	cte
ORDER BY database_name
GO

generate-mirroring-set-partner.sql

--
-- generates T-SQL commands for full and log backups of
--
DECLARE	@PrincipalEndPointName sysname = N'TCP://servername.domain.local:5022';
DECLARE	@MirrorEndPointName sysname = N'TCP://servername.domain.local:5022';

WITH cte AS
(
	-- use databases already configured for mirroring
	SELECT	DB_NAME(database_id) AS database_name
	FROM	sys.database_mirroring
	WHERE	mirroring_state IS NOT NULL
	-- or use a specific list
	-- SELECT	database_name
	-- FROM	(VALUES ('database1'), ('database2')) AS T(database_name)
)
SELECT
	'ALTER DATABASE [' + database_name + '] SET PARTNER = ''' + @PrincipalEndPointName + ''';' AS set_partner_on_mirror
,	'ALTER DATABASE [' + database_name + '] SET PARTNER = ''' + @MirrorEndPointName + ''';' AS set_partner_on_principal
FROM	cte
ORDER BY database_name
GO