Setting Up Database Mirroring

 1
 2--------------------------------------
 3-- Meta script : Script to STOP mirroring
 4-- Primary   server: SQLONE
 5-- Secondary server: SQLTWO
 6--------------------------------------
 7GO
 8:OUT $(TEMP)\deleteit1.txt
 9:CONNECT SQLONE
10GO
11:OUT STDOUT
12GO
13PRINT 'GO'
14PRINT ':CONNECT ' + @@servername;
15PRINT 'GO'
16PRINT 'PRINT ''----------------------'';';
17PRINT 'PRINT ''-- Script to STOP mirroring'';';
18PRINT 'PRINT ''-- Created : '+ CAST(GetDate() AS NVARCHAR(100))+''';';
19PRINT 'PRINT ''----------------------'';';
20PRINT '-- Server name: <a href="mailto:'+@@servername">'+@@servername</a>
21PRINT 'USE Master;'
22SET NOCOUNT ON;
23DECLARE @sql nvarchar(MAX);
24DECLARE @dbname nvarchar(255);
25BEGIN TRY DROP TABLE #db; END TRY BEGIN CATCH END CATCH;
26SELECT f.name into #db FROM sys.database_mirroring AS m INNER JOIN sys.databases AS f ON m.database_id = f.database_id WHERE (f.database_id > 4);
27
28DECLARE @sqltemplate nvarchar(MAX);
29SET @sqltemplate = '
30IF (EXISTS(SELECT f.name FROM sys.database_mirroring AS m INNER JOIN sys.databases AS f ON m.database_id = f.database_id WHERE f.name = <a href="mailto:''@dbname''">''@dbname''</a> AND mirroring_guid IS NOT NULL))
31BEGIN
32    PRINT ''-- Remove Mirroring Database "@dbname" ''
33    ALTER DATABASE [@dbname] SET PARTNER OFF;
34END;
35'
36WHILE ((SELECT count(*) FROM #db)>0) BEGIN
37SET @sql = '';
38SELECT TOP 1 @dbname = name , @sql = REPLACE(@sqltemplate, <a href="mailto:'@dbname'">'@dbname'</a>, name) FROM #db f ORDER BY f.name;
39DELETE FROM #db WHERE name = @dbname;
40PRINT @sql;
41END
42GO
43PRINT 'GO'
44GO
45:OUT $(TEMP)\deleteit1.txt
46GO
47:CONNECT SQLTWO
48GO
49:OUT STDOUT
50GO
51PRINT 'GO'
52PRINT ':CONNECT ' + @@servername;
53PRINT 'GO'
54PRINT 'PRINT ''----------------------'';';
55PRINT 'PRINT ''-- Script to STOP mirroring'';';
56PRINT 'PRINT ''-- Created : '+ CAST(GetDate() AS NVARCHAR(100))+''';';
57PRINT 'PRINT ''----------------------'';';
58PRINT '-- Server name: <a href="mailto:'+@@servername">'+@@servername</a>
59PRINT 'USE Master;'
60SET NOCOUNT ON;
61DECLARE @sql nvarchar(MAX);
62DECLARE @dbname nvarchar(255);
63BEGIN TRY DROP TABLE #db; END TRY BEGIN CATCH END CATCH;
64SELECT f.name into #db FROM sys.database_mirroring AS m INNER JOIN sys.databases AS f ON m.database_id = f.database_id WHERE (f.database_id > 4);
65
66DECLARE @sqltemplate nvarchar(MAX);
67SET @sqltemplate = '
68IF (EXISTS(SELECT f.name FROM sys.database_mirroring AS m INNER JOIN sys.databases AS f ON m.database_id = f.database_id WHERE f.name = <a href="mailto:''@dbname''">''@dbname''</a> AND mirroring_guid IS NOT NULL))
69BEGIN
70    PRINT ''-- Remove Mirroring Database "@dbname" ''
71    ALTER DATABASE [@dbname] SET PARTNER OFF;
72END;
73'
74WHILE ((SELECT count(*) FROM #db)>0) BEGIN
75SET @sql = '';
76SELECT TOP 1 @dbname = name , @sql = REPLACE(@sqltemplate, <a href="mailto:'@dbname'">'@dbname'</a>, name) FROM #db f ORDER BY f.name;
77DELETE FROM #db WHERE name = @dbname;
78PRINT @sql;
79END
80GO
81PRINT 'GO'
82GO
83:OUT $(TEMP)\deleteit2.txt
84GO

 1
 2PRINT 'PRINT ''----------------------'';';
 3PRINT 'PRINT ''-- Script to clean Certificates, MasterKey, EndPoints'';';
 4PRINT 'PRINT ''-- Created : '+ CAST(GetDate() AS NVARCHAR(100))+''';';
 5PRINT 'PRINT ''----------------------'';';
 6PRINT '-- Server name : ' + @@servername
 7
 8DECLARE @template nvarchar(MAX);
 9DECLARE @sql nvarchar(MAX);
10
11PRINT '-- DROP ENDPOINT'
12SET @sql = '';
13SET @template = 'IF EXISTS ( SELECT * FROM sys.tcp_endpoints WHERE name = ''@endpointName'') DROP ENDPOINT [@endpointName];';
14SELECT @sql = replace(@template,'@endpointName',[name]) FROM sys.tcp_endpoints WHERE [type] = 4;
15IF (@sql <> '') PRINT @sql;
16PRINT 'GO';
17
18SET @sql = '';
19IF (EXISTS(SELECT database_name = d.name , dek.encryptor_type , cert_name = c.name FROM sys.dm_database_encryption_keys dek LEFT JOIN sys.certificates c ON dek.encryptor_thumbprint = c.thumbprint INNER JOIN sys.databases d ON dek.database_id = d.database_id WHERE d.database_id > 4)) BEGIN
20SELECT @sql = @sql + 'Database : [' + d.name + '] uses certificate ['+c.name+']'+char(13) FROM sys.dm_database_encryption_keys dek LEFT JOIN sys.certificates c ON dek.encryptor_thumbprint = c.thumbprint INNER JOIN sys.databases d ON dek.database_id = d.database_id WHERE d.database_id > 4
21PRINT 'PRINT ''-- ATTENTION : The master key and some certificates cannot be dropped if it is used by a database'';'
22PRINT '/* ';
23PRINT @sql + '*/';
24END;
25PRINT '-- DROP CERTIFICATE';
26SET @sql = '';
27SET @template = 'IF EXISTS ( SELECT * FROM sys.certificates WHERE name = ''@certificateName'' ) DROP CERTIFICATE [@certificateName];';
28SELECT @sql = @sql + replace(@template,'@certificateName',[name]) FROM sys.certificates WHERE pvt_key_encryption_type = 'MK';
29IF (@sql <> '') PRINT @sql;
30PRINT 'GO';
31
32PRINT '-- DROP MASTER KEY';
33SET @sql = '';
34SET @sql = 'IF EXISTS ( SELECT * FROM sys.symmetric_keys WHERE [name] LIKE ''%DatabaseMasterKey%'') DROP MASTER KEY;'
35IF (@sql <> '') PRINT @sql;
36PRINT 'GO';

 1
 2--------------------------------------
 3-- Meta script : Script to clean Certificates, MasterKey, EndPoints
 4-- Primary   server: SQLONE
 5-- Secondary server: SQLTWO
 6--------------------------------------
 7GO
 8:OUT $(TEMP)\deleteit.txt
 9GO
10:CONNECT SQLONE
11GO
12:OUT STDOUT
13GO
14PRINT ':CONNECT '+@@servername;
15PRINT 'PRINT ''----------------------'';';
16PRINT 'PRINT ''-- Script to clean Certificates, MasterKey, EndPoints'';';
17PRINT 'PRINT ''-- Created : '+ CAST(GetDate() AS NVARCHAR(100))+''';';
18PRINT 'PRINT ''----------------------'';';
19PRINT '-- Server name : ' + @@servername
20
21DECLARE @template nvarchar(MAX);
22DECLARE @sql nvarchar(MAX);
23
24PRINT '-- DROP ENDPOINT'
25SET @sql = '';
26SET @template = 'IF EXISTS ( SELECT * FROM sys.tcp_endpoints WHERE name = ''@endpointName'') DROP ENDPOINT [@endpointName];';
27SELECT @sql = replace(@template,'@endpointName',[name]) FROM sys.tcp_endpoints WHERE [type] = 4;
28IF (@sql <> '') PRINT @sql;
29PRINT 'GO';
30
31SET @sql = '';
32IF (EXISTS(SELECT database_name = d.name , dek.encryptor_type , cert_name = c.name FROM sys.dm_database_encryption_keys dek LEFT JOIN sys.certificates c ON dek.encryptor_thumbprint = c.thumbprint INNER JOIN sys.databases d ON dek.database_id = d.database_id WHERE d.database_id > 4)) BEGIN
33SELECT @sql = @sql + 'Database : [' + d.name + '] uses certificate ['+c.name+']'+char(13) FROM sys.dm_database_encryption_keys dek LEFT JOIN sys.certificates c ON dek.encryptor_thumbprint = c.thumbprint INNER JOIN sys.databases d ON dek.database_id = d.database_id WHERE d.database_id > 4
34PRINT 'PRINT ''-- ATTENTION : The master key and some certificates cannot be dropped if it is used by a database'';'
35PRINT '/* ';
36PRINT @sql + '*/';
37END;
38PRINT '-- DROP CERTIFICATE';
39SET @sql = '';
40SET @template = 'IF EXISTS ( SELECT * FROM sys.certificates WHERE name = ''@certificateName'' ) DROP CERTIFICATE [@certificateName];';
41SELECT @sql = @sql + replace(@template,'@certificateName',[name]) FROM sys.certificates WHERE pvt_key_encryption_type = 'MK';
42IF (@sql <> '') PRINT @sql;
43PRINT 'GO';
44
45PRINT '-- DROP MASTER KEY';
46SET @sql = '';
47SET @sql = 'IF EXISTS ( SELECT * FROM sys.symmetric_keys WHERE [name] LIKE ''%DatabaseMasterKey%'') DROP MASTER KEY;'
48IF (@sql <> '') PRINT @sql;
49PRINT 'GO';
50GO
51:OUT $(TEMP)\deleteit1.txt
52GO
53:CONNECT SQLTWO
54GO
55:OUT STDOUT
56GO
57PRINT ':CONNECT '+@@servername;
58PRINT 'PRINT ''----------------------'';';
59PRINT 'PRINT ''-- Script to clean Certificates, MasterKey, EndPoints'';';
60PRINT 'PRINT ''-- Created : '+ CAST(GetDate() AS NVARCHAR(100))+''';';
61PRINT 'PRINT ''----------------------'';';
62PRINT '-- Server name : ' + @@servername
63
64DECLARE @template nvarchar(MAX);
65DECLARE @sql nvarchar(MAX);
66
67PRINT '-- DROP ENDPOINT'
68SET @sql = '';
69SET @template = 'IF EXISTS ( SELECT * FROM sys.tcp_endpoints WHERE name = ''@endpointName'') DROP ENDPOINT [@endpointName];';
70SELECT @sql = replace(@template,'@endpointName',[name]) FROM sys.tcp_endpoints WHERE [type] = 4;
71IF (@sql <> '') PRINT @sql;
72PRINT 'GO';
73
74SET @sql = '';
75IF (EXISTS(SELECT database_name = d.name , dek.encryptor_type , cert_name = c.name FROM sys.dm_database_encryption_keys dek LEFT JOIN sys.certificates c ON dek.encryptor_thumbprint = c.thumbprint INNER JOIN sys.databases d ON dek.database_id = d.database_id WHERE d.database_id > 4)) BEGIN
76SELECT @sql = @sql + 'Database : [' + d.name + '] uses certificate ['+c.name+']'+char(13) FROM sys.dm_database_encryption_keys dek LEFT JOIN sys.certificates c ON dek.encryptor_thumbprint = c.thumbprint INNER JOIN sys.databases d ON dek.database_id = d.database_id WHERE d.database_id > 4
77PRINT 'PRINT ''-- ATTENTION : The master key and some certificates cannot be dropped if it is used by a database'';'
78PRINT '/* ';
79PRINT @sql + '*/';
80END;
81PRINT '-- DROP CERTIFICATE';
82SET @sql = '';
83SET @template = 'IF EXISTS ( SELECT * FROM sys.certificates WHERE name = ''@certificateName'' ) DROP CERTIFICATE [@certificateName];';
84SELECT @sql = @sql + replace(@template,'@certificateName',[name]) FROM sys.certificates WHERE pvt_key_encryption_type = 'MK';
85IF (@sql <> '') PRINT @sql;
86PRINT 'GO';
87
88PRINT '-- DROP MASTER KEY';
89SET @sql = '';
90SET @sql = 'IF EXISTS ( SELECT * FROM sys.symmetric_keys WHERE [name] LIKE ''%DatabaseMasterKey%'') DROP MASTER KEY;'
91IF (@sql <> '') PRINT @sql;
92PRINT 'GO';
93GO
94:OUT $(TEMP)\deleteit2.txt
95GO

 1
 2GO
 3:connect SQLONE
 4GO
 5PRINT '-- Server name: '+@@servername
 6PRINT '---- Create Endpoint'
 7CREATE ENDPOINT [Hadr_endpoint]
 8    STATE=STARTED
 9    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
10    FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
11, ENCRYPTION = REQUIRED ALGORITHM AES)
12GO
13PRINT '---- GRANT CONNECT ON ENDPOINT'
14GRANT CONNECT ON ENDPOINT::[Hadr_endpoint]        TO [contoso\SQLService];
15GO
16PRINT '---- AUTHORIZATION ON ENDPOINT'
17ALTER AUTHORIZATION ON ENDPOINT::[Hadr_endpoint]  TO [contoso\SQLService];
18GO
19:connect SQLTWO
20GO
21PRINT '-- Server name: '+@@servername
22PRINT '---- Create Endpoint'
23CREATE ENDPOINT [Hadr_endpoint]
24    STATE=STARTED
25    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
26    FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
27, ENCRYPTION = REQUIRED ALGORITHM AES)
28GO
29PRINT '---- GRANT CONNECT ON ENDPOINT'
30GRANT CONNECT ON ENDPOINT::[Hadr_endpoint]        TO [contoso\SQLService];
31GO
32PRINT '---- AUTHORIZATION ON ENDPOINT'
33ALTER AUTHORIZATION ON ENDPOINT::[Hadr_endpoint]  TO [contoso\SQLService];
34GO

 1
 2:CONNECT SQLONE
 3GO
 4PRINT '-- Database "Adventureworks2012" ';
 5IF (EXISTS(SELECT * FROM sys.databases WHERE NOT(recovery_model_desc = 'FULL') AND [name] = 'Adventureworks2012' ))
 6BEGIN
 7    PRINT '---- Fix recovery mode full'
 8    ALTER DATABASE [Adventureworks2012] SET RECOVERY FULL WITH NO_WAIT;;
 9END;
10IF (EXISTS(SELECT * FROM sys.databases WHERE NOT(suser_sname(owner_sid) = 'sa') AND [name] = 'Adventureworks2012' ))
11BEGIN
12    PRINT '---- Fix owner user to "sa"'
13    USE [Adventureworks2012];EXEC dbo.sp_changedbowner @loginame = N'sa',@map=false;
14END;
15
16-- TRUSTWORTHY is set to OFF when a database is backed up. Therefore, TRUSTWORTHY is always OFF on a new mirror database.
17-- If the database needs to be trustworthy after a failover, additional setup steps are necessary.
18-- For more information, see How to: Set Up a Mirror Database to Use the Trustworthy Property.
19IF (EXISTS(SELECT * FROM sys.databases WHERE (is_trustworthy_on = 0) AND [name] = 'Adventureworks2012' ))
20BEGIN
21    PRINT '---- Set TRUSTWORTHY OFF'
22    ALTER DATABASE [Adventureworks2012] SET TRUSTWORTHY OFF;
23END;
24
25GO
26PRINT '---- Backup Database "Adventureworks2012"';
27BACKUP DATABASE [Adventureworks2012] TO  DISK = N'\\SQLONE\s$\Adventureworks2012_backup.BAK'     WITH NOFORMAT, INIT,  NAME = N'Adventureworks2012-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10;
28BACKUP LOG [Adventureworks2012]      TO  DISK = N'\\SQLONE\s$\Adventureworks2012_backup-Log.BAK' WITH NOFORMAT, INIT,  NAME = N'Adventureworks2012-Log Database Backup' , SKIP, NOREWIND, NOUNLOAD,  STATS = 10;
29GO
30
31:CONNECT SQLTWO
32GO
33PRINT '---- Restore Database "[Adventureworks2012]"';
34-- DROP DATABASE [Adventureworks2012];
35RESTORE DATABASE [Adventureworks2012] FROM  DISK = N'\\SQLONE\s$\Adventureworks2012_backup.BAK'     WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5;
36RESTORE LOG [Adventureworks2012]      FROM  DISK = N'\\SQLONE\s$\Adventureworks2012_backup-Log.BAK' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5;
37GO
38
39GO
40:CONNECT SQLONE
41GO
42PRINT '---- DATABASE [Adventureworks2012] SET PARTNER Server: ' + @@servername
43ALTER DATABASE [Adventureworks2012] SET PARTNER = 'TCP://SQLTWO:5022';
44GO
45
46GO
47:CONNECT SQLTWO
48GO
49PRINT '---- DATABASE [Adventureworks2012] SET PARTNER Server: ' + @@servername
50ALTER DATABASE [Adventureworks2012] SET PARTNER = 'TCP://SQLONE:5022';
51GO

  1
  2PRINT 'PRINT ''----------------------'';';
  3PRINT 'PRINT ''-- Script to mirror a database'';';
  4PRINT 'PRINT ''-- Created : '+ CAST(GetDate() AS NVARCHAR(100))+''';';
  5PRINT 'PRINT ''----------------------'';';
  6PRINT 'USE Master;'
  7SET NOCOUNT ON;
  8--------------------------
  9-- Parameters
 10DECLARE @PrimaryServer   nvarchar(1024) = 'SQLONE';
 11DECLARE @SecondaryServer nvarchar(1024) = 'SQLTWO';
 12DECLARE @pathTemp nvarchar(1024) = '\\SQLONE\s$';
 13DECLARE @RestoreDataPath nvarchar(1024) = 'c:\DemoDatabases\Data';
 14DECLARE @RestoreLogPath nvarchar(1024)  = 'c:\DemoDatabases\Log';
 15DECLARE @CreateRestorePath BIT          = 1;
 16DECLARE @MirrorPort nvarchar(5)         = '5022';
 17
 18-- Select databases
 19BEGIN TRY DROP TABLE #db; END TRY BEGIN CATCH END CATCH;
 20SELECT * INTO #db FROM sys.databases f WHERE
 21f.state_desc = 'ONLINE' AND (f.database_id > 4);
 22
 23--------------------------
 24
 25DECLARE @sql nvarchar(MAX);
 26DECLARE @dbname nvarchar(255);
 27
 28DECLARE @cmdCreatepathRestore nvarchar(max) = '';
 29IF (@CreateRestorePath=1)
 30BEGIN
 31SET @cmdCreatepathRestore = '
 32GO
 33:CONNECT @SecondaryServer
 34GO
 35PRINT ''-- Server name : ''+@@servername;
 36EXEC sp_configure ''show advanced options'', 1;
 37RECONFIGURE;
 38EXEC sp_configure ''xp_cmdshell'', 1;
 39RECONFIGURE;
 40GO
 41exec xp_cmdshell N''MD @RestoreDataPath'';
 42exec xp_cmdshell N''MD @RestoreLogPath'';
 43GO
 44'
 45SET @cmdCreatepathRestore = REPLACE(@cmdCreatepathRestore,'@RestoreLogPath', @RestoreLogPath);
 46SET @cmdCreatepathRestore = REPLACE(@cmdCreatepathRestore,'@RestoreDataPath', @RestoreDataPath);
 47SET @cmdCreatepathRestore = REPLACE(@cmdCreatepathRestore,'@PrimaryServer', @PrimaryServer);
 48SET @cmdCreatepathRestore = REPLACE(@cmdCreatepathRestore,'@SecondaryServer', @SecondaryServer);
 49END
 50PRINT @cmdCreatepathRestore;
 51
 52DECLARE @sqltemplateSet nvarchar(MAX) = '
 53
 54PRINT ''------------------------------------------------'';
 55PRINT '''';
 56PRINT ''-- Database "@dbname" '';
 57PRINT '''';
 58PRINT ''------------------------------------------------'';
 59GO
 60:CONNECT @PrimaryServer
 61GO
 62PRINT ''-- Server name : ''+@@servername;
 63IF (EXISTS(SELECT * FROM sys.databases WHERE NOT(recovery_model_desc = ''FULL'') AND [name] = ''@dbname'' ))
 64BEGIN
 65    PRINT ''---- Fix recovery mode full''
 66    ALTER DATABASE [@dbname] SET RECOVERY FULL WITH NO_WAIT;;
 67END;
 68IF (EXISTS(SELECT * FROM sys.databases WHERE NOT(suser_sname(owner_sid) = ''sa'') AND [name] = ''@dbname'' ))
 69BEGIN
 70    PRINT ''---- Fix owner user to "sa"''
 71    USE [@dbname];EXEC dbo.sp_changedbowner @loginame = N''sa'',@map=false;
 72END;
 73
 74IF (EXISTS(SELECT * FROM sys.databases WHERE (is_trustworthy_on = 0) AND [name] = ''@dbname'' ))
 75BEGIN
 76    PRINT ''---- Set TRUSTWORTHY OFF''
 77    PRINT ''---- TRUSTWORTHY is set to OFF when a database is backed up. Therefore, TRUSTWORTHY is always OFF on a new mirror database.''
 78    PRINT ''---- If the database needs to be trustworthy after a failover, additional setup steps are necessary.''
 79    PRINT ''---- For more information, see How to: Set Up a Mirror Database to Use the Trustworthy Property.''
 80    ALTER DATABASE [@dbname] SET TRUSTWORTHY OFF;
 81END;
 82';
 83SET @sqltemplateSet = REPLACE(@sqltemplateSet,'@PrimaryServer', @PrimaryServer);
 84SET @sqltemplateSet = REPLACE(@sqltemplateSet,'@SecondaryServer', @SecondaryServer);
 85
 86DECLARE @sqltemplateBackUp nvarchar(MAX);
 87SET @sqltemplateBackUp = '
 88GO
 89:CONNECT @PrimaryServer
 90GO
 91PRINT ''-- Server name : ''+@@servername;
 92PRINT ''-- Backup Database "@dbname" '';
 93BACKUP DATABASE [@dbname] TO DISK = N''@pathTemp\@dbname_backup.BAK''     WITH NOFORMAT, INIT,  NAME = N''@dbname-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 5;
 94GO
 95BACKUP LOG      [@dbname] TO DISK = N''@pathTemp\@dbname_backup-Log.BAK'' WITH NOFORMAT, INIT,  NAME = N''@dbname-Log Database Backup'' , SKIP, NOREWIND, NOUNLOAD,  STATS = 5;
 96GO
 97';
 98SET @sqltemplateBackUp = REPLACE(@sqltemplateBackUp,'@pathTemp', @pathTemp);
 99SET @sqltemplateBackUp = REPLACE(@sqltemplateBackUp,'@PrimaryServer', @PrimaryServer);
100SET @sqltemplateBackUp = REPLACE(@sqltemplateBackUp,'@SecondaryServer', @SecondaryServer);
101
102DECLARE @sqltemplateRestore nvarchar(MAX);
103SET @sqltemplateRestore = '
104print ''GO'';
105PRINT '':CONNECT @SecondaryServer'';
106print ''GO'';
107PRINT ''PRINT ''''---- Restore Database : [@dbname]'''';''
108declare @sql nvarchar(max) = ''RESTORE DATABASE [@dbname] FROM  DISK = N''''@pathTemp\@dbname_backup.BAK''''     WITH  FILE = 1'';
109select @sql = @sql + '', MOVE N''''''+[name]+ '''''' TO N''''''+ case when [type] = 0 then ''@RestoreDataPath\'' else ''@RestoreLogPath\'' end + case when [name] like ''@dbname_%'' then [name] else ''@dbname_''+[name] end + case when [type] = 0 then ''.mdf'' else ''.ldf'' end + '''''''' from [@dbname].sys.database_files;
110set @sql = @sql + '', NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5;'';
111print @sql;
112print ''GO'';
113PRINT ''RESTORE LOG [@dbname]      FROM  DISK = N''''@pathTemp\@dbname_backup-Log.BAK'''' WITH  FILE = 1, NORECOVERY,  NOUNLOAD,  STATS = 5;''
114print ''GO'';
115--';
116SET @sqltemplateRestore = REPLACE(@sqltemplateRestore,'@RestoreLogPath', @RestoreLogPath);
117SET @sqltemplateRestore = REPLACE(@sqltemplateRestore,'@RestoreDataPath', @RestoreDataPath);
118SET @sqltemplateRestore = REPLACE(@sqltemplateRestore,'@pathTemp', @pathTemp);
119SET @sqltemplateRestore = REPLACE(@sqltemplateRestore,'@PrimaryServer', @PrimaryServer);
120SET @sqltemplateRestore = REPLACE(@sqltemplateRestore,'@SecondaryServer', @SecondaryServer);
121
122DECLARE @sqltemplateSetPartner nvarchar(MAX);
123SET @sqltemplateSetPartner = '
124GO
125-- Secondary must be first
126:CONNECT @SecondaryServer
127GO
128PRINT ''---- DATABASE [@dbname] SET PARTNER Server: '' + @@servername
129ALTER DATABASE [@dbname] SET PARTNER = ''TCP://@PrimaryServer:5022'';
130GO
131
132GO
133:CONNECT @PrimaryServer
134GO
135PRINT ''---- DATABASE [@dbname] SET PARTNER Server: '' + @@servername
136ALTER DATABASE [@dbname] SET PARTNER = ''TCP://@SecondaryServer:5022'';
137GO
138'
139SET @sqltemplateSetPartner = REPLACE(@sqltemplateSetPartner,'@PrimaryServer', @PrimaryServer);
140SET @sqltemplateSetPartner = REPLACE(@sqltemplateSetPartner,'@SecondaryServer', @SecondaryServer);
141SET @sqltemplateSetPartner = REPLACE(@sqltemplateSetPartner,'@MirrorPort', @MirrorPort);
142
143WHILE ((SELECT count(*) FROM #db)>0)
144BEGIN
145
146    SET @sql = '';
147
148    SELECT TOP 1 @dbname = name
149    FROM #db f
150    ORDER BY f.name;
151    DELETE FROM #db
152    WHERE
153        name = @dbname;
154
155    PRINT 'GO';
156
157    SET @sql = REPLACE(@sqltemplateSet, '@dbname', @dbname);
158    PRINT @sql;
159
160    SET @sql = REPLACE(@sqltemplateBackUp, '@dbname', @dbname);
161    PRINT @sql;
162
163    SET @sql = REPLACE(@sqltemplateRestore, '@dbname', @dbname);
164    EXEC (@sql);
165
166    SET @sql = REPLACE(@sqltemplateSetPartner, '@dbname', @dbname);
167    PRINT (@sql);
168
169    PRINT 'PRINT ''------------------------------------------------'';';
170    PRINT 'GO';
171    PRINT 'GO';
172END

 1
 2PRINT 'PRINT ''----------------------'';';
 3PRINT 'PRINT ''-- Script to set Trustworthy ON'';';
 4PRINT 'PRINT ''-- Created : '+ CAST(GetDate() AS NVARCHAR(100))+''';';
 5PRINT 'PRINT ''----------------------'';';
 6PRINT 'USE Master;'
 7SET NOCOUNT ON;
 8DECLARE @sql nvarchar(MAX);
 9DECLARE @dbname nvarchar(255);
10BEGIN TRY DROP TABLE #db; END TRY BEGIN CATCH END CATCH;
11SELECT * INTO #db FROM sys.databases f WHERE f.state_desc = 'ONLINE'
12AND (f.database_id > 4)
13
14DECLARE @sqltemplate nvarchar(MAX);
15SET @sqltemplate = '
16PRINT ''-- Database "?" '';
17IF (EXISTS(SELECT * FROM sys.databases WHERE (is_trustworthy_on = 0) AND [name] = ''?'' ))
18BEGIN
19    PRINT ''---- Set TRUSTWORTHY ON''
20    ALTER DATABASE [?] SET TRUSTWORTHY ON  ;
21END;
22'
23WHILE ((SELECT count(*) FROM #db)>0) BEGIN
24SET @sql = '';
25SELECT TOP 1 @dbname = name , @sql = REPLACE(@sqltemplate, '?', name) FROM #db f ORDER BY f.name;
26DELETE FROM #db WHERE name = @dbname;
27PRINT @sql;
28END

 1
 2PRINT 'PRINT ''----------------------'';';
 3PRINT 'PRINT ''-- Script to Logins'';';
 4PRINT 'PRINT ''-- Created : '+ CAST(GetDate() AS NVARCHAR(100))+''';';
 5PRINT 'PRINT ''----------------------'';';
 6PRINT 'USE Master;'
 7SET NOCOUNT ON;
 8
 9DECLARE @StriptSql TABLE ( [NumberRow] [int] IDENTITY(1,1) NOT NULL , SqlCmd nvarchar(MAX) );
10
11INSERT INTO @StriptSql ( SqlCmd ) VALUES ( '-- Logins' );
12INSERT INTO @StriptSql ( SqlCmd ) SELECT 'IF (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(SP.name)+
13CASE WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ' +CONVERT(NVARCHAR(MAX),SL.password_hash,1)+ ' HASHED, CHECK_EXPIRATION = '
14+ CASE WHEN SL.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END +', CHECK_POLICY = ' +CASE WHEN SL.is_policy_checked = 1 THEN 'ON,' ELSE 'OFF,' END ELSE ' FROM WINDOWS WITH' END
15+' DEFAULT_DATABASE=[' +SP.default_database_name+ '], DEFAULT_LANGUAGE=[' +SP.default_language_name+ '] END;' COLLATE SQL_Latin1_General_CP1_CI_AS AS [-- Logins To Be Created --] FROM sys.server_principals AS SP LEFT JOIN sys.sql_logins AS SL ON SP.principal_id = SL.principal_id WHERE SP.type IN ('S','G','U')
16AND SP.name NOT LIKE '##%##'
17AND SP.name NOT LIKE 'NT AUTHORITY%'
18AND SP.name NOT LIKE 'NT SERVICE%'
19AND SP.name <> ('sa') ORDER BY SP.type_desc , SP.name;
20
21INSERT INTO @StriptSql ( SqlCmd ) VALUES ( '-- Server Roles' );
22INSERT INTO @StriptSql ( SqlCmd ) SELECT 'EXEC master..sp_addsrvrolemember @loginame = N''' + SL.name + ''', @rolename = N''' + SR.name + ''';' FROM master.sys.server_role_members SRM JOIN master.sys.server_principals SR ON SR.principal_id = SRM.role_principal_id JOIN master.sys.server_principals SL ON SL.principal_id = SRM.member_principal_id WHERE SL.type IN ('S','G','U')
23AND SL.name NOT LIKE '##%##'
24AND SL.name NOT LIKE 'NT AUTHORITY%'
25AND SL.name NOT LIKE 'NT SERVICE%'
26AND SL.name <> ('sa');
27
28INSERT INTO @StriptSql ( SqlCmd ) VALUES ( '-- Permissions to Be Granted' );
29INSERT INTO @StriptSql ( SqlCmd ) SELECT CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' THEN SrvPerm.state_desc ELSE 'GRANT' END
30+ ' ' + SrvPerm.permission_name + ' TO [' + SP.name + ']' +
31CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' THEN '' ELSE ' WITH GRANT OPTION' END + ';' COLLATE database_default AS [-- Server Level Permissions to Be Granted --] FROM sys.server_permissions AS SrvPerm JOIN sys.server_principals AS SP ON SrvPerm.grantee_principal_id = SP.principal_id WHERE SP.type IN ( 'S', 'U', 'G' )
32AND SP.name NOT LIKE '##%##'
33AND SP.name NOT LIKE 'NT AUTHORITY%'
34AND SP.name NOT LIKE 'NT SERVICE%'
35AND SP.name <> ('sa');
36
37DECLARE @sql nvarchar(max);
38DECLARE @NumberRow INT;
39WHILE (exists (SELECT * FROM @StriptSql))
40BEGIN
41    SELECT TOP 1 @NumberRow=[NumberRow], @sql = SqlCmd FROM @StriptSql order by [NumberRow];
42    DELETE FROM @StriptSql WHERE [NumberRow]=@NumberRow;
43    PRINT @sql;
44END

 1
 2PRINT 'PRINT ''----------------------'';';
 3PRINT 'PRINT ''-- Script to manual failover'';';
 4PRINT 'PRINT ''-- Created : '+ CAST(GetDate() AS NVARCHAR(100))+''';';
 5PRINT 'PRINT ''----------------------'';';
 6PRINT 'USE Master;'
 7SET NOCOUNT ON;
 8DECLARE @sql nvarchar(MAX);
 9DECLARE @dbname nvarchar(255);
10BEGIN TRY DROP TABLE #db; END TRY BEGIN CATCH END CATCH;
11SELECT * INTO #db FROM sys.databases
12WHERE (database_id > 4)
13
14DECLARE @sqltemplate nvarchar(MAX);
15SET @sqltemplate = '
16GO
17USE master;
18DECLARE @mirroring_state nvarchar(10);
19DECLARE @mirroring_state_desc nvarchar(100);
20DECLARE @mirroring_role nvarchar(10);
21DECLARE @mirroring_role_desc nvarchar(100);
22SELECT @mirroring_state= m.mirroring_state , @mirroring_state_desc=m.mirroring_state_desc , @mirroring_role_desc= m.mirroring_role_desc , @mirroring_role=m.mirroring_role FROM sys.database_mirroring AS m INNER JOIN sys.databases AS f ON m.database_id = f.database_id WHERE (f.name = ''@dbname'')
23IF (@mirroring_state IS NULL) BEGIN PRINT ''-- FAIL @dbname : Database No mirroring'';END
24ELSE IF (NOT(@mirroring_state = 4)) BEGIN PRINT ''-- FAIL @dbname : NOT SYNCHRONIZED'';END
25ELSE IF (@mirroring_role <> 1) BEGIN PRINT ''-- FAIL @dbname : NOT PRINCIPAL''; END
26ELSE BEGIN PRINT ''-- OK   @dbname : FAILOVER''; ALTER DATABASE [@dbname] SET PARTNER FAILOVER; END;
27GO
28'
29WHILE ((SELECT count(*) FROM #db)>0) BEGIN
30SET @sql = '';
31SELECT TOP 1 @dbname = name , @sql = REPLACE(@sqltemplate, '@dbname', name) FROM #db f ORDER BY f.name;
32DELETE FROM #db WHERE name = @dbname;
33PRINT @sql;
34END