- ----判断数据库是否正在被使用,如果被使用中,那么中止使用数据库的进程
- IF EXISTS(SELECT * FROM sys.sysprocesses WHERE dbid=DB_ID(N'SampleData'))
- BEGIN
- DECLARE @s NVARCHAR(1000)
- DECLARE myCur CURSOR FOR
- SELECT 'kill '+CAST(spid AS VARCHAR) FROM sys.sysprocesses WHERE dbid=DB_ID(N'SampleData')
- OPEN myCur
- FETCH NEXT FROM myCur INTO @s
- WHILE @@FETCH_STATUS =0
- BEGIN
- EXEC(@S)
- FETCH NEXT FROM myCur INTO @s
- END
- CLOSE myCur
- DEALLOCATE myCur
- END
- ----新建数据库
- IF EXISTS(SELECT * FROM sys.sysdatabases WHERE dbid =db_id(N'SampleData'))
- DROP DATABASE SampleData
- ---IF DB_ID(N'SampleData') IS NOT NULL
- --- DROP DATABASE SampleData
- CREATE DATABASE SampleData ON PRIMARY
- (NAME=N'SampleData', FILENAME=N'E:\Sample\SampleData.MDF',SIZE=10MB,MAXSIZE=UNLIMITED,FILEGROWTH=10%),
- FILEGROUP FG1 DEFAULT
- (NAME=N'SampleData_01',FILENAME=N'E:\Sample\SampleData_01.ndf',SIZE=10MB,MAXSIZE=UNLIMITED,FILEGROWTH=10%),
- (NAME=N'SampleData_02',FILENAME=N'E:\Sample\SampleData_02.ndf',SIZE=10MB,MAXSIZE=UNLIMITED,FILEGROWTH=10%)
- LOG ON
- (NAME=N'SampleData_Log',FILENAME=N'E:\Sample\SampleData_Log.ldf',SIZE=10MB,MAXSIZE=UNLIMITED,FILEGROWTH=10%)
- COLLATE Chinese_PRC_CI_AS
- GO
- ----将数据库的恢复模式设定为【FULL】
- IF NOT EXISTS(SELECT recovery_model FROM sys.databases WHERE database_id =DB_ID(N'SampleData') AND recovery_model=1)
- ALTER DATABASE SampleData SET RECOVERY FULL
- ----在数据库中新建表[TestTable],并添加测试数据
- USE SampleData
- IF EXISTS(SELECT * FROM sys.objects WHERE object_id =OBJECT_ID(N'dbo.TestTable') AND type IN (N'U'))
- DROP TABLE dbo.TestTable
- CREATE TABLE dbo.TestTable
- (ID INT IDENTITY(1,1),
- Column1 VARCHAR(50) NOT NULL,
- CONSTRAINT pk_testtableID PRIMARY KEY(ID))
- GO
- INSERT INTO dbo.TestTable(Column1) VALUES('ROWA')
- INSERT INTO dbo.TestTable(Column1) VALUES('ROWB')
- INSERT INTO dbo.TestTable(Column1) VALUES('ROWC')
- INSERT INTO dbo.TestTable(Column1) VALUES('ROWD')
- INSERT INTO dbo.TestTable(Column1) VALUES('ROWE')
- ----完整备份数据库
- BACKUP DATABASE [SampleData] TO DISK = N'E:\SampleBack\SampleDataAllback.bak' WITH NOFORMAT, NOINIT, NAME = N'SampleData-完整数据库备份', SKIP, NOREWIND, NOUNLOAD, STATS = 10
- ----事务日志备份(截断日志)
- BACKUP LOG [SampleData] TO DISK = N'E:\SampleBack\SampleDataLogBack.bak' WITH NOFORMAT, NOINIT, NAME = N'SampleData-事务日志 备份', SKIP, NOREWIND, NOUNLOAD, STATS = 10
- ----在辅助数据库服务器上执行还原
- ------还原完整备份
- --------RESTORE DATABASE [SampleData] FROM DISK = N'E:\SampleBack\SampleDataAllback.bak' WITH FILE = 1, MOVE N'SampleData_01' TO N'E:\Sample\SampleData_1.ndf', MOVE N'SampleData_02' TO N'E:\Sample\SampleData_2.ndf', MOVE N'SampleData_Log' TO N'E:\Sample\SampleData_3.ldf', NORECOVERY, NOUNLOAD, STATS = 10
- --------GO
- ------还原事务日志备份
- --------RESTORE LOG [SampleData] FROM DISK = N'E:\SampleBack\SampleDataLogBack.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
- ----说明:数据库镜像的两种身份验证方式:证书和Windows身份验证
- ----使用基于证书的身份验证方式设置数据库镜像
- -------第一步:配置[出站连接]
- ---------配置主机服务器的出站连接
- ------------在master数据库中创建数据库主密钥
- USE master
- CREATE MASTER KEY ENCRYPTION BY PASSWORD='hdf007@163.com'
- ------------为主机服务器数据库实例制作一个证书
- USE master
- CREATE CERTIFICATE HOST_A_Cert WITH SUBJECT='HOST_A certificate'
- ------------使用该证书为主机服务器数据库实例创建一个镜像端口
- CREATE ENDPOINT Endpoint_Mirroring
- STATE=STARTED
- AS TCP (LISTENER_PORT=7024,LISTENER_IP=ALL)
- FOR DATABASE_MIRRORING (AUTHENTICATION=CERTIFICATE HOST_A_Cert,ENCRYPTION=REQUIRED ALGORITHM AES,ROLE=ALL)
- ------------备份HOST_A证书,并将其复制到辅助服务器、见证服务器
- BACKUP CERTIFICATE HOST_A_Cert TO FILE='C:\HOST_A_Cert.cer'
- ---------配置辅助服务器的出站连接
- ------------在master数据库中创建数据库主密钥
- USE master
- CREATE MASTER KEY ENCRYPTION BY PASSWORD='hdf007@163.com'
- ------------为辅助服务器数据库实例制作一个证书
- USE master
- CREATE CERTIFICATE HOST_B_Cert WITH SUBJECT='HOST_B certificate'
- ------------使用该证书为辅助服务器数据库实例创建一个镜像端口
- CREATE ENDPOINT Endpoint_Mirroring
- STATE=STARTED
- AS TCP (LISTENER_PORT=7024,LISTENER_IP=ALL)
- FOR DATABASE_MIRRORING (AUTHENTICATION=CERTIFICATE HOST_B_Cert,ENCRYPTION=REQUIRED ALGORITHM AES,ROLE=ALL)
- ------------备份HOST_B证书,并将其复制到主机服务器、见证服务器
- BACKUP CERTIFICATE HOST_B_Cert TO FILE='C:\HOST_B_Cert.cer'
- ----------配置见证服务器的出站连接
- USE master
- CREATE MASTER KEY ENCRYPTION BY PASSWORD='hdf007@163.com'
- ---------为见证服务器数据库实例制作一个证书
- USE master
- CREATE CERTIFICATE HOST_C_Cert WITH SUBJECT='HOST_C_certificate'
- ---------使用该证书为见证服务器数据库实例创建一个镜像端口
- CREATE ENDPOINT Endpoint_Mirroring
- STATE=STARTED
- AS TCP (LISTENER_PORT=7024,LISTENER_IP=ALL)
- FOR DATABASE_MIRRORING (AUTHENTICATION=CERTIFICATE HOST_C_Cert,ENCRYPTION=REQUIRED ALGORITHM AES,ROLE=ALL)
- ---------备份HOST_C证书,并将其复制到主机服务器、辅助服务器
- BACKUP CERTIFICATE HOST_C_Cert TO FILE='C:\HOST_C_Cert.cer'
- -------第二步:配置[入站连接]
- ---------配置主机服务器的入站连接
- ------------在主机服务器上为镜像服务器创建一个登录名
- USE master
- CREATE LOGIN HOST_B_Login WITH PASSWORD='hdf007@163.com'
- ------------创建一个该登录名的用户
- CREATE USER HOST_B_User FROM LOGIN HOST_B_Login
- ------------使用从镜像服务器复制过来的证书与此用户关联
- CREATE CERTIFICATE HOST_B_Cert AUTHORIZATION HOST_B_User FROM FILE='C:\HOST_B_Cert.cer'
- ------------授予对远程镜像端点的登录名的 CONNECT 权限
- GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_Login]
- ------------在主机服务器上为见证服务器创建一个登录名
- USE master
- CREATE LOGIN HOST_C_Login WITH PASSWORD='hdf007@163.com'
- ------------创建一个该登录名的用户
- CREATE USER HOST_C_User FROM LOGIN HOST_C_Login
- ------------使用从见证服务器复制过来的证书与此用户关联
- CREATE CERTIFICATE HOST_C_Cert AUTHORIZATION HOST_C_User FROM FILE='C:\HOST_C_Cert.cer'
- ------------授予对远程镜像端点的登录名的CONNECT权限
- GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_C_Login]
- ---------配置辅助服务器的入站连接
- ------------在辅助服务器上为主机服务器创建一个登录名
- USE master
- CREATE LOGIN HOST_A_Login WITH PASSWORD='hdf007@163.com'
- ------------创建一个该登录名的用户
- CREATE USER HOST_A_User FROM LOGIN HOST_A_Login
- ------------使用从主机服务器复制过来的证书与此用户关联
- CREATE CERTIFICATE HOST_A_Cert AUTHORIZATION HOST_A_User FROM FILE='C:\HOST_A_Cert.cer'
- ------------授予对远程镜像端点的登录名的 CONNECT 权限
- GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_Login]
- ------------在辅助服务器上为见证服务器创建一个登录名
- USE master
- CREATE LOGIN HOST_C_Login WITH PASSWORD='hdf007@163.com'
- ------------创建一个该登录名的用户
- CREATE USER HOST_C_User FROM LOGIN HOST_C_Login
- ------------使用从见证服务器复制过来的证书与此用户关联
- CREATE CERTIFICATE HOST_C_Cert AUTHORIZATION HOST_C_User FROM FILE='C:\HOST_C_Cert.cer'
- ------------授予对远程镜像端点的登录名的CONNECT权限
- GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_C_Login]
- ---------配置见证服务器的入站连接
- ------------在见证服务器上为主机服务器创建一个登录名
- USE master
- CREATE LOGIN HOST_A_Login WITH PASSWORD='hdf007@163.com'
- ------------创建一个该登录名的用户
- CREATE USER HOST_A_User FROM LOGIN HOST_A_Login
- ------------使用从主机服务器复制过来的证书与此用户关联
- CREATE CERTIFICATE HOST_A_Cert AUTHORIZATION HOST_A_User FROM FILE='C:\HOST_A_Cert.cer'
- ------------授予对远程镜像端点的登录名的 CONNECT 权限
- GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_Login]
- ------------在见证服务器上为辅助服务器创建一个登录名
- USE master
- CREATE LOGIN HOST_B_Login WITH PASSWORD='hdf007@163.com'
- ------------创建一个该登录名的用户
- CREATE USER HOST_B_User FROM LOGIN HOST_B_Login
- ------------使用从见证服务器复制过来的证书与此用户关联
- CREATE CERTIFICATE HOST_B_Cert AUTHORIZATION HOST_B_User FROM FILE='C:\HOST_B_Cert.cer'
- ------------授予对远程镜像端点的登录名的CONNECT权限
- GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_Login]
- -------第三步:配置镜像伙伴
- ---------在镜像服务器上将主机服务器设置为伙伴
- ALTER DATABASE SampleData SET PARTNER='TCP://S1:7024'
- ---------在主机服务器上将镜像服务器设置为伙伴
- ALTER DATABASE SampleData SET PARTNER='TCP://S2:7024'
- ---------在主机服务器上设置见证服务器
- ALTER DATABASE SampleData SET WITNESS='TCP://S2:7024'
- GO
- -----以下代码是在没有见证服务器的情况下实现手动故障转移
- ---------若要在高性能模式下配置此会话,在主体服务器实例上,将事务安全性设置为 OFF。
- ALTER DATABASE SampleData SET PARTNER SAFETY OFF
- ---------若要在高安全模式下配置些会话,在主体服务器实例上,将事务安全性设置为FULL
- ALTER DATABASE SampleData SET PARTNER SAFETY FULL
- ---------在高性能模式下,使用下列语句在镜像服务器上强制执行实现故障转移,会丢失数据
- ALTER DATABASE SampleData SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
- ---------在高安全模式下,使用下列语句实现手工故障转移
- ALTER DATABASE SampleData SET PARTNER FAILOVER
- ---------原来的主服务器恢复,可以继续工作,需要重新设定镜像
- ---------在辅助服务器上执行:
- USE master
- ALTER DATABASE SampleData SET PARTNER RESUME --恢复镜像
- ALTER DATABASE SampleData SET PARTNER FAILOVER --切换主备
- ----使用基于Windows的身份验证方式设置数据库镜像
- ------------------------------------------------------------
- -- 主机服务器 S1 S1\S1User --
- -- 辅助服务器 S2 S2\S2User --
- -- 见证服务器 S3 S3\S3User --
- ------------------------------------------------------------
- ---在主机服务器上创建一个镜像端点
- CREATE ENDPOINT Endpoint_Mirroring STATE=STARTED AS TCP (LISTENER_PORT=7024) FOR DATABASE_MIRRORING (ROLE=ALL)
- ---在主机服务器上为辅助服务器创建一个登录
- CREATE LOGIN [S2\S2User] FROM WINDOWS
- ---授予对远程镜像端点的登录名的CONNECT权限
- GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [S2\S2User]
- ---在主机服务器上为见证服务器创建一个登录
- CREATE LOGIN [S3\S3User] FROM WINDOWS
- ---授予对远程镜像端点的登录名的CONNECT权限
- GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [S3\S3User]
- ---在辅助服务器上创建一个镜像端点
- CREATE ENDPOINT Endpoint_Mirroring STATE=STARTED AS TCP (LISTENER_PORT=7024) FOR DATABASE_MIRRORING (ROLE=ALL)
- ---在辅助服务器上为主机服务器创建一个登录
- CREATE LOGIN [S1\S2User] FROM WINDOWS
- ---授予对远程镜像端点的登录名的CONNECT权限
- GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [S1\S2User]
- ---在辅助服务器上为见证服务器创建一个登录
- CREATE LOGIN [S3\S3User] FROM WINDOWS
- ---授予对远程镜像端点的登录名的CONNECT权限
- GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [S3\S3User]
- ---在见证服务器上创建一个镜像端点
- CREATE ENDPOINT Endpoint_Mirroring STATE=STARTED AS TCP (LISTENER_PORT=7024) FOR DATABASE_MIRRORING (ROLE=ALL)
- ---在见证服务器上为主机服务器创建一个登录
- CREATE LOGIN [S1\S1User] FROM WINDOWS
- ---授予对远程镜像端点的登录名的CONNECT权限
- GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [S1\S1User]
- ---在见证服务器上为辅助服务器创建一个登录
- CREATE LOGIN [S2\S2User] FROM WINDOWS
- ---授予对远程镜像端点的登录名的CONNECT权限
- GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [S2\S2User]
- ----配置镜像伙伴
- ---------在镜像服务器上将主机服务器设置为伙伴
- ALTER DATABASE SampleData SET PARTNER='TCP://S1:7024'
- ---------在主机服务器上将镜像服务器设置为伙伴
- ALTER DATABASE SampleData SET PARTNER='TCP://S2:7024'
- ---------在主机服务器上设置见证服务器
- ALTER DATABASE SampleData SET WITNESS='TCP://S2:7024'
转载于:https://blog.51cto.com/hdf007/824297