-- *
-- * Just to prevent accidental F5's
-- *
RAISERROR('*** Explicit Disconnect - Careful what you run.', 20, 0) with log
go

-----------------------------------------------------------------------------------------
-- Start of Data Entry Section.
-----------------------------------------------------------------------------------------
-- *
-- * Specify Principal, Mirror and Witness and store in TEMPDB.
-- *
if (object_id('tempdb..#mirroring_info') is not NULL)
	drop table #mirroring_info
go
select	'WIN2008R2' principal_host, 'WIN2008R2\SMRT01' principal_instance
	, 	'WIN2008' mirror_host, 'WIN2008\SMRT02' mirror_instance
	,	'WIN2008R2' witness_host, 'WIN2008R2\smart02witness' witness_instance
	,	'E:\SMART02\Data'	mirror_data
	,	'E:\SMART02\Log'	mirror_log
	,	'\\WIN2008\C$\Temp\SMRT01'	backup_to
	,	'C:\Temp\SMRT01'	backup_from
into	#mirroring_info
go

-- *
-- * The Databases to 
-- *
if (object_id('tempdb..#databases') is not NULL)
	drop table #databases
go
create table #databases (name sysname)
go
insert into #databases select 'dba'
insert into #databases select 'dwpd'
insert into #databases select 'dwpd_2011'
insert into #databases select 'speed_router'
insert into #databases select 'stg2'
go

-----------------------------------------------------------------------------------------
-- End of Data Entry Section.
-----------------------------------------------------------------------------------------
if (object_id('tempdb..#filelist') is not NULL)
	drop table #filelist
go
CREATE TABLE #filelist
	(
	LogicalName NVARCHAR (128),
	PhysicalName NVARCHAR (260),
	Type NCHAR (1),
	FileGroupName NVARCHAR (128),
	Size BIGINT,
	MaxSize BIGINT,
	FileId BIGINT,
	CreateLSN NUMERIC (25, 0),
	DropLSN NUMERIC (25, 0),
	UniqueId UNIQUEIDENTIFIER,
	ReadOnlyLSN NUMERIC (25, 0),
	ReadWriteLSN NUMERIC (25, 0),
	BackupSizeInBytes BIGINT,
	SourceBlockSize INT,
	FileGroupId INT,
	LogGroupGUID UNIQUEIDENTIFIER,
	DifferentialBaseLSN NUMERIC (25, 0),
	DifferentialBaseGUID UNIQUEIDENTIFIER,
	IsReadOnly BIT,
	IsPresent BIT,
	TDEThumbprint VARBINARY,
	id int identity
	)
go


-- *
-- * If we are NOT connected to the principal then stop (ie: Disconnect).
-- *
if @@servername <> (select principal_instance from #mirroring_info)
begin
	RAISERROR('Explicit Disconnect - You must be connected to the PRINCIPAL for this operation.', 20, 0) with log
end
go
USE master
go

-- *
-- * If there is No linked server, create it now.
-- *
if not exists (select * from master..sysservers where srvname = 'SOR_MIRROR')
begin
	declare @provstr_s nvarchar(512)
	select	@provstr_s = 'DRIVER={SQL Server};SERVER='+mirror_instance+';Integrated Security=SSPI;'
	from	#mirroring_info
	
	EXEC sp_addlinkedserver @server = 'SOR_MIRROR', @srvproduct = '', @provider = 'MSDASQL', @provstr = @provstr_s
end
GO

if not exists (select * from master..sysservers where srvname = 'SOR_WITNESS')
begin
	declare @provstr_s nvarchar(512)
	select	@provstr_s = 'DRIVER={SQL Server};SERVER='+witness_instance+';Integrated Security=SSPI;'
	from	#mirroring_info
	
	EXEC sp_addlinkedserver @server = 'SOR_WITNESS', @srvproduct = '', @provider = 'MSDASQL', @provstr = @provstr_s
end
GO


-- *
-- * Remove Any mirroring and Restoring status from the principal!
-- *
declare @dbname sysname
set		@dbname = ''
while 1=1
begin
	select @dbname = min(name) from #databases where name > @dbname
	if @dbname is null
		break

	if exists (select * from master.sys.database_mirroring where database_id = db_id(@dbname) and mirroring_witness_name is not null)
		exec ('ALTER DATABASE ['+@dbname+'] SET WITNESS OFF')

	if exists (select * from master.sys.database_mirroring where database_id = db_id(@dbname) and mirroring_partner_name is not null)
		exec ('ALTER DATABASE ['+@dbname+'] SET PARTNER OFF')

end
go

declare @dbname sysname
set		@dbname = ''
while 1=1
begin
	select @dbname = min(name) from #databases where name > @dbname
	if @dbname is null
		break

	if exists (select * from master.sys.databases where database_id = db_id(@dbname) and state_desc = 'RESTORING')
		exec ('RESTORE LOG ['+@dbname+'] WITH RECOVERY')
end
go



-- *
-- * On Principal
-- * backup databases and logs directly to the MIRROR host, ready for recovery!
-- *
declare @filename nvarchar(512)
declare @dbname sysname
set		@dbname = ''
while 1=1
begin
	select @dbname = min(name) from #databases where name > @dbname
	if @dbname is null
		break

	select @filename = backup_to + '\\' + @dbname + '.bak' from #mirroring_info

	exec ('BACKUP DATABASE ['+@dbname+'] TO  DISK = '''+@filename+''' WITH NOFORMAT, INIT,  NAME = N'''+@dbname+'-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10')
	exec ('BACKUP LOG 	   ['+@dbname+'] TO  DISK = '''+@filename+''' WITH NOFORMAT, NOINIT,NAME = N'''+@dbname+'-Tran Log  Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10')
end
go

-- *
-- * On Mirror
-- * turn off mirroring on databases
-- * drop databases
-- *
declare @filename nvarchar(512)
declare @dbname sysname
set		@dbname = ''
while 1=1
begin
	select @dbname = min(name) from #databases where name > @dbname
	if @dbname is null
		break

	-- Can not drop the database if its busy mirroring!
	exec ('SELECT * FROM OPENQUERY([SOR_MIRROR],  ''if exists (select * from master.sys.database_mirroring where database_id = db_id('''+@dbname+''') and mirroring_partner_name is not null) ALTER DATABASE ['+@dbname+'] SET PARTNER OFF; select @@error'')')
	exec ('SELECT * FROM OPENQUERY([SOR_MIRROR],  ''DROP DATABASE ['+@dbname+']; select @@error'')')
end
go



-- *
-- * restore databases and log of databases	-- Ensuring any File Moves are kept!
-- *
declare @sql varchar(8000)
declare @dbname sysname
declare @backupfrom sysname
declare @mirror_data sysname
declare @mirror_log sysname
set @dbname = ''
while 1=1
begin
	select @dbname = min(name) from #databases where name > @dbname
	if @@rowcount = 0 or @dbname is null
		break
		
	-- Build the table of files needed for all the MOVE's 
	select	@backupfrom = backup_from
		,	@mirror_data = mirror_data
		,	@mirror_log =  mirror_log
	from	#mirroring_info 
	
	select @sql = 'RESTORE FILELISTONLY FROM DISK = '''+@backupfrom+'\'+@dbname+'.bak'''
	
	truncate table #filelist
	insert into #filelist exec (@sql)
--	select * from #filelist

	-- Now start building the command.
	select @sql = 'RESTORE DATABASE ['+@dbname+'] FROM  DISK = N'''+@backupfrom+'\'+@dbname+'.bak'' WITH  FILE = 1'
	
	-- Now loop through the Files.
	declare @fileId bigint
	declare @PhysicalName nvarchar(260)
	declare @LogicalName nvarchar(260)
	declare @Type nchar(1)
	declare @id int
	set @id = 1
	while 1=1
	begin
		select	@fileId = fileId
			,	@PhysicalName = PhysicalName
			,	@LogicalName = LogicalName
			,	@Type = Type
		from	#filelist
		where	id = @id
		
		if (@@rowcount = 0 or @fileId is null)
			break
		
		-- Just the name of the file without the path please.		
		set @PhysicalName = reverse(@PhysicalName)
		set @PhysicalName = substring(@PhysicalName, 0, charindex('\', @PhysicalName, 1))
		set @PhysicalName = reverse(@PhysicalName)
		
		if (@Type = 'D')
			set @sql = @sql + ', MOVE N'''+@LogicalName+''' TO N'''+@mirror_data+'\'+@PhysicalName+''''
		else
			set @sql = @sql + ', MOVE N'''+@LogicalName+''' TO N'''+@mirror_log+'\'+@PhysicalName+''''
		
		set @id = @id + 1
	end
	set @sql = @sql + ',  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 10'

	
	set @sql = 'SELECT * FROM OPENQUERY([SOR_MIRROR], ''' + replace(@sql,'''', '''''') + '; select @@error'')'
	print @sql
	exec (@sql)

	set @sql = 'SELECT * FROM OPENQUERY([SOR_MIRROR], ''RESTORE LOG ['+@dbname+'] FROM  DISK = N'''''+@backupfrom+'\'+@dbname+'.bak'''' WITH  FILE = 2,  NORECOVERY,  NOUNLOAD,  STATS = 10; select @@error'')'
	print @sql
	exec (@sql)

end
go

-- *
-- * Drop all the EndPoints so we can re-create them.
-- *
declare @MirrorName varchar(255)

SELECT @MirrorName = name FROM OPENQUERY([SOR_MIRROR], 'select name from sys.tcp_endpoints where type_desc = ''DATABASE_MIRRORING''')
if (@MirrorName is not null)
	exec ('SELECT * FROM OPENQUERY([SOR_MIRROR],  ''DROP ENDPOINT ['+@MirrorName+']; select @@error'')')

SELECT @MirrorName = name FROM OPENQUERY([SOR_WITNESS], 'select name from sys.tcp_endpoints where type_desc = ''DATABASE_MIRRORING''')
if (@MirrorName is not null)
	exec ('SELECT * FROM OPENQUERY([SOR_WITNESS], ''DROP ENDPOINT ['+@MirrorName+']; select @@error'')')

SELECT @MirrorName = name from sys.tcp_endpoints where type_desc = 'DATABASE_MIRRORING'
if (@MirrorName is not null)
	exec ('DROP ENDPOINT ['+@MirrorName+']')
go

-- *
-- * Create the EndPoints (if necessary)
-- *	1st on the mirror.
declare @sql varchar(8000)
declare @EndPointName varchar(100)
select @EndPointName = name from [SOR_MIRROR].master.sys.endpoints where type_desc = 'DATABASE_MIRRORING'
if (@EndPointName is null)
begin
	print '[SOR_MIRROR] : CREATE ENDPOINT [Mirroring]'
	set @sql = 'SELECT * FROM OPENQUERY([SOR_MIRROR],''CREATE ENDPOINT [Mirroring] AUTHORIZATION [ASIAPAC\jxc]	STATE=STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4); select @@error'')'
	exec (@sql)
	set @sql = 'SELECT * FROM OPENQUERY([SOR_MIRROR],''GRANT TAKE OWNERSHIP ON ENDPOINT::Mirroring TO [ASIAPAC\ap-sqldbeng] WITH GRANT OPTION; select @@error'')'
	exec (@sql)
	set @sql = 'SELECT * FROM OPENQUERY([SOR_MIRROR],''GRANT CONNECT ON ENDPOINT::[Mirroring] TO [asiapac\ap-sqldbeng]; select @@error'')'
	exec (@sql)
end

-- *	2nd on the witness.
select @EndPointName = name from [SOR_WITNESS].master.sys.endpoints where type_desc = 'DATABASE_MIRRORING'
if (@EndPointName is null)
begin
	print '[SOR_WITNESS] : CREATE ENDPOINT [Mirroring]'
	exec ('SELECT * FROM OPENQUERY([SOR_WITNESS],''CREATE ENDPOINT [Mirroring] AUTHORIZATION [ASIAPAC\jxc]	STATE=STARTED AS TCP (LISTENER_PORT = 5023, LISTENER_IP = ALL) FOR DATA_MIRRORING (ROLE = WITNESS, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4); select @@error'')')
	exec ('SELECT * FROM OPENQUERY([SOR_WITNESS],''GRANT TAKE OWNERSHIP ON ENDPOINT::Mirroring TO [ASIAPAC\ap-sqldbeng] WITH GRANT OPTION; select @@error'')')
	exec ('SELECT * FROM OPENQUERY([SOR_WITNESS],''GRANT CONNECT ON ENDPOINT::[Mirroring] TO [asiapac\ap-sqldbeng]; select @@error'')')
end

-- *	3rd on the principal.
if not exists (select * from master.sys.endpoints where type_desc = 'DATABASE_MIRRORING')
begin
	print 'principal : CREATE ENDPOINT [Mirroring]'
	exec ('CREATE ENDPOINT [Mirroring] AUTHORIZATION [ASIAPAC\jxc]	STATE=STARTED AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE, ENCRYPTION = REQUIRED ALGORITHM RC4)')
	exec ('GRANT TAKE OWNERSHIP ON ENDPOINT::Mirroring TO [ASIAPAC\ap-sqldbeng] WITH GRANT OPTION')
	exec ('GRANT CONNECT ON ENDPOINT::[Mirroring] TO [asiapac\ap-sqldbeng]')
end
go


-- *
-- * Configure MIRRORing!
-- *		On mirror 1st!!! This is important!
-- *
declare @domain nvarchar(512)
EXECUTE master..xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\services\Tcpip\Parameters','Domain', @domain OUTPUT

declare @principal_host varchar(512)
select	@principal_host = principal_host from #mirroring_info

declare @partner nvarchar(512)
SELECT	@partner = 'TCP://' + @principal_host + '.' + @domain 
		+ ':' + convert(varchar(10),port)
FROM master.sys.tcp_endpoints where type_desc = 'DATABASE_MIRRORING'
-- select @partner


declare @sql varchar(1024)
declare @dbname sysname
set		@dbname = ''
while 1=1
begin
	select @dbname = min(name) from #databases where name > @dbname
	if @dbname is null
		break

	set		@sql = 'SELECT * FROM OPENQUERY([SOR_MIRROR], ''ALTER DATABASE ['+@dbname+'] SET PARTNER = '''''+@partner+'''''; select @@error'')'
	print @sql
	exec (@sql)
end
go


-- *
-- * Configure MIRRORing!
-- *		On principal 2nd!!  This is important!
-- *
declare @domain nvarchar(512)
EXECUTE master..xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\services\Tcpip\Parameters','Domain', @domain OUTPUT

declare @mirror_host nvarchar(512)
declare @witness_host nvarchar(512)
select	@mirror_host = mirror_host, @witness_host = witness_host from #mirroring_info

declare @mirror nvarchar(512)
SELECT	@mirror = 'TCP://' + @mirror_host + '.' + @domain + ':' + convert(varchar(10),port)
FROM [SOR_MIRROR].master.sys.tcp_endpoints where type_desc = 'DATABASE_MIRRORING'


declare @witness nvarchar(512)
SELECT	@witness = 'TCP://' + @witness_host + '.' + @domain + ':' + convert(varchar(10),port)
FROM [SOR_WITNESS].master.sys.tcp_endpoints where type_desc = 'DATABASE_MIRRORING'
-- select @mirror, @witness


declare @sql varchar(1024)
declare @dbname sysname
set		@dbname = ''
while 1=1
begin
	select @dbname = min(name) from #databases where name > @dbname
	if @dbname is null
		break

	set @sql = 'ALTER DATABASE ['+@dbname+'] SET PARTNER = ''' + @mirror + ''''
	print @sql
	exec (@sql)

	set @sql = 'ALTER DATABASE ['+@dbname+'] SET WITNESS = ''' + @witness + ''''
	print @sql
	exec (@sql)
end
go