/*============================================================================
  File:     GenericDatabaseMirroringSetup.sql (a SQLCMD script)

  Summary:  A generic SQLCMD script for setting up Database Mirroring 
			in the High Availability configuration. LOTS of variables to
			define... be sure to review the entire script before execution.
			Based on the Database Mirroring Lab - DBM "In Action".
			Have fun!

  Date:     January 2008

  SQL Server Version: 9.00.3054.00 (SP2)
------------------------------------------------------------------------------
  Copyright (C) 2008 Kimberly L. Tripp, SYSolutions, Inc.
  Script provided for sample use. All rights reserved.

  For more scripts and sample code, check out 
    http://www.SQLskills.com

  This script is intended only as a supplement to demos and lectures
  given by SQLskills instructors.  
  
  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF 
  ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED 
  TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
  PARTICULAR PURPOSE.
============================================================================*/

:SETVAR PrincipalServer Servername\InstanceName
:SETVAR PrincipalDNS FullyQualifiedDNSName
:SETVAR PrincipalPort 5091

:SETVAR MirrorServer Servername\InstanceName
:SETVAR MirrorDNS FullyQualifiedDNSName
:SETVAR MirrorPort 5092

:SETVAR WitnessServer Servername\InstanceName
:SETVAR WitnessDNS FullyQualifiedDNSName
:SETVAR WitnessPort 5090

:SETVAR Database2Mirror DatabaseName
:SETVAR RestoreWithMove TRUE
:SETVAR BackupLocation \\servername\sharename\path
go

-- These are the parameters needed when using the VPC
--:SETVAR PrincipalServer SQLHAVPC\SQLDev01
--:SETVAR PrincipalDNS SQLHAVPC
--:SETVAR PrincipalPort 5091
--
--:SETVAR MirrorServer SQLHAVPC\SQLDev02
--:SETVAR MirrorDNS SQLHAVPC
--:SETVAR MirrorPort 5092
--
--:SETVAR WitnessServer SQLHAVPC\SQLExpress
--:SETVAR WitnessDNS SQLHAVPC
--:SETVAR WitnessPort 5090
--
--:SETVAR Database2Mirror TicketSalesDB
--:SETVAR RestoreWithMove TRUE
--:SETVAR BackupLocation "C:\AlwaysOn Labs\Database Mirroring Lab"
--go


:ON ERROR EXIT
go

:CONNECT $(PrincipalServer)

-- Mirroring ONLY supports the FULL Recovery Model
ALTER DATABASE $(Database2Mirror)
	SET RECOVERY FULL
go

CREATE ENDPOINT Mirroring
    STATE=STARTED 
    AS TCP (LISTENER_PORT=$(PrincipalPort)) 
    FOR DATABASE_MIRRORING (ROLE=ALL)
GO

:CONNECT $(MirrorServer)

CREATE ENDPOINT Mirroring
    STATE=STARTED 
    AS TCP (LISTENER_PORT=$(MirrorPort)) 
    FOR DATABASE_MIRRORING (ROLE=ALL)
GO

:CONNECT $(WitnessServer)

CREATE ENDPOINT Mirroring
    STATE=STARTED 
    AS TCP (LISTENER_PORT=$(WitnessPort)) 
    FOR DATABASE_MIRRORING (ROLE=WITNESS)
GO

:CONNECT $(PrincipalServer)

BACKUP DATABASE $(Database2Mirror)
TO DISK = '$(BackupLocation)\$(Database2Mirror).bak'
WITH INIT
GO

:CONNECT $(MirrorServer)

DECLARE @InstanceName	sql_variant,
		@InstanceDir	sql_variant,
		@SQLDataRoot	nvarchar(512),
		@ExecStr		nvarchar(max)

IF '$(RestoreWithMove)' = 'TRUE'
	BEGIN

		SELECT @InstanceName = ISNULL(SERVERPROPERTY('InstanceName'), 'MSSQLServer')

		EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 
		   'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL', 
			@InstanceName, @InstanceDir	OUTPUT

		SELECT @ExecStr = 'EXECUTE master.dbo.xp_regread '
			+ '''HKEY_LOCAL_MACHINE'', ' 
			+ '''SOFTWARE\Microsoft\Microsoft SQL Server\' 
			+ convert(varchar, @InstanceDir) 
			+ '\Setup'', ''SQLDataRoot'', @SQLDataRoot	OUTPUT'

		EXEC master.dbo.sp_executesql @ExecStr
			, N'@SQLDataRoot nvarchar(512) OUTPUT'
			, @SQLDataRoot OUTPUT

		IF @SQLDataRoot IS NULL
		BEGIN
			RAISERROR ('Did not find the correct SQL Data Root Directory. Cannot proceed. Databases backed up but not yet restored.', 16, -1)
		END

		CREATE TABLE #BackupFileList
		( LogicalName	sysname	NULL
			, PhysicalName	sysname	NULL
			, [Type]	char(1)
			, FileGroupName	sysname NULL
			, Size	bigint
			, MaxSize	bigint
			, FileId	smallint
			, CreateLSN	numeric(25,0)
			, DropLSN	numeric(25,0)
			, UniqueId uniqueidentifier
			, ReadOnlyLSN	numeric(25,0)
			, ReadWriteLSN	numeric(25,0)
			, BackupSizeInBytes	bigint
			, SourceBlockSize	bigint
			, FileGroupId		smallint
			, LogGroupGUID	uniqueidentifier
			, DifferentialBaseLSN	numeric(25,0)
			, DifferentialBaseGUID	uniqueidentifier
			, IsReadOnly	bit
			, IsPresent	bit
		)

		INSERT #BackupFileList
			EXEC('RESTORE FILELISTONLY FROM DISK = ''$(BackupLocation)\$(Database2Mirror).bak''')

		UPDATE #BackupFileList
			SET PhysicalName 
					= @SQLDataRoot 
						+ N'\Data\' 
						+ REVERSE(SUBSTRING(REVERSE(PhysicalName)
							, 1, PATINDEX('%\%', REVERSE(PhysicalName)) -1))

		DECLARE @LogicalName	sysname
			, @PhysicalName	sysname

		DECLARE FileListCursor CURSOR FAST_FORWARD FOR 
			SELECT LogicalName, PhysicalName
			FROM #BackupFileList

		OPEN FileListCursor

		FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName

		SELECT @ExecStr = N'RESTORE DATABASE $(Database2Mirror)' +
			N' FROM DISK = ''$(BackupLocation)\$(Database2Mirror).bak'' '

		SELECT @ExecStr = @ExecStr + N' WITH MOVE ''' 
							+ @LogicalName + N''' TO ''' + @PhysicalName + N''''
			
		FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName

		WHILE @@FETCH_STATUS <> -1
		BEGIN
			SELECT @ExecStr = @ExecStr + N', MOVE ''' + @LogicalName 
								+ ''' TO ''' + @PhysicalName + ''''
			FETCH NEXT FROM FileListCursor INTO @LogicalName, @PhysicalName
		END

		-- NORECOVERY is required for Database Mirroring, replace is not. 
		-- Replace is used here solely to allow repetitive use of this script.
		SELECT @ExecStr = @ExecStr + N' , NORECOVERY, REPLACE'

		DEALLOCATE FileListCursor

	END
ELSE
	BEGIN
		-- NORECOVERY is required for Database Mirroring, replace is not. 
		-- Replace is used here solely to allow repetitive use of this script.
		SELECT @ExecStr = N'RESTORE DATABASE $(Database2Mirror)' +
			N' FROM DISK = ''$(BackupLocation)\$(Database2Mirror).bak'' ' +
			N'WITH NORECOVERY, REPLACE'
	END

-- Useful for testing
-- Only return the string and then comment out the EXEC line below.
-- SELECT @ExecStr

EXEC (@ExecStr)
GO

:CONNECT $(PrincipalServer)

-- Backup the transaction log to get these two databases closer to current.
BACKUP LOG $(Database2Mirror)
TO DISK = '$(BackupLocation)\$(Database2Mirror)_Log.bak'
WITH INIT
GO

:CONNECT $(MirrorServer)

-- Restore the transaction log.
RESTORE LOG $(Database2Mirror)
FROM DISK = '$(BackupLocation)\$(Database2Mirror)_Log.bak'
WITH NORECOVERY
GO

-- Establish the mirroring partnership
ALTER DATABASE $(Database2Mirror)
    SET PARTNER = 'TCP://$(PrincipalDNS):$(PrincipalPort)'
GO

:CONNECT $(PrincipalServer)

ALTER DATABASE $(Database2Mirror)
	SET PARTNER = 'TCP://$(MirrorDNS):$(MirrorPort)'
GO

ALTER DATABASE $(Database2Mirror)
	SET WITNESS = 'TCP://$(WitnessDNS):$(WitnessPort)'
GO

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	sd.[database_id] = db_id(N'$(Database2Mirror)')
GO

--------------------------------------------------------------------
-- CLEAN UP SECTION
--------------------------------------------------------------------
--
-- BEGIN CLEANUP...

-- --Copy your variables here...

--GO
--
--:CONNECT $(PRINCIPALSERVER)
--
--USE MASTER
--GO
--
--SELECT * FROM SYS.DATABASE_MIRRORING_ENDPOINTS
--SELECT * FROM SYS.ENDPOINTS
--SELECT * FROM SYS.TCP_ENDPOINTS
--GO
--
--ALTER DATABASE $(DATABASE2MIRROR)
--    SET PARTNER OFF
--GO
--
--DROP ENDPOINT MIRRORING
--GO
--
----DROP DATABASE $(DATABASE2MIRROR)
----GO
--
--:CONNECT $(MIRRORSERVER)
--
--DROP ENDPOINT MIRRORING
--GO
--
--ALTER DATABASE $(DATABASE2MIRROR)
--    SET PARTNER OFF
--GO
--DROP DATABASE $(DATABASE2MIRROR)
--GO
--
--:CONNECT $(WITNESSSERVER)
--
--DROP ENDPOINT MIRRORING
--GO
--
---- TO RESTORE ADVENTUREWORKS BACK TO THE ORIGINAL VERSION,
---- PLEASE REFER TO THIS SECTION OF THE BOOKS ONLINE: 
---- MS-HELP://MS.SQLCC.V9/MS.SQLSVR.V9.EN/INSTSQL9/HTML/7DE6CEB0-BE7A-4309-894B-A0963DC6C27B.HTM
--
------ END CLEANUP...