Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Scripted Setup for Mirroring from the Principal
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Julien.Crawford
Starting Member

Australia
21 Posts

Posted - 09/13/2011 :  19:21:43  Show Profile  Reply with Quote
So, I wanted to script the setup of mirroring in our Dev/QA environments so I could build it as needed.
This in turn lead me to want to apply the same script in production - so everything matches. Fine.

I was really annoyed that the SQL Server Managment Studio GUI means did not allow me to capture the SQL with their 'script to' feature.

So, after failing a lot and breaking things a lot, I finally managed to build a script that is 'mostly' generic. There are still some points where its hard coded and if you use this, you will need to
tweak these to use your user name etc.

This runs Only on the principal (or the machine that will be the principal).
Fill in the sections at the beginning with your data.

DISCLAIMER :: This is not a complete generic tool for you to use 'out of the box'. Its just what I use and I felt it had lots of useful things in it.



-- *
-- * 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


sqldba23
Starting Member

1 Posts

Posted - 03/10/2015 :  04:26:00  Show Profile  Reply with Quote
Hi Julien, thanks for the above code. When trying to use your script, I get : Msg 11527, Level 16, State 1, Procedure sp_describe_first_result_set, Line 280
The metadata could not be determined because statement 'RESTORE DATABASE [_XYZ] FROM DISK = N'\\dummy-sql-1\SQL_Backups\vs-sql-admin\_XYZ.bak' WITH FILE = 1' does not support metadata discovery. Any ideas how to resolve this error?
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000