镜像数据库

liyihongcug

数据库镜像配置
[服务器初始配置]
SQL-A:    主体服务器
SQL-B:    镜像服务器
SQL-W:    见证服务器

[镜像数据库]
TEST

[过程]
配置过程一共分为4个部分

一、准备镜像数据库

-- 1.在SQL-A上，对TEST数据库做完全备份
BACKUP DATABASE TEST
TO  DISK = 'C:\Tools\backup.bak'
GO

-- 2.在SQL-B上，将SQL-A上恢复TEST数据库的备份，并使用NORECOVERY选项
RESTORE DATABASE TEST
FROM  DISK = 'C:\Tools\backup.bak' WITH  NORECOVERY
GO


二、创建数据库镜像端点
--    1.在SQL-A上，创建数据库镜像端点，用于伙伴通讯
CREATE ENDPOINT DbMirroringEP
AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING (ROLE = PARTNER, ENCRYPTION = SUPPORTED);
GO

ALTER ENDPOINT DbMirroringEP STATE = STARTED
GO

-- 2.在SQL-B上，创建数据库镜像端点，用于伙伴通讯
CREATE ENDPOINT DbMirroringEP
AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING (ROLE = PARTNER, ENCRYPTION = SUPPORTED);
GO

ALTER ENDPOINT DbMirroringEP STATE = STARTED
GO

-- 3.在SQL-W上，创建数据库镜像端点，用于见证通讯
CREATE ENDPOINT DbMirroringEP
AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING (ROLE = WITNESS, ENCRYPTION = SUPPORTED);
GO

ALTER ENDPOINT DbMirroringEP STATE = STARTED
GO

-- 4.检查端点配置
SELECT * FROM sys.database_mirroring_endpoints
GO

三、配置数据库镜像安全性

        /*
            说明：
            当前的所有服务器使用相同的服务账户SQL2k5SP1\SQLService名称和口令
  


-- 1.
USE master
GO
GRANT CONNECT ON ENDPOINT::"DbMirroringEP" TO "SQL2k5SP1\SQLService"
GO

-- 2.
USE master
GO
GRANT CONNECT ON ENDPOINT::"DbMirroringEP" TO "SQL2k5SP1\SQLService"
GO

-- 3.
USE master
GO
GRANT CONNECT ON ENDPOINT::"DbMirroringEP" TO "SQL2k5SP1\SQLService"
GO

四、启动数据库镜像

--注意顺序，需要在首先在镜像服务器上配置伙伴

-- 1.在SQL-B上，指定伙伴端点
ALTER DATABASE TEST
SET PARTNER = N'TCP://SQL-A:5022'
GO

-- 2.在SQL-A上，指定伙伴端点
ALTER DATABASE TEST
SET PARTNER = N'TCP://SQL-B:5022'
GO

-- 3.在SQL-A上，指定证服务器端点
ALTER DATABASE TEST
SET WITNESS = N'TCP://SQL-W:5022'
GO

-- 3.配置数据库镜像事务安全级别
ALTER DATABASE TEST SET SAFETY FULL
GO

五、查看数据库镜像的配置状态

-- 1.)通过Management studio 对象资源管理器，查看主体数据库、镜像数据库状态

-- 2.)通过Management studio 对象资源管理器中的数据库属性查看状态

-- 3.)通过系统目录视图查看数据库镜像配置情况
SELECT * FROM sys.database_mirroring_endpoints
SELECT * FROM sys.database_mirroring WHERE database_id =
    (SELECT database_id FROM sys.databases WHERE name = 'SQL')


SELECT * FROM sys.database_mirroring_endpoints;
SELECT * FROM sys.database_mirroring ;

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

liyihongcug

sys.database_mirroring (Transact-SQL)
SQL Server 2005
其他版本

    * SQL Server 2008 R2
    * SQL Server 2008

更新日期： 2006 年 4 月 14 日

实例 SQL Server 中的每个数据库在表中占一行。如果数据库未联机，或未启用数据库镜像，则除 database_id 以外的所有列的值都将为 NULL。

若要查看除 master 或 tempdb 以外的数据库行，您必须是数据库所有者或至少在 master 数据库中拥有 ALTER ANY DATABASE 或 VIEW ANY DATABASE 服务器级别的权限或 CREATE DATABASE 权限。
ms178655.note(zh-cn,SQL.90).gif注意：
如果数据库未参与镜像，则所有带有 mirroring_ 前缀的列将为 NULL。

列名         数据类型         说明

database_id
       

int
       

数据库的 ID。在 SQL Server 实例中是唯一的。

mirroring_guid
       

uniqueidentifier
       

镜像合作关系的 ID。

NULL = 数据库不可访问或未镜像。
如果数据库未参与镜像，则所有带有 mirroring_ 前缀的列将为 NULL。
ms178655.note(zh-cn,SQL.90).gif注意：

mirroring_state
       

tinyint
       

镜像数据库的状态和数据库镜像会话的状态。

0 = 已挂起

1 = 与其他伙伴断开

2 = 正在同步

3 = 挂起故障转移

4 = 已同步

NULL = 数据库不可访问或未镜像。

mirroring_state_desc
       

nvarchar(60)
       

镜像数据库状态和数据库镜像会话状态的说明，可以是下列值之一：

DISCONNECTED

SYNCHRONIZED

SYNCHRONIZING

PENDING_FAILOVER

SUSPENDED

NULL

有关详细信息，请参阅镜像状态。

mirroring_role
       

tinyint
       

本地数据库在数据库镜像会话中的当前角色。

1 = 主体数据库

2 = 镜像数据库

NULL = 数据库不可访问或未镜像。

mirroring_role_desc
       

nvarchar(60)
       

本地数据库在镜像中的角色说明，可以是以下值之一：

PRINCIPAL

MIRROR

mirroring_role_sequence
       

int
       

由于故障转移或强制服务，导致镜像伙伴在主体数据库角色和镜像数据库角色之间进行切换的次数。

NULL = 数据库不可访问或未镜像。

mirroring_safety_level
       

tinyint
       

镜像数据库更新的安全设置：

0 = 未知状态

1 = 关闭 [异步]

2 = 完全 [同步]

NULL = 数据库不可访问或未镜像。

mirroring_safety_level_desc
       

tinyint
       

镜像数据库更新的事务安全设置，可以是下列值之一：

UNKNOWN

OFF

FULL

NULL

mirroring_safety_sequence
       

int
       

将更改的序列号更新为事务安全级别。

NULL = 数据库不可访问或未镜像。

mirroring_partner_name
       

nvarchar(12
       

数据库镜像伙伴的服务器名称。

NULL = 数据库不可访问或未镜像。

mirroring_partner_instance
       

nvarchar(12
       

其他伙伴的实例名和计算机名称。如果伙伴成为主体服务器，则客户端需要此信息以连接到该伙伴服务器。

NULL = 数据库不可访问或未镜像。

mirroring_witness_name
       

nvarchar(12
       

数据库镜像见证服务器的服务器名称

NULL = 不存在见证服务器。

mirroring_witness_state
       

tinyint
       

数据库的数据库镜像会话中的见证服务器状态，可以是下列值之一：

0 = 未知

1= 已连接

2 = 已断开

NULL = 见证服务器不存在，数据库未联机或未镜像。

mirroring_witness_state_desc
       

nvarchar(60)
       

状态说明，可以是下列值之一：

UNKNOWN

CONNECTED

DISCONNECTED

NULL

mirroring_failover_lsn
       

numeric(25,0)
       

保证将被镜像到两个伙伴服务器磁盘中的最新事务日志记录的日志序列号 (LSN)。故障转移之后，伙伴服务器便会将 mirroring_failover_lsn 用作调解点，在这个调解点上，新的镜像服务器开始将新的镜像数据库与新的主体数据库进行同步。

mirroring_connection_timeout
       

int
       

镜像连接超时值（秒）。这是等待伙伴和见证服务器回复的秒数，超过该指定时间之后伙伴或见证服务器被视为不可用。默认超时值为 10 秒。

NULL = 数据库不可访问或未镜像。

mirroring_redo_queue
       

int
       

对镜像服务器重做的最大日志量。如果 mirroring_redo_queue_type 已设置为默认设置 UNLIMITED，则此列为 NULL。如果数据库未联机，则该列也为 NULL。

否则，该列包含最大日志量 (MB)。如果达到最大值，则当镜像服务器也达到同一值时，日志将在主体服务器上临时停止。此功能限制故障转移时间。

有关详细信息，请参阅估计角色切换过程中的服务中断。

mirroring_redo_queue_type
       

nvarchar(60)
       

UNLIMITED 指示镜像不会禁止重做队列。这是默认设置。

以兆字节为单位的重做队列的最大大小 (MB)。注意，如果队列大小以 KB 或 GB 形式指定，则 SQL Server 2005 数据库引擎会将该值转换为 MB。

如果数据库未联机，则该列为 NULL。

liyihongcug

http://msdn.microsoft.com/zh-cn/library/ms178655.aspx
sys.database_mirroring_endpoints (Transact-SQL)
SQL Server 2005
其他版本

    * SQL Server 2008 R2
    * SQL Server 2008

服务器实例的数据库镜像端点对应一行。
列名         数据类型         说明

<继承列>
       

—
       

从 sys.endpoints 继承列（有关详细信息，请参阅 sys.endpoints (Transact-SQL)）。

role
       

tinyint
       

镜像角色，为以下值之一：

0 = 无

1 = 伙伴角色

2 = 见证角色

3 = 所有角色

role_desc
       

nvarchar(60)
       

镜像角色的说明，为以下值之一：

    * NONE
    * PARTNER
    * WITNESS
    * ALL

is_encryption_enabled
       

bit
       

1 表示启用加密。

0 表示禁用加密。

connection_auth
       

tinyint
       

连接到此端点所需的连接身份验证的类型，为以下值之一：

1 - NTLM

2 - KERBEROS

3 - NEGOTIATE

4 - CERTIFICATE

5 - NTLM、CERTIFICATE

6 - KERBEROS、CERTIFICATE

7 - NEGOTIATE、CERTIFICATE

8 - CERTIFICATE、NTLM

9 - CERTIFICATE、KERBEROS

10 - CERTIFICATE、NEGOTIATE

connection_auth_desc
       

Nvarchar (60)
       

连接到此端点所需的身份验证类型的说明，为以下值之一：

    * NTLM
    * KERBEROS
    * NEGOTIATE
    * CERTIFICATE
    * NTLM、CERTIFICATE
    * KERBEROS、CERTIFICATE
    * NEGOTIATE、CERTIFICATE
    * CERTIFICATE、NTLM
    * CERTIFICATE、KERBEROS
    * CERTIFICATE、NEGOTIATE

certificate_id
       

int
       

身份验证所用证书的 ID（如果有）。

NULL = 使用 Windows 身份验证。

encryption_algorithm
       

tinyint
       

加密算法，为以下值之一：

0 – NONE

1 – RC4

2 – AES

3 – NONE, RC4

4 – NONE, AES

5 – RC4, AES

6 – AES, RC4

7 – NONE, RC4, AES

8 – NONE, AES, RC4

encryption_algorithm_desc
       

nvarchar(60)
       

加密算法的说明，为以下值之一：

NONE

RC4

AES

NONE, RC4

NONE, AES

RC4, AES

AES, RC4

NONE, RC4, AES

NONE, AES, RC4

liyihongcug

QL Server 2008 Mirror


对SQL Server 2008 进行Mirror的基本配置及安装。
准备工作
1. Principal 与Mirror 以及 Witness 三台实例都安装为相同版本，目前是到SQL Server 2008 SP1
2. 需要有一个域帐户分别添加到 三台数据库实例所在的Server 的 Administrators组、以及SQL Server 相关的组中；
由于具体的组的功能不清楚，因此建议所有组都添加该域帐户，确保权限运行正常。
3. 将第二步中的域帐户设置为SQL Server 服务，SQL Server Agent 的启动帐户，并重启服务；
4. 确保Principal 与Miroor 两台实例上的数据数据及日志相同。备份模式为完全备份，恢复模式为完全，向Mirror实例上恢复数据库时必须选择为NoRecovery模式。
创建EndPoint
1. 在Principal与 Mirror实例上创建EndPoint，运行脚本：
CREATE ENDPOINT mirroring_ep
STATE=STARTED
AS TCP (LISTENER_PORT=5022)
FOR  DATABASE_MIRRORING
(ROLE = PARTNER, ENCRYPTION = SUPPORTED)
或：
CREATE ENDPOINT mirroring_ep
STATE=STARTED
AS TCP (LISTENER_PORT=5022)
FOR  DATABASE_MIRRORING
(AUTHENTICATION=WINDOWS NTLM,ROLE = PARTNER)
2. 在Witness实例上创建EndPoint，运行脚本：
CREATE ENDPOINT mirroring_ep
STATE = STARTED
AS TCP ( LISTENER_PORT = 5022 )
FOR DATABASE_MIRRORING (
    ENCRYPTION = SUPPORTED,
    ROLE=WITNESS );
或：
CREATE ENDPOINT mirroring_ep
STATE=STARTED
AS TCP (LISTENER_PORT=5022)
FOR  DATABASE_MIRRORING
(AUTHENTICATION=WINDOWS NTLM,ROLE = WITNESS)
查看EndPoint及删除EndPoint脚本：
Select * From Sys.database_mirroring_endpoints
Select * From sys.database_mirroring
Drop Endpoint endpoint_mirroring

创建 Mirror Session
1.在Mirror 实例上创建Session，运行脚本：
ALTER DATABASE  DataBaseName
SET PARTNER = 'TCP://PrincipalServerName:5022';
2.在Principal 实例上创建Session ，运行脚本：
ALTER DATABASE  DataBaseName
SET PARTNER = 'TCP://MirrorServerName:5022';
3.在Principal 实例上指定Witness，运行脚本：
ALTER DATABASE  HB2008_Gome
SET WITNESS = 'TCP://WitnessServerName:5022';
注意：
1.在Principal实例上创建Session时可能会遇到此错误，错误信息如下：
镜像数据库 "dbName" 包含的事务日志数据不足，无法保留主体数据库的日志备份链。如果没有从主体数据库进行日志备份或者没有在镜像数据库上还原日志备份，则可能会出现这种情况。
解决方法是在Principal实例上再进行一次日志备份，并按照NoRecovery模式还原至Mirror实例上。
原因是日志存在间断，可能在进行完全备份时又产生日志。

2.测试FailOver
ALTER DATABASE DataBase
SET PARTNER FAILOVER
3.相关查询表：
Sys.database_mirroring
Sys.database_mirroring_endpoints
Sys.database_mirroring_witnesses
4.计划任务提前备份：
可以将Principal上的计划任务提前生成脚本，在Mirror配置完成后，在Mirror实例上执行。

对SQL Server 2008 进行Mirror的基本配置及安装。
准备工作

1. Principal 与Mirror 以及 Witness 三台实例都安装为相同版本，目前是到SQL Server 2008 SP1。

2. 需要有一个域帐户分别添加到 三台数据库实例所在的Server 的 Administrators组、以及SQL Server 相关的组中； 由于具体的组的功能不清楚，因此建议所有组都添加该域帐户，确保权限运行正常。

3. 将第二步中的域帐户设置为SQL Server 服务，SQL Server Agent 的启动帐户，并重启服务。

4. 确保Principal 与Miroor 两台实例上的数据数据及日志相同。备份模式为完全备份，恢复模式为完全，向Mirror实例上恢复数据库时必须选择为NoRecovery模式。

创建EndPoint

1. 在Principal与 Mirror实例上创建EndPoint，运行脚本：
Create EndPoint On Principal and Mirror Instance

CREATE ENDPOINT mirroring_ep
STATE=STARTED
AS TCP (LISTENER_PORT=5022)
FOR DATABASE_MIRRORING
(ROLE = PARTNER, ENCRYPTION = SUPPORTED)
--或
CREATE ENDPOINT mirroring_ep
STATE=STARTED
AS TCP (LISTENER_PORT=5022)
FOR DATABASE_MIRRORING
(AUTHENTICATION=WINDOWS NTLM,ROLE = PARTNER)



2. 在Witness实例上创建EndPoint，运行脚本：
Create EndPoint On Witness Instance

CREATE ENDPOINT mirroring_ep
STATE = STARTED
AS TCP ( LISTENER_PORT = 5022 )
FOR DATABASE_MIRRORING
( ENCRYPTION = SUPPORTED, ROLE=WITNESS );
--或
CREATE ENDPOINT mirroring_ep
STATE=STARTED
AS TCP (LISTENER_PORT=5022)
FOR DATABASE_MIRRORING
(AUTHENTICATION=WINDOWS NTLM,ROLE = WITNESS)





查看EndPoint及删除EndPoint脚本：

Select * From Sys.database_mirroring_endpoints

Select * From sys.database_mirroring

Drop Endpoint endpoint_mirroring



创建 Mirror Session

1.在Mirror 实例上创建Session，运行脚本：

ALTER DATABASE  DataBaseName

SET PARTNER = 'TCP://PrincipalServerName:5022';

2.在Principal 实例上创建Session ，运行脚本：

ALTER DATABASE  DataBaseName

SET PARTNER = 'TCP://MirrorServerName:5022';

3.在Principal 实例上指定Witness，运行脚本：

ALTER DATABASE  HB2008_Gome

SET WITNESS = 'TCP://WitnessServerName:5022';
注意：
1.在Principal实例上创建Session时可能会遇到此错误，错误信息如下：
镜像数据库 "dbName" 包含的事务日志数据不足，无法保留主体数据库的日志备份链。如果没有从主体数据库进行日志备份或者没有在镜像数据库上还原日志备份，则可能会出现这种情况。
解决方法是在Principal实例上再进行一次日志备份，并按照NoRecovery模式还原至Mirror实例上。
原因是日志存在间断，可能在进行完全备份时又产生日志。
2.测试FailOver
　　  ALTER DATABASE DataBase
SET PARTNER FAILOVER
3.相关查询表：
Sys.database_mirroring
Sys.database_mirroring_endpoints
Sys.database_mirroring_witnesses
4.计划任务提前备份：
可以将Principal上的计划任务提前生成脚本，在Mirror配置完成后，在Mirror实例上执行。





5.镜像运行模式

　　1.高安全性模式：支持同步操作，事务在主体与镜像上同时提交，会延长事务滞后时间。

　　2.高性能模式：支持异步运行，镜像服务器可能稍微滞后于主体数据库。

　　值得说明的是，高安全性模式，由于是主体与镜像为同步操作，镜像的未提交会导致主体不进行提交，可能会有较大的影响。

　　 比如：DBServerA与DBServerB数据库互为镜像，DBServerA为主体，DBServerB为镜像；

　　　　　 同时DBServerA做了发布服务器，DBServerC从DBServerA做了订阅。应用系统在DBServerC上进行查询。

　　　　　若DBServerB 出现故障，则DBServerA无法提交，从而DBServerC查询数据不及时。



6.设置镜像连接超时时间

　　默认值为10s，通过更改Set Partner Timeout 来更改，如：

　　 ALTER DATABASE DataBaseName SET PARTNER TIMEOUT 20

liyihongcug

查看ms sqlserver数据库是否有进程死锁 收藏
最近我在弄数据库大批时间时，由于时间太长，我就中断了，中断后我先是update看到没有反映，又select，还是不出来，最后delete还是没反映，开始意识到可能是自己太心急了，然后就在一边等，但是五分钟过去了没有反映，十分钟过去了还是没有反映，网上查了查，可能是是数据库死锁了，解开死锁的方法有两个，一个是重启数据库，二就是用kill杀死死锁的进程，但是怎么知道哪个进程死锁了呢？最后还是网上找到了，别人写的存储过程，可以查看数据库死锁的进程，在这里和大家分享一下。

CREATE  procedure [dbo].[sp_who_lock]
as
begin
declare @spid int,@bl int,
        @intTransactionCountOnEntry  int,
        @intRowcount    int,
        @intCountProperties   int,
        @intCounter    int
create table #tmp_lock_who (id int identity(1,1),spid smallint,bl smallint)

IF @@ERROR<>0 RETURN @@ERROR

insert into #tmp_lock_who(spid,bl) select  0 ,blocked
   from (select * from master..sysprocesses where  blocked>0 ) a
   where not exists(select * from (select * from master..sysprocesses where  blocked>0 ) b
   where a.blocked=spid)
   union select spid,blocked from master..sysprocesses where  blocked>0

IF @@ERROR<>0 RETURN @@ERROR

-- 找到临时表的记录数
select  @intCountProperties = Count(*),@intCounter = 1
from #tmp_lock_who

IF @@ERROR<>0 RETURN @@ERROR

if @intCountProperties=0
  select '现在没有阻塞和死锁信息' as message

-- 循环开始
while @intCounter <= @intCountProperties
begin
-- 取第一条记录
  select  @spid = spid,@bl = bl
  from #tmp_lock_who where id = @intCounter
begin
  if @spid =0
    select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'
else
    select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
end

-- 循环指针下移
set @intCounter = @intCounter + 1
end


drop table #tmp_lock_who

return 0

中关村村草

写的比较详细，顶一下！学习

liyihongcug

http://tech.it168.com/db/s/2007-06-29/200706291229796_1.shtml  http://lihuojun.blog.51cto.com/687170/412520
http://www.jz123.cn/text/2633092.html

liyihongcug

傻瓜 开发
Sql server 数据库镜像（图）

数据的安全性、完整性、可靠性对于应用系统（特别是营运系统和收银系统）非常重要。而为尽量满足这些要求有以下几种方法：

1、服务器集群:

优点，这种方法对应用访问性高，缺点，需要存储，成本高，而且存储也是单点，存储故障，数据挂失可能性大。

2、数据库备份

优点，成本低，缺点，备份的完整性不高，数据出问题时，只能恢复到上一次备份的状态。

3、使用双机软件

目前接触的较少，一般品牌的比较贵，便宜的又不敢相信。

4、数据库镜像

优点，这种方法可靠性高，数据完整性也高，成本也不高，镜像服务器和见证服务器可利用其他应用服务器（因为镜像服务器和见证服务器不实时提供应用服务，对性能要求不高）。

下面利用数据库镜像过程，因为没见证服务器，所以不能自动切换，这种方式可以保证数据的完整性，这个过程有两个要求：第一、数据库版本必须是sql server 2005 sp1以上的版本，第二、两台服务器必须都域控制器。

一、在两台域控制器上安装sql server2005 ,并升级到sp1。

二、在主机服务器创建一个数据库qyg（可以自己定义），数据库的恢复模式要选择“FULL”,然后完整备份数据库如下图。

三、在镜像服务器上还原，如下图

在还原选项中选上“覆盖现有数据库”，恢复状态选择“不对数据库执行任何操作，不回滚提交的事务。可以还原其他事务日志。

四、 启动配置数据库镜像安全向导

连接到主体服务器实例之后，在对象资源管理器中，单击服务器名称以展开服务器树。展开“数据库”，再选择要镜像的数据库"qyg"。右键单击数据库，选择“任务”，再单击“镜像”。这样便可打开“数据库属性”对话框的“镜像”页。单击“配置安全性”。

下一步，因为没有见证服务器，所以这里选择“否”，

下一步

下一步，这里是主体服务器，也就是本机，默认就行。

下一步，点击“连接”服务器名称填写镜像服务器的主机名或IP，因为都是一个域，所以使用“windows 身份验证”，OK

连接，端口和端点名称默认就行

下一步，因为使用域管理，所以主体和镜像都为空

完成，

开始镜像

确定

查看状态，主机数据库服务器显示为：“主体，已同步”

镜像服务器显示为：“镜像，已同步/正在还原”

测试，右键单击数据库，选择“任务”，再单击“镜像”。这样便可打开“数据库属性”对话框的“镜像”页。单击“故障转移”。确认

故障转移后，主机服务器的状态显示“镜像，已同步/正在还原”

镜像服务器状态显示为：“主体，已同步”

同样可以在主机服务器执行transact-sql语句：

"Use master

alter database <DatabaseName> set partner failover

Go"

进行故障转移，也可以把主机的网络断开，在镜像服务器上执行transact-sql语句：

“Use master

alter database <DatabaseName> set partner force_service_allow_data_loss

Go”

把镜像服务器启用，状态为“主机，断开”，为应用系统服务。

当主机服务器正常后，在镜像服务器上执行transact-sql语句：

“USE master;

ALTER DATABASE <DatabaseName> SET PARTNER RESUME; --恢复镜像

ALTER DATABASE <DatabaseName> SET PARTNER FAILOVER; --切换主备”

恢复正常镜像。

另外还可以使用：

“ALTER DATABASE <DatabaseName> SET PARTNER SUSPEND

ALTER DATABASE <DatabaseName> SET PARTNER RESUME”

暂停镜像和启用镜像，以便于维护。

使用“ALTER DATABASE <database_name> SET PARTNER OFF”，删除镜像。

liyihongcug

SSQL 从 2005 开始推出镜像功能.

　　SQL Server镜像是不可读的.整个库在个 norecovery模式.

　　SQL Server镜像只能是1对1 但可以用镜像+logshipping 混合实现一对多和镜像链.

　　SQL Server镜像认证分为 证书 和windows验证.windows验证必须在一个域里面实现.

　　并不是每个公司的生产环境都是一个域.域的好处是可以做故障转移群集+镜像混合实现保障.

　　下面就说下认证方式的镜像

　　环境:win2008 +mssql2008

　　1 在主库上面创建数据库主密钥

　　create master key encryption by password ='123!@#abc';

　　2 创建证书

　　create certificate host_105with subject='host_105_c'

　　3创建端点

　　create endpoint endport_mirrorstate=started as tcp(listener_port=1522, listener_ip=all)for database_mirroring(authentication=certificate host_105, encryption=required algorithm AES, role=all );

　　4 备份证书并把证书复制到镜像服务器上

　　backup certificate host_105 to file='c:\host_105.cer';

　　5 在备份机上面重复1-4步骤 (证书名不一样 把 所有带 host_105替换成host_106)

　　6 在主机上创建sql验证账户并和证书绑定

　　create login mirror_b with password='xwj1234!@#

liyihongcug

http://topic.csdn.net/u/20081010 ... e-46dbf936e0cb.html  SQL备份与还原经典文档 ====================================================================== SQL备份 ====================================================================== ---------------------------------------------------------------------- 1、SQL数据库恢复模型 ---------------------------------------------------------------------- 1）完全恢复模型 ----------------- （1）备份时要备份数据库的数据文件和日志文件（2）还原时使用数据库的备份的数据文件副本和全部日志信息来恢复数据库。（3）能还原全部数据，并可以将数据库恢复到任意指定的时刻。（4）为保证实现即时点恢复，对数据库的所有*作都将完整地记入日志，这样，日志占用空间较大，对性能也有所影响。 ------------------ （2）大容量日志记录恢复模型 ------------------ （1）备份时要备份数据库的数据文件和日志文件（2）还原时使用数据库的备份的数据文件副本和全部日志信息来恢复数据库。（3）日志中不记录*作细节（如select into、create index等），而只记录*作的最终结果，因此占用日志空间小。（4）只支持将数据库还原到事务日志备份的时刻，而不支持即时点恢复，因此可能产生数据丢失。 ------------------- （3）简单恢复模型 ------------------- （1）备份时只备份数据文件，还原时也用备份的数据文件恢复数据库。（2）只能将数据恢复到数据文件备份的时刻，可能产生最多的数据丢失。（3）不适于生产系统和大规模*作环境下选用。 ----------------------------------------- alter database d1 set recovery simple --设置数据库恢复模型 alter database d1 set recovery bulk_logged alter database d1 set recovery full ---------------------------------------------------------------------- 2、备份设备 ---------------------------------------------------------------------- 1）物理设备 --------------------------- disk：支持本地磁盘或者网络备份 tape：支持磁带机备份 name pipe：支持第三方备份软件 --------------------------- 2）逻辑设备 --------------------------- 永久备份文件：可以重复使用，应该在备份前创建。临时备份文件：用于一次性备份，在备份时创建。 ------------------------------------------------- exec sp_addumpdevice 'disk','bak2','e:\back_device\bak2.bak' --创建永久磁盘备份设备 exec sp_addumpdevice 'disk','bak3','e:\back_device\bak3.bak' ---------------------------------------------------------------------- exec sp_addumpdevice 'disk','bak4','\\sv2\backup\bak4.bak' --创建网络永久磁盘备份设备 exec sp_addumpdevice 'disk','bak5','\\sv2\backup\bak5.bak' ---------------------------------------------------------------------- exec sp_dropdevice 'bak5' --删除备份设备 ---------------------------------------------------------------------- backup database d3 to bak3 --将数据库备份到备份设备 backup database d4 to bak4 ---------------------------------------------------------------------- restore headeronly from bak2 --查看备份设备中的内容 ---------------------------------------------------------------------- backup database d3 to disk='e:\back_file\d3.bak' --将数据库备份到临时备份文件 backup database d4 to disk='e:\back_file\d4.bak' ---------------------------------------------------------------------- restore database d3 from bak3 --从备份设备还原数据库 restore database d4 from disk='e:\back_file\d4.bak' --从备份文件还原数据库 ---------------------------------------------------------------------- 3、使用多个备份文件存储备份 ---------------------------------------------------------------------- 1）SQL可同时向多个备份文件进行写*作。如果把这些文件放到多个磁带机或磁盘中，则可提高备份速度。 2）这多个备份文件必须用同业型的媒体，并放到一个媒体集中。 3）媒体集中的文件必须同时使用，而不能单独使用。 4）可以通过format命令将媒体集重新划分，但原备份集中的数据不能再使用。 -------------------------------------------------------------------- backup database d4 to bak4,bak5,bak6 with medianame='bak456',format --备份D4并形成Media Set backup database d3 to bak4 --失败，因Media set中文件必须同时使用 backup database d3 to bak4,bak5,bak6 --成功，将D3也备份到Media Set中 restore headeronly from bak4,bak5,bak6--查看Media Set中的备份内容 ---------------------------------------------------------------------- backup database d4 to bak4 with medianame='bak4',format --重新划分Media Set backup database d3 to bak5,bak6 with medianame='bak56',format ---------------------------------------------------------------------- backup database d1 to bak1 with init --with init重写备份设备中内容 backup database d2 to bak1 with noinit --with noinit将内容追加到备份设备中 restore headeronly from bak1 ---------------------------------------------------------------------- 4、备份的方法 ---------------------------------------------------------------------- 1）完全备份 ------------------------------------------- （1）是备份的基准。在做备份时第一次备份都建议使用完全备份。（2）完全备份会备份数据库的所有数据文件、数据对象和数据。（3）会备份事务日志中任何未提交的事务。因为已提交的事务已经写入数据文件中。 -------------------------------------------- backup database d1 to bak1 with init --完全备份 backup database d1 to bak1 with noinit ---------------------------------------------------------------------- 2）差异备份 --------------------------------------------- （1）基于完全备份。（2）备份自最近一次完全备份以来的所有数据库改变。（3）恢复时，只应用最近一次完全备份和最新的差异备份。 ----------------------------------------------- backup database d2 to bak2 with init,name='d2_full' --差异备份，第一次备份时应做完全备份 create table b1(c1 int not null,c2 char(10) not null) backup database d2 to bak2 with differential,name='d2_diff1' insert b1 values(1,'a') backup database d2 to bak2 with differential,name='d2_diff2' insert b1 values(2,'b') backup database d2 to bak2 with differential,name='d2_diff3' insert b1 values(3,'c') backup database d2 to bak2 with differential,name='d2_diff4' restore headeronly from bak2 ---------------------------------------------------------------------- 3）事务日志备份 ------------------------------------------------------------- （1）基于完全备份。（2）为递增备份，即备份从上一次备份以来到备份时所写的事务日志。（3）允许恢复到故障时刻或者一个强制时间点。（4）恢复时，需要应用完全备份和完全备份后的每次日志备份。 ------------------------------------------------------------- backup database d3 to bak3 with init,name='d3_full' --日志备份，第一次备份时应做完全备份 create table b1(c1 int not null,c2 char(10) not null) backup log d3 to bak3 with insert b1 values(1,'a') backup log d3 to bak3 with insert b1 values(2,'b') backup log d3 to bak3 with insert b1 values(3,'c') backup log d3 to bak3 with restore headeronly from bak3 ---------------------------------------------------------------------- create table b1(c1 int not null,c2 char(10) not null) --Full+Log+Diff backup log d4 to bak4 with insert b1 values(1,'a') backup log d4 to bak4 with insert b1 values(2,'b') backup database d4 to bak4 with differential,name='d4_diff1' insert b1 values(3,'c') backup log d4 to bak4 with insert b1 values(4,'d') backup log d4 to bak4 with insert b1 values(5,'d') backup database d4 to bak4 with differential,name='d4_diff2' restore headeronly from bak4 ----------------------------------------------------------------------

SQL code

    日志清除 ----------------------------------------- 1）如果日志空间被填满，数据库将不能记录修改。 2）数据库在做完全备份时日志被截断。 3）如果将'Trans log on checkpoint'选项设为TRUE，则结果为不保存日志，即没有日志记录，不建议使用。 4）with truncate_only和with no_log设置日志满时清除日志 5）with no_truncate则可以完整保存日志，不清除，即使在数据文件已经损坏情况下。主要用于数据库出问题后在恢复前使用。可以将数据还原到出故障的那一时刻。 ------------------------------------------- exec sp_dboption d3 exec sp_dboption sp_dboption 'd3','trunc. log on chkpt.','true' --设置自动清除数据库日志 sp_dboption 'd3','trunc. log on chkpt.','false' --将自动清除数据库日志的选项去除 ---------------------------------------------------------------------- backup log d4 with truncate_only --设置D4日志满时清除日志，并做清除记录 ---------------------------------------------------------------------- backup log d4 with no_log --设置D4日志满时清除日志，但不做清除记录 ---------------------------------------------------------------------- backup log d4 to bak4 with no_truncate--在D4数据库损坏时马上备份当前数据库日志(DEMO) -------- 使用no_truncate 完全+修改1+差异+修改2+差异+修改3+停止SQL，删除数据库数据文件+重启SQL backup log no_truncate 再还原，可还原到修改3 ---------------------------------------------------------------------- 4）文件/文件组备份 ------------------------------------------------------------------ （1）用于超大型数据库。（2）只备份选定的文件或者文件组。（3）必须同时作日志备份。（4）还原时用文件/文件组备份和日志备份进行还原。（5）备份量少，恢复速度快。 ------------------------------------------------------------------ create database d5 on primary (name=d5_data1, filename='e:\data\d5\d5_data1.mdf', size=2MB), filegroup FG2 --创建数据库时创建filegroup FG2 (name=d5_data2, filename='e:\data\d5\d5_data2.ndf', --并将文件d5_data2放到FG2中 size=2Mb) log on (name=d5_log1, filename='e:\data\d5\d5_log1.ldf', size=2Mb) use d5 go alter database d5 add file (name=d5_data3, filename='e:\data\d5\d5_data5.ndf', size=2MB) to filegroup FG2 --将d5_data3加到文件组FG2中 alter database d5 add filegroup FG3 --增加文件组FG3 alter database d5 --将d5_data4加到文件组FG2中 add file (name=d5_data4, filename='e:\data\d5\d5_data4.ndf', size=2MB) to filegroup FG3 sp_helpdb d5 create table t1(c1 int not null,c2 char(10) not null) on [primary] --将不同表放到不同filegroup中 create table t2(c1 int not null,c2 char(10) not null) on FG2 create table t3(c1 int not null,c2 char(10) not null) on FG3 ---------------------------------------------------------------------- backup database d5 to bak5 with init,name='d5_full' --filegroup备份 backup database d5 filegroup='primary' to bak5 with backup log d5 to bak5 with backup database d5 filegroup='FG2' to bak5 with backup log d5 to bak5 with backup database d5 filegroup='FG3' to bak5 with backup log d5 to bak5 with ---------------------------------------------------------------------- backup database d5 to bak6 with init,name='d5_full' --file备份 backup database d5 file='d5_data1' to bak6 with backup log d5 to bak6 with backup database d5 file='d5_data2' to bak6 with backup log d5 to bak6 with backup database d5 file='d5_data3' to bak6 with backup log d5 to bak6 with backup database d5 file='d5_data4' to bak6 with backup log d5 to bak6 with restore headeronly from bak6 ====================================================================== SQL还原 ====================================================================== 1、验证备份 ------------------------------------------------------------ restore headeronly from bak3 restore filelistonly from bak3 with file=1 restore labelonly from bak3 restore verifyonly from bak3 ---------------------------------------------------------------------- 2、从备份中还原 ------------------------------------------------------------------------- restore headeronly from bak1 restore database d1 from bak1 with file=2 --从完全备份中恢复 ---------------------------------------------------------------------- restore headeronly from bak2 --从差异备份中恢复 restore database d2 from bak2 with file=1,norecovery restore database d2 from bak2 with file=5,recovery ---------------------------------------------------------------------- restore headeronly from bak3 --从日志备份中恢复 restore database d3 from bak3 with file=1,norecovery restore log d3 from bak3 with file=2,norecovery restore log d3 from bak3 with file=3,norecovery restore log d3 from bak3 with file=4,norecovery restore log d3 from bak3 with file=5,recovery ---------------------------------------------------------------------- restore database d3 from bak3 with file=1,norecovery --恢复到指定时间 restore log d3 from bak3 with file=2,norecovery restore log d3 from bak3 with file=3,norecovery restore log d3 from bak3 with file=4,recovery,stopat='2003-08-15 11:29:00.000' ---------------------------------------------------------------------- restore database d5 filegroup='FG2' from bak5 with file=4,norecovery --还原文件组备份 restore log d5 from bak5 with file=5,norecovery restore log d5 from bak5 with file=7,recovery ---------------------------------------------------------------------- restore headeronly from bak6 --还原文件备份 restore database d5 file='d5_data3' from bak6 with file=6,norecovery restore log d5 from bak6 with file=7,norecovery restore log d5 from bak6 with file=9,recovery ---------------------------------------------------------------------- restore database d5 from bak6 with replace --删除现有数据库，从备份中重建数据库 ---------------------------------------------------------------------- create database d6 --move to将数据库文件移动到新位置 on primary (name=d6_data, filename='E:\Program Files\Microsoft SQL Server\MSSQL\data\d6_Data.MDF', size=2MB) log on (name=d6_log, filename='E:\Program Files\Microsoft SQL Server\MSSQL\data\d6_log.ldf', size=2MB) go backupdatabase d6 to bak6 with init drop database d6 restore database d6 from bak6 with move 'd6_data' to 'e:\data\d6\d6_data.mdf', move 'd6_log'to 'e:\data\d6\d6_log.ldf' sp_helpdb d6 ---------------------------------------------------------------------- 3、分离与重连接数据库 -------------------------------------- sp_detach_db 'd6' sp_attach_db 'd6','e:\data\d6\d6_data.mdf','e:\data\d6\d6_log.ldf' -------------------------------------- sp_detach_db d6 go create database d6 on primary (filename='e:\data\d6\d6_data.mdf') for attach go ---------------------------------------------------------------------- 4、恢复损坏的系统数据库 ---------------------------------------------------------------------- 1）先备份MASTER、MSDB 2）停止SQL服务，将MASTER数据库文件删除或者重命名。这样，SQL服务将不能启动。 3）系统数据库的还原 ----------------------------------------------- （1）如果SQL服务还能启动，则从备份中恢复系统数据库。（2）如果SQL服务不能启动，则需要重建系统数据库。使用SQL文件夹TOOLS\BINN目录下的Rebuildm.exe重建master数据库。（3）创建备份设备，指向以前的备份设备。（4）以单用户模式启动SQL cd programe files\microsoft sql server\mssql\binn sqlservr.exe -c -m （5）进查询分析器，从备份中恢复master数据库。 restore database master from masterbak restore database msdb from disk='e:\bak\msdb.bak' MASTER还原后，SQL中用户数据库的信息也会恢复。（6）如果MASTER没有备份，则需要用sp_attach_db命令将用户数据库附加到新的MASTER数据库中。