Setting Up Database Mirroring

Create mirror between two server without witness (High safety without automatic failover - synchronous).

This exemple use TechNet Virtual Labs :
Exploring AlwaysOn Availability Groups in SQL Server 2014 

This script will not change anything in your database, it just will create the scripts for.

All script was create for TechLabs :

Primary Server SQL Server   : SQLONE
Secondary Server SQL Server : SQLTWO
Mirroring User : contoso\SQLService
Temp Path      : \\SQLONE\s$
Mirroing Port  : 5022
ENDPOINT name  : Hadr_endpoint

You need change it for yours servers.

 
Step zero:
We use TechLabs to do this tutorial then we delete all configurations before we start.

** Attention when you run on your SQL Server.**
1. Remove Database Mirroring;
2. Remove Certificates, Master Key, EndPoints. (if it's possible, and maybe, additional steps are necessary).

Step 1: Prepare mirror
1. Add Login user
You need to create a user on AD, and add this user on both SQL Servers

2. Create End Points
You need to use the name of login created the End Points, and you cannot use your login.

Step 2: Mirroring a database
1. Set full recovery model on database.

2. Change owner user to "sa".
It's not necessary, but if you do not change it, you must be sure that you have the same user on secondary server.

3. Set Trustworthy OFF
Trustworthy is always OFF on a new mirror database.

4. Backup database

5. Backup login

6. Restore database

7. Restore log

8. Set partners

* High safety without automatic failover:
All committed transactions are guaranteed to be written to disk on the mirror server.
Manual failover is possible when the partners are connected to each other and the database is synchronized.

The loss of a partner has the following effect:

 * If the mirror server instance becomes unavailable, the principal continues.
 * If the principal server instance becomes unavailable, the mirror stops but is accessible as a warm standby; the database owner can force service to the mirror server instance (with possible data loss).

* We will use xp_cmdshell.

Step zero:

1. Remove Database Mirroring :

Execute on primary server

--------------------------------------
-- Meta script : Script to STOP mirroring
-- Primary   server: SQLONE
-- Secondary server: SQLTWO
--------------------------------------
GO
:OUT $(TEMP)\deleteit1.txt
:CONNECT SQLONE
GO
:OUT STDOUT
GO
PRINT 'GO'
PRINT ':CONNECT ' + @@servername;
PRINT 'GO'
PRINT 'PRINT ''----------------------'';';
PRINT 'PRINT ''-- Script to STOP mirroring'';';
PRINT 'PRINT ''-- Created : '+ CAST(GetDate() AS NVARCHAR(100))+''';';
PRINT 'PRINT ''----------------------'';';
PRINT '-- Server name: '+@@servername
PRINT 'USE Master;'
SET NOCOUNT ON;
DECLARE @sql nvarchar(MAX);
DECLARE @dbname nvarchar(255);
BEGIN TRY DROP TABLE #db; END TRY BEGIN CATCH END CATCH;
SELECT 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);


DECLARE @sqltemplate nvarchar(MAX);
SET @sqltemplate = '
IF (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 = ''@dbname'' AND mirroring_guid IS NOT NULL))
BEGIN
    PRINT ''-- Remove Mirroring Database "@dbname" ''
    ALTER DATABASE [@dbname] SET PARTNER OFF;
END;
'
WHILE ((SELECT count(*) FROM #db)>0) BEGIN
SET @sql = '';
SELECT TOP 1 @dbname = name , @sql = REPLACE(@sqltemplate, '@dbname', name) FROM #db f ORDER BY f.name;
DELETE FROM #db WHERE name = @dbname;
PRINT @sql;
END
GO
PRINT 'GO'
GO
:OUT $(TEMP)\deleteit1.txt
GO
:CONNECT SQLTWO
GO
:OUT STDOUT
GO
PRINT 'GO'
PRINT ':CONNECT ' + @@servername;
PRINT 'GO'
PRINT 'PRINT ''----------------------'';';
PRINT 'PRINT ''-- Script to STOP mirroring'';';
PRINT 'PRINT ''-- Created : '+ CAST(GetDate() AS NVARCHAR(100))+''';';
PRINT 'PRINT ''----------------------'';';
PRINT '-- Server name: '+@@servername
PRINT 'USE Master;'
SET NOCOUNT ON;
DECLARE @sql nvarchar(MAX);
DECLARE @dbname nvarchar(255);
BEGIN TRY DROP TABLE #db; END TRY BEGIN CATCH END CATCH;
SELECT 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);

DECLARE @sqltemplate nvarchar(MAX);
SET @sqltemplate = '
IF (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 = ''@dbname'' AND mirroring_guid IS NOT NULL))
BEGIN
    PRINT ''-- Remove Mirroring Database "@dbname" ''
    ALTER DATABASE [@dbname] SET PARTNER OFF;
END;
'
WHILE ((SELECT count(*) FROM #db)>0) BEGIN
SET @sql = '';
SELECT TOP 1 @dbname = name , @sql = REPLACE(@sqltemplate, '@dbname', name) FROM #db f ORDER BY f.name;
DELETE FROM #db WHERE name = @dbname;
PRINT @sql;
END
GO
PRINT 'GO'
GO
:OUT $(TEMP)\deleteit2.txt
GO

2. Remove Certificates, Master Key, EndPoints

Execute on both servers : primary & secundary

PRINT 'PRINT ''----------------------'';';
PRINT 'PRINT ''-- Script to clean Certificates, MasterKey, EndPoints'';';
PRINT 'PRINT ''-- Created : '+ CAST(GetDate() AS NVARCHAR(100))+''';';
PRINT 'PRINT ''----------------------'';';
PRINT '-- Server name : ' + @@servername

DECLARE @template nvarchar(MAX);
DECLARE @sql nvarchar(MAX);

PRINT '-- DROP ENDPOINT'
SET @sql = '';
SET @template = 'IF EXISTS ( SELECT * FROM sys.tcp_endpoints WHERE name = ''@endpointName'') DROP ENDPOINT [@endpointName];';
SELECT @sql = replace(@template,'@endpointName',[name]) FROM sys.tcp_endpoints WHERE [type] = 4;
IF (@sql <> '') PRINT @sql;
PRINT 'GO';

SET @sql = '';
IF (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
SELECT @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
PRINT 'PRINT ''-- ATTENTION : The master key and some certificates cannot be dropped if it is used by a database'';'
PRINT '/* ';
PRINT @sql + '*/';
END;
PRINT '-- DROP CERTIFICATE';
SET @sql = '';
SET @template = 'IF EXISTS ( SELECT * FROM sys.certificates WHERE name = ''@certificateName'' ) DROP CERTIFICATE [@certificateName];';
SELECT @sql = @sql + replace(@template,'@certificateName',[name]) FROM sys.certificates WHERE pvt_key_encryption_type = 'MK';
IF (@sql <> '') PRINT @sql;
PRINT 'GO';

PRINT '-- DROP MASTER KEY';
SET @sql = '';
SET @sql = 'IF EXISTS ( SELECT * FROM sys.symmetric_keys WHERE [name] LIKE ''%DatabaseMasterKey%'') DROP MASTER KEY;'
IF (@sql <> '') PRINT @sql;
PRINT 'GO';

or just run this :

--------------------------------------
-- Meta script : Script to clean Certificates, MasterKey, EndPoints
-- Primary   server: SQLONE
-- Secondary server: SQLTWO
--------------------------------------
GO
:OUT $(TEMP)\deleteit.txt
GO
:CONNECT SQLONE
GO
:OUT STDOUT
GO
PRINT ':CONNECT '+@@servername;
PRINT 'PRINT ''----------------------'';';
PRINT 'PRINT ''-- Script to clean Certificates, MasterKey, EndPoints'';';
PRINT 'PRINT ''-- Created : '+ CAST(GetDate() AS NVARCHAR(100))+''';';
PRINT 'PRINT ''----------------------'';';
PRINT '-- Server name : ' + @@servername

DECLARE @template nvarchar(MAX);
DECLARE @sql nvarchar(MAX);

PRINT '-- DROP ENDPOINT'
SET @sql = '';
SET @template = 'IF EXISTS ( SELECT * FROM sys.tcp_endpoints WHERE name = ''@endpointName'') DROP ENDPOINT [@endpointName];';
SELECT @sql = replace(@template,'@endpointName',[name]) FROM sys.tcp_endpoints WHERE [type] = 4;
IF (@sql <> '') PRINT @sql;
PRINT 'GO';

SET @sql = '';
IF (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
SELECT @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
PRINT 'PRINT ''-- ATTENTION : The master key and some certificates cannot be dropped if it is used by a database'';'
PRINT '/* ';
PRINT @sql + '*/';
END;
PRINT '-- DROP CERTIFICATE';
SET @sql = '';
SET @template = 'IF EXISTS ( SELECT * FROM sys.certificates WHERE name = ''@certificateName'' ) DROP CERTIFICATE [@certificateName];';
SELECT @sql = @sql + replace(@template,'@certificateName',[name]) FROM sys.certificates WHERE pvt_key_encryption_type = 'MK';
IF (@sql <> '') PRINT @sql;
PRINT 'GO';

PRINT '-- DROP MASTER KEY';
SET @sql = '';
SET @sql = 'IF EXISTS ( SELECT * FROM sys.symmetric_keys WHERE [name] LIKE ''%DatabaseMasterKey%'') DROP MASTER KEY;'
IF (@sql <> '') PRINT @sql;
PRINT 'GO';
GO
:OUT $(TEMP)\deleteit1.txt
GO
:CONNECT SQLTWO
GO
:OUT STDOUT
GO
PRINT ':CONNECT '+@@servername;
PRINT 'PRINT ''----------------------'';';
PRINT 'PRINT ''-- Script to clean Certificates, MasterKey, EndPoints'';';
PRINT 'PRINT ''-- Created : '+ CAST(GetDate() AS NVARCHAR(100))+''';';
PRINT 'PRINT ''----------------------'';';
PRINT '-- Server name : ' + @@servername

DECLARE @template nvarchar(MAX);
DECLARE @sql nvarchar(MAX);

PRINT '-- DROP ENDPOINT'
SET @sql = '';
SET @template = 'IF EXISTS ( SELECT * FROM sys.tcp_endpoints WHERE name = ''@endpointName'') DROP ENDPOINT [@endpointName];';
SELECT @sql = replace(@template,'@endpointName',[name]) FROM sys.tcp_endpoints WHERE [type] = 4;
IF (@sql <> '') PRINT @sql;
PRINT 'GO';

SET @sql = '';
IF (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
SELECT @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
PRINT 'PRINT ''-- ATTENTION : The master key and some certificates cannot be dropped if it is used by a database'';'
PRINT '/* ';
PRINT @sql + '*/';
END;
PRINT '-- DROP CERTIFICATE';
SET @sql = '';
SET @template = 'IF EXISTS ( SELECT * FROM sys.certificates WHERE name = ''@certificateName'' ) DROP CERTIFICATE [@certificateName];';
SELECT @sql = @sql + replace(@template,'@certificateName',[name]) FROM sys.certificates WHERE pvt_key_encryption_type = 'MK';
IF (@sql <> '') PRINT @sql;
PRINT 'GO';

PRINT '-- DROP MASTER KEY';
SET @sql = '';
SET @sql = 'IF EXISTS ( SELECT * FROM sys.symmetric_keys WHERE [name] LIKE ''%DatabaseMasterKey%'') DROP MASTER KEY;'
IF (@sql <> '') PRINT @sql;
PRINT 'GO';
GO
:OUT $(TEMP)\deleteit2.txt
GO

Step 1: Prepare mirror

1. Create End Points on both servers: [Hadr_endpoint]

GO
:connect SQLONE
GO
PRINT '-- Server name: '+@@servername
PRINT '---- Create Endpoint'
CREATE ENDPOINT [Hadr_endpoint]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
PRINT '---- GRANT CONNECT ON ENDPOINT'
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint]        TO [contoso\SQLService];
GO
PRINT '---- AUTHORIZATION ON ENDPOINT'
ALTER AUTHORIZATION ON ENDPOINT::[Hadr_endpoint]  TO [contoso\SQLService];
GO
:connect SQLTWO
GO
PRINT '-- Server name: '+@@servername
PRINT '---- Create Endpoint'
CREATE ENDPOINT [Hadr_endpoint]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO
PRINT '---- GRANT CONNECT ON ENDPOINT'
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint]        TO [contoso\SQLService];
GO
PRINT '---- AUTHORIZATION ON ENDPOINT'
ALTER AUTHORIZATION ON ENDPOINT::[Hadr_endpoint]  TO [contoso\SQLService];
GO

Set mirror to database : Adventureworks2012

:CONNECT SQLONE
GO
PRINT '-- Database "Adventureworks2012" ';
IF (EXISTS(SELECT * FROM sys.databases WHERE NOT(recovery_model_desc = 'FULL') AND [name] = 'Adventureworks2012' ))
BEGIN
    PRINT '---- Fix recovery mode full'
    ALTER DATABASE [Adventureworks2012] SET RECOVERY FULL WITH NO_WAIT;;
END;
IF (EXISTS(SELECT * FROM sys.databases WHERE NOT(suser_sname(owner_sid) = 'sa') AND [name] = 'Adventureworks2012' ))
BEGIN
    PRINT '---- Fix owner user to "sa"'
    USE [Adventureworks2012];EXEC dbo.sp_changedbowner @loginame = N'sa',@map=false;
END;

-- TRUSTWORTHY is set to OFF when a database is backed up. Therefore, TRUSTWORTHY is always OFF on a new mirror database.
-- If the database needs to be trustworthy after a failover, additional setup steps are necessary.
-- For more information, see How to: Set Up a Mirror Database to Use the Trustworthy Property.
IF (EXISTS(SELECT * FROM sys.databases WHERE (is_trustworthy_on = 0) AND [name] = 'Adventureworks2012' ))
BEGIN
    PRINT '---- Set TRUSTWORTHY OFF'
    ALTER DATABASE [Adventureworks2012] SET TRUSTWORTHY OFF;
END;

GO
PRINT '---- Backup Database "Adventureworks2012"';
BACKUP DATABASE [Adventureworks2012] TO  DISK = N'\\SQLONE\s$\Adventureworks2012_backup.BAK'     WITH NOFORMAT, INIT,  NAME = N'Adventureworks2012-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10;
BACKUP 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;
GO

:CONNECT SQLTWO
GO
PRINT '---- Restore Database "[Adventureworks2012]"';
-- DROP DATABASE [Adventureworks2012];
RESTORE DATABASE [Adventureworks2012] FROM  DISK = N'\\SQLONE\s$\Adventureworks2012_backup.BAK'     WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5;
RESTORE LOG [Adventureworks2012]      FROM  DISK = N'\\SQLONE\s$\Adventureworks2012_backup-Log.BAK' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  STATS = 5;
GO

GO
:CONNECT SQLONE
GO
PRINT '---- DATABASE [Adventureworks2012] SET PARTNER Server: ' + @@servername
ALTER DATABASE [Adventureworks2012] SET PARTNER = 'TCP://SQLTWO:5022';
GO

GO
:CONNECT SQLTWO
GO
PRINT '---- DATABASE [Adventureworks2012] SET PARTNER Server: ' + @@servername
ALTER DATABASE [Adventureworks2012] SET PARTNER = 'TCP://SQLONE:5022';
GO

Mirroring all database

PRINT 'PRINT ''----------------------'';';
PRINT 'PRINT ''-- Script to mirror a database'';';
PRINT 'PRINT ''-- Created : '+ CAST(GetDate() AS NVARCHAR(100))+''';';
PRINT 'PRINT ''----------------------'';';
PRINT 'USE Master;'
SET NOCOUNT ON;
--------------------------
-- Parameters
DECLARE @PrimaryServer   nvarchar(1024) = 'SQLONE';
DECLARE @SecondaryServer nvarchar(1024) = 'SQLTWO';
DECLARE @pathTemp nvarchar(1024) = '\\SQLONE\s$';
DECLARE @RestoreDataPath nvarchar(1024) = 'c:\DemoDatabases\Data';
DECLARE @RestoreLogPath nvarchar(1024)  = 'c:\DemoDatabases\Log';
DECLARE @CreateRestorePath BIT          = 1;
DECLARE @MirrorPort nvarchar(5)         = '5022';

-- Select databases
BEGIN TRY DROP TABLE #db; END TRY BEGIN CATCH END CATCH;
SELECT * INTO #db FROM sys.databases f WHERE
f.state_desc = 'ONLINE' AND (f.database_id > 4);

--------------------------

DECLARE @sql nvarchar(MAX);
DECLARE @dbname nvarchar(255);


DECLARE @cmdCreatepathRestore nvarchar(max) = '';
IF (@CreateRestorePath=1)
BEGIN
SET @cmdCreatepathRestore = '
GO
:CONNECT @SecondaryServer
GO
PRINT ''-- Server name : ''+@@servername;
EXEC sp_configure ''show advanced options'', 1;
RECONFIGURE;
EXEC sp_configure ''xp_cmdshell'', 1;
RECONFIGURE;
GO
exec xp_cmdshell N''MD @RestoreDataPath'';
exec xp_cmdshell N''MD @RestoreLogPath'';
GO
'
SET @cmdCreatepathRestore = REPLACE(@cmdCreatepathRestore,'@RestoreLogPath', @RestoreLogPath);
SET @cmdCreatepathRestore = REPLACE(@cmdCreatepathRestore,'@RestoreDataPath', @RestoreDataPath);
SET @cmdCreatepathRestore = REPLACE(@cmdCreatepathRestore,'@PrimaryServer', @PrimaryServer);
SET @cmdCreatepathRestore = REPLACE(@cmdCreatepathRestore,'@SecondaryServer', @SecondaryServer);
END
PRINT @cmdCreatepathRestore;


DECLARE @sqltemplateSet nvarchar(MAX) = '

PRINT ''------------------------------------------------'';
PRINT '''';
PRINT ''-- Database "@dbname" '';
PRINT '''';
PRINT ''------------------------------------------------'';
GO
:CONNECT @PrimaryServer
GO
PRINT ''-- Server name : ''+@@servername;
IF (EXISTS(SELECT * FROM sys.databases WHERE NOT(recovery_model_desc = ''FULL'') AND [name] = ''@dbname'' ))
BEGIN
    PRINT ''---- Fix recovery mode full''
    ALTER DATABASE [@dbname] SET RECOVERY FULL WITH NO_WAIT;;
END;
IF (EXISTS(SELECT * FROM sys.databases WHERE NOT(suser_sname(owner_sid) = ''sa'') AND [name] = ''@dbname'' ))
BEGIN
    PRINT ''---- Fix owner user to "sa"''
    USE [@dbname];EXEC dbo.sp_changedbowner @loginame = N''sa'',@map=false;
END;

IF (EXISTS(SELECT * FROM sys.databases WHERE (is_trustworthy_on = 0) AND [name] = ''@dbname'' ))
BEGIN
    PRINT ''---- Set TRUSTWORTHY OFF''
    PRINT ''---- TRUSTWORTHY is set to OFF when a database is backed up. Therefore, TRUSTWORTHY is always OFF on a new mirror database.''  
    PRINT ''---- If the database needs to be trustworthy after a failover, additional setup steps are necessary.''  
    PRINT ''---- For more information, see How to: Set Up a Mirror Database to Use the Trustworthy Property.''
    ALTER DATABASE [@dbname] SET TRUSTWORTHY OFF;
END;
';
SET @sqltemplateSet = REPLACE(@sqltemplateSet,'@PrimaryServer', @PrimaryServer);
SET @sqltemplateSet = REPLACE(@sqltemplateSet,'@SecondaryServer', @SecondaryServer);

DECLARE @sqltemplateBackUp nvarchar(MAX);
SET @sqltemplateBackUp = '
GO
:CONNECT @PrimaryServer
GO
PRINT ''-- Server name : ''+@@servername;
PRINT ''-- Backup Database "@dbname" '';
BACKUP DATABASE [@dbname] TO DISK = N''@pathTemp\@dbname_backup.BAK''     WITH NOFORMAT, INIT,  NAME = N''@dbname-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 5;
GO
BACKUP LOG      [@dbname] TO DISK = N''@pathTemp\@dbname_backup-Log.BAK'' WITH NOFORMAT, INIT,  NAME = N''@dbname-Log Database Backup'' , SKIP, NOREWIND, NOUNLOAD,  STATS = 5;
GO
';
SET @sqltemplateBackUp = REPLACE(@sqltemplateBackUp,'@pathTemp', @pathTemp);
SET @sqltemplateBackUp = REPLACE(@sqltemplateBackUp,'@PrimaryServer', @PrimaryServer);
SET @sqltemplateBackUp = REPLACE(@sqltemplateBackUp,'@SecondaryServer', @SecondaryServer);


DECLARE @sqltemplateRestore nvarchar(MAX);
SET @sqltemplateRestore = '
print ''GO'';
PRINT '':CONNECT @SecondaryServer'';
print ''GO'';
PRINT ''PRINT ''''---- Restore Database : [@dbname]'''';''
declare @sql nvarchar(max) = ''RESTORE DATABASE [@dbname] FROM  DISK = N''''@pathTemp\@dbname_backup.BAK''''     WITH  FILE = 1'';
select @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;
set @sql = @sql + '', NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5;'';
print @sql;
print ''GO'';
PRINT ''RESTORE LOG [@dbname]      FROM  DISK = N''''@pathTemp\@dbname_backup-Log.BAK'''' WITH  FILE = 1, NORECOVERY,  NOUNLOAD,  STATS = 5;''
print ''GO'';
--';
SET @sqltemplateRestore = REPLACE(@sqltemplateRestore,'@RestoreLogPath', @RestoreLogPath);
SET @sqltemplateRestore = REPLACE(@sqltemplateRestore,'@RestoreDataPath', @RestoreDataPath);
SET @sqltemplateRestore = REPLACE(@sqltemplateRestore,'@pathTemp', @pathTemp);
SET @sqltemplateRestore = REPLACE(@sqltemplateRestore,'@PrimaryServer', @PrimaryServer);
SET @sqltemplateRestore = REPLACE(@sqltemplateRestore,'@SecondaryServer', @SecondaryServer);


DECLARE @sqltemplateSetPartner nvarchar(MAX);
SET @sqltemplateSetPartner = '
GO
-- Secondary must be first
:CONNECT @SecondaryServer
GO
PRINT ''---- DATABASE [@dbname] SET PARTNER Server: '' + @@servername
ALTER DATABASE [@dbname] SET PARTNER = ''TCP://@PrimaryServer:5022'';
GO

GO
:CONNECT @PrimaryServer
GO
PRINT ''---- DATABASE [@dbname] SET PARTNER Server: '' + @@servername
ALTER DATABASE [@dbname] SET PARTNER = ''TCP://@SecondaryServer:5022'';
GO
'
SET @sqltemplateSetPartner = REPLACE(@sqltemplateSetPartner,'@PrimaryServer', @PrimaryServer);
SET @sqltemplateSetPartner = REPLACE(@sqltemplateSetPartner,'@SecondaryServer', @SecondaryServer);
SET @sqltemplateSetPartner = REPLACE(@sqltemplateSetPartner,'@MirrorPort', @MirrorPort);


WHILE ((SELECT count(*) FROM #db)>0)
BEGIN

    SET @sql = '';

    SELECT TOP 1 @dbname = name
    FROM #db f
    ORDER BY f.name;
    DELETE FROM #db
    WHERE
        name = @dbname;

    PRINT 'GO';
    
    SET @sql = REPLACE(@sqltemplateSet, '@dbname', @dbname);
    PRINT @sql;

    SET @sql = REPLACE(@sqltemplateBackUp, '@dbname', @dbname);
    PRINT @sql;

    SET @sql = REPLACE(@sqltemplateRestore, '@dbname', @dbname);
    EXEC (@sql);
                                      
    SET @sql = REPLACE(@sqltemplateSetPartner, '@dbname', @dbname);
    PRINT (@sql);
    
    PRINT 'PRINT ''------------------------------------------------'';';
    PRINT 'GO';
    PRINT 'GO';
END

Set Trustworthy ON

PRINT 'PRINT ''----------------------'';';
PRINT 'PRINT ''-- Script to set Trustworthy ON'';';
PRINT 'PRINT ''-- Created : '+ CAST(GetDate() AS NVARCHAR(100))+''';';
PRINT 'PRINT ''----------------------'';';
PRINT 'USE Master;'
SET NOCOUNT ON;
DECLARE @sql nvarchar(MAX);
DECLARE @dbname nvarchar(255);
BEGIN TRY DROP TABLE #db; END TRY BEGIN CATCH END CATCH;
SELECT * INTO #db FROM sys.databases f WHERE f.state_desc = 'ONLINE'
AND (f.database_id > 4)

DECLARE @sqltemplate nvarchar(MAX);
SET @sqltemplate = '
PRINT ''-- Database "?" '';
IF (EXISTS(SELECT * FROM sys.databases WHERE (is_trustworthy_on = 0) AND [name] = ''?'' ))
BEGIN
    PRINT ''---- Set TRUSTWORTHY ON''
    ALTER DATABASE [?] SET TRUSTWORTHY ON  ;
END;
'
WHILE ((SELECT count(*) FROM #db)>0) BEGIN
SET @sql = '';
SELECT TOP 1 @dbname = name , @sql = REPLACE(@sqltemplate, '?', name) FROM #db f ORDER BY f.name;
DELETE FROM #db WHERE name = @dbname;
PRINT @sql;
END

Copy logins

PRINT 'PRINT ''----------------------'';';
PRINT 'PRINT ''-- Script to Logins'';';
PRINT 'PRINT ''-- Created : '+ CAST(GetDate() AS NVARCHAR(100))+''';';
PRINT 'PRINT ''----------------------'';';
PRINT 'USE Master;'
SET NOCOUNT ON;

DECLARE @StriptSql TABLE ( [NumberRow] [int] IDENTITY(1,1) NOT NULL , SqlCmd nvarchar(MAX) );

INSERT INTO @StriptSql ( SqlCmd ) VALUES ( '-- Logins' );
INSERT INTO @StriptSql ( SqlCmd ) SELECT 'IF (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(SP.name)+
CASE WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ' +CONVERT(NVARCHAR(MAX),SL.password_hash,1)+ ' HASHED, CHECK_EXPIRATION = '
+ 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
+' 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')
AND SP.name NOT LIKE '##%##'
AND SP.name NOT LIKE 'NT AUTHORITY%'
AND SP.name NOT LIKE 'NT SERVICE%'
AND SP.name <> ('sa') ORDER BY SP.type_desc , SP.name;

INSERT INTO @StriptSql ( SqlCmd ) VALUES ( '-- Server Roles' );
INSERT 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')
AND SL.name NOT LIKE '##%##'
AND SL.name NOT LIKE 'NT AUTHORITY%'
AND SL.name NOT LIKE 'NT SERVICE%'
AND SL.name <> ('sa');

INSERT INTO @StriptSql ( SqlCmd ) VALUES ( '-- Permissions to Be Granted' );
INSERT INTO @StriptSql ( SqlCmd ) SELECT CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' THEN SrvPerm.state_desc ELSE 'GRANT' END
+ ' ' + SrvPerm.permission_name + ' TO [' + SP.name + ']' +
CASE 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' )
AND SP.name NOT LIKE '##%##'
AND SP.name NOT LIKE 'NT AUTHORITY%'
AND SP.name NOT LIKE 'NT SERVICE%'
AND SP.name <> ('sa');

DECLARE @sql nvarchar(max);
DECLARE @NumberRow INT;
WHILE (exists (SELECT * FROM @StriptSql))
BEGIN
    SELECT TOP 1 @NumberRow=[NumberRow], @sql = SqlCmd FROM @StriptSql order by [NumberRow];
    DELETE FROM @StriptSql WHERE [NumberRow]=@NumberRow;
    PRINT @sql;
END

Script to failover all database
You must run on principal server that will be the new secondary server.

PRINT 'PRINT ''----------------------'';';
PRINT 'PRINT ''-- Script to manual failover'';';
PRINT 'PRINT ''-- Created : '+ CAST(GetDate() AS NVARCHAR(100))+''';';
PRINT 'PRINT ''----------------------'';';
PRINT 'USE Master;'
SET NOCOUNT ON;
DECLARE @sql nvarchar(MAX);
DECLARE @dbname nvarchar(255);
BEGIN TRY DROP TABLE #db; END TRY BEGIN CATCH END CATCH;
SELECT * INTO #db FROM sys.databases
WHERE (database_id > 4)

DECLARE @sqltemplate nvarchar(MAX);
SET @sqltemplate = '
GO
USE master;
DECLARE @mirroring_state nvarchar(10);
DECLARE @mirroring_state_desc nvarchar(100);
DECLARE @mirroring_role nvarchar(10);
DECLARE @mirroring_role_desc nvarchar(100);
SELECT @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'')
IF (@mirroring_state IS NULL) BEGIN PRINT ''-- FAIL @dbname : Database No mirroring'';END
ELSE IF (NOT(@mirroring_state = 4)) BEGIN PRINT ''-- FAIL @dbname : NOT SYNCHRONIZED'';END
ELSE IF (@mirroring_role <> 1) BEGIN PRINT ''-- FAIL @dbname : NOT PRINCIPAL''; END
ELSE BEGIN PRINT ''-- OK   @dbname : FAILOVER''; ALTER DATABASE [@dbname] SET PARTNER FAILOVER; END;
GO
'
WHILE ((SELECT count(*) FROM #db)>0) BEGIN
SET @sql = '';
SELECT TOP 1 @dbname = name , @sql = REPLACE(@sqltemplate, '@dbname', name) FROM #db f ORDER BY f.name;
DELETE FROM #db WHERE name = @dbname;
PRINT @sql;
END

Sources:
Establish a Database Mirroring Session Using Windows Authentication (SQL Server Management Studio
https://technet.microsoft.com/en-us/library/ms188712(v=sql.110).aspx

How to: Prepare a Mirror Database for Mirroring (Transact-SQL)
https://technet.microsoft.com/en-us/library/ms189047(v=sql.105).aspx

Add a Database Mirroring Witness Using Windows Authentication (Transact-SQL)
https://technet.microsoft.com/en-us/library/ms190430(v=sql.110).aspx

The Database Mirroring Endpoint (SQL Server)
https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/the-database-mirroring-endpoint-sql-server

Create a Database Mirroring Endpoint for Windows Authentication (Transact-SQL)
https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/create-a-database-mirroring-endpoint-for-windows-authentication-transact-sql

How to: Remove Database Mirroring (Transact-SQL)
https://technet.microsoft.com/en-us/library/ms189112(v=sql.105).aspx

Manually Fail Over a Database Mirroring Session (Transact-SQL)
https://docs.microsoft.com/en-us/sql/database-engine/database-mirroring/manually-fail-over-a-database-mirroring-session-transact-sql