Remove database mirroring with SQLCMD

As an addition to my previous post, I just want to share this script to remove the database mirroring from a database with SQLCMD.  Again, make sure that you have activated the SQLCMD Mode in your management studio (Menu – Query – SQLCMD Mode).

Remark: The database on the mirror server will be removed.

/****DEFINE THE VARIABLES THAT ARE NECESSARY TO REMOVE SQL DATABASE MIRRORING***/
:setvar MYDATABASE [AdventureWorks2012]
:setvar PRINCIPAL SQLSRV01
:setvar MIRROR SQLSRV02

:CONNECT $(PRINCIPAL)
GO
ALTER DATABASE $(MYDATABASE) SET PARTNER OFF
GO
:CONNECT $(MIRROR)
GO
RESTORE DATABASE $(MYDATABASE) WITH RECOVERY
GO
DROP DATABASE $(MYDATABASE)
GO

Setup Database Mirroring with SQLCMD

Hi all, I just want to share this script.
Today I had to setup SQL Database Mirroring for 12 databases. Instead of configuring this manually with the SQL Server Management Studio, I created a script to implement database mirroring by using SQLCMD. The script must be executed in SQLCMD Mode in the management studio. To activate this select in the menu Query – SQLCMD Mode. On the mirror server, I’m using the default data and log path to store the database files and the log files.
Just fill in the correct variables:
Variable Description
MYDATABASE Database name that you want to mirror
PRINCIPAL Principal SQL Server
MIRROR Mirror SQL Server
WITNESS Witness SQL Server. If you don”t want to use automatic failover, leave the WITNESS variable empty
DOMAIN The domain of the servers
BACKUPPATH Specify where the full backup file should be stored
RESTOREPATH Specify where the path where the backup file will be stored on the mirror server
COPYPATH Specify the path where the backup files should be copied to
PrincipalListenerPort Principal listener port
MirrorListenerPort Mirror listener port
WitnessListenerPort Witness listener port
Timeout Seconds to wait until automatic failover
/****DEFINE THE VARIABLES THAT ARE NECESSARY TO SETUP SQL DATABASE MIRRORING***/
:setvar MYDATABASE [AdventureWorks2012]
:setvar PRINCIPAL SQLSRV01
:setvar MIRROR SQLSRV02
:setvar WITNESS “SQLWITNESS”
:setvar DOMAIN pieter.local
:setvar BACKUPPATH I:MSSQLBackupMirroring
:setvar RESTOREPATH I:MSSQLBackupMirroring
:setvar COPYPATH i$MSSQLBackupMirroring
:setvar PrincipalListenerPort 5022
:setvar MirrorListenerPort 5022
:setvar WitnessListenerPort 5022
:setvar Timeout 10
SET NOCOUNT ON
GO
–Verify if witness is defined
if ‘$(WITNESS)’ =”
print ‘*** WARNING: NO witness server has been defined ***’

–1. Create endpoints on principal, mirror and witness.
:CONNECT $(PRINCIPAL)
GO
–*** Creating endpoint on the principal ***
if exists (select 1 from sys.endpoints where name = ‘Mirroring’)
print ‘*** WARNING: Endpoint ”Mirroring’2014-03-28 19:35:36′ already exists on $(PRINCIPAL) ***’
else
CREATE ENDPOINT Mirroring STATE=STARTED AS tcp (listener_port=$(PrincipalListenerPort)) FOR database_mirroring (ROLE=all)
GO
:CONNECT $(MIRROR)
GO
–*** Creating endpoint on the mirror ***
if exists (select 1 from sys.endpoints where name = ‘Mirroring’)
print ‘*** WARNING: Endpoint ”Mirroring” already exists on $(MIRROR) ***’
else
CREATE ENDPOINT Mirroring STATE=STARTED AS tcp (listener_port=$(MirrorListenerPort)) FOR database_mirroring (ROLE=all)
GO
:CONNECT $(WITNESS)
GO
–*** Creating endpoint on the witness ***
if exists (select 1 from sys.endpoints where name = ‘Mirroring’)
print ‘*** WARNING: Endpoint ”Mirroring” already exists on $(WITNESS) or witness server was not defined ***’
else
CREATE ENDPOINT Mirroring STATE=STARTED AS tcp (listener_port=$(WitnessListenerPort)) FOR database_mirroring (ROLE=WITNESS)
–2. Take Full Backup and COPY backup files to the mirror
:CONNECT $(PRINCIPAL)
GO
print ‘*** Take full backup of principal database $(MYDATABASE) ***’
IF  (left(cast(SERVERPROPERTY(‘ProductVersion’)as varchar),5)=’10.00′ and SERVERPROPERTY(‘EngineEdition’)=3) OR (left(cast(SERVERPROPERTY(‘ProductVersion’)as varchar),5)=’10.50′ and SERVERPROPERTY(‘EngineEdition’)in(2,3))
BEGIN
BACKUP DATABASE $(MYDATABASE) TO DISK = ‘$(BACKUPPATH)$(MYDATABASE).bak’
WITH  COPY_ONLY, NOFORMAT, INIT,  NAME = ‘$(MYDATABASE) Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10,COMPRESSION
print ‘*** Take transaction log backup of principal database $(MYDATABASE) ***’
BACKUP LOG $(MYDATABASE) TO  DISK = ‘$(BACKUPPATH)$(MYDATABASE).trn’
WITH NOFORMAT, INIT,  NAME = ‘$(MYDATABASE) Transaction Log Backup’, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 10,COMPRESSION
END
ELSE
BEGIN
BACKUP DATABASE $(MYDATABASE) TO DISK = ‘$(BACKUPPATH)$(MYDATABASE).bak’
WITH  COPY_ONLY, NOFORMAT, INIT,  NAME = ‘$(MYDATABASE) Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
print ‘*** Take transaction log backup of principal database $(MYDATABASE) ***’
BACKUP LOG $(MYDATABASE) TO  DISK = ‘$(BACKUPPATH)$(MYDATABASE).trn’
WITH NOFORMAT, INIT,  NAME = ‘$(MYDATABASE) Transaction Log Backup’, SKIP, REWIND, NOUNLOAD, COMPRESSION,  STATS = 10
END
GO
print ‘*** Copy principal database $(MYDATABASE) from principal server $(PRINCIPAL) to mirror server $(MIRROR) ***’
!!ROBOCOPY $(BACKUPPATH) $(MIRROR)$(COPYPATH) $(MYDATABASE).*
GO

–3. Restore database on the mirror
:CONNECT $(MIRROR)
GO
print ‘*** Create database directories ***’
DECLARE @root_folder nvarchar(128);
EXECUTE [master].[dbo].[xp_instance_regread]
@rootkey = N’HKEY_LOCAL_MACHINE’,
@key = N’SoftwareMicrosoftMSSQLServerMSSQLServer’,
@value_name = N’DefaultData’,
@value = @root_folder OUTPUT;

select @root_folder=@root_folder + ‘$(MYDATABASE)’
EXEC master.dbo.xp_create_subdir @root_folder

EXECUTE [master].[dbo].[xp_instance_regread]
@rootkey = N’HKEY_LOCAL_MACHINE’,
@key = N’SoftwareMicrosoftMSSQLServerMSSQLServer’,
@value_name = N’DefaultLog’,
@value = @root_folder OUTPUT;

select @root_folder=@root_folder + ‘$(MYDATABASE)’
EXEC master.dbo.xp_create_subdir @root_folder

GO
print ‘*** Restore full backup of database $(MYDATABASE) ***’
RESTORE DATABASE $(MYDATABASE)
FROM  DISK = ‘$(RESTOREPATH)$(MYDATABASE).bak’
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10,REPLACE
GO
print ‘*** Restore transaction log backup of database $(MYDATABASE) ***’
RESTORE LOG $(MYDATABASE) FROM  DISK = ‘$(RESTOREPATH)$(MYDATABASE).trn’
WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 10
GO

–4.Activate Mirroring
:CONNECT $(MIRROR)
GO
print ‘*** Set partner on the Mirror DB ***’
ALTER DATABASE $(MYDATABASE) SET PARTNER = ‘TCP://$(PRINCIPAL).$(DOMAIN):$(PrincipalListenerPort)’
GO
:CONNECT $(PRINCIPAL)
GO
print ‘*** Set partner on the Principal DB ***’
ALTER DATABASE $(MYDATABASE) SET PARTNER = ‘TCP://$(MIRROR).$(DOMAIN):$(MirrorListenerPort)’
GO
print ‘*** Set PARTNER SAFETY FULL on the Principal***’
ALTER DATABASE $(MYDATABASE) SET PARTNER SAFETY FULL
GO
print ‘*** Declare the witness on the Principal ***’
if ‘$(WITNESS)’ <> ”
ALTER DATABASE $(MYDATABASE) SET WITNESS = ‘TCP://$(WITNESS).$(DOMAIN):$(WitnessListenerPort)’
print ‘*** Setting the timeout on the principal to $(TIMEOUT) seconds ***’
ALTER DATABASE $(MYDATABASE) SET PARTNER TIMEOUT $(TIMEOUT)
GO

If you should have remarks or suggestions, just let me know.
Happy mirroring!