sqlserver2005创建热备份

 

6.25

上个星期公司需要将两台服务器做成热备份，此任务交与我，但是本人早就已经把数据库的知识还给大学老师了，于是找资料好好研究了一番，通过几次实验，终于成功。以下大部分资料参考于网络。

 

一、检查SQL Server 2005 数据库

use master;

alter database <databasename>

set recover full

如果不用sql语言的话，只需右击需要备份的数据库，属性，将其中一项恢复选成“完整”即可。

 

二、主备实例互通

1、创建证书

--主体服务器执行；

use master;

creat master key encryption by password='zhouhang';--创建主密钥

creat certifiate HOST_02_cert with subject='HOST_A certificate',  --创建证书名为‘HOST_02_cert’（此名字可任意改变）

start_date='06/22/2011',

expiry_date='07/07/2030'----证书有效期06/22/2011-07/07/2030，可自定义

 

--镜像服务执行；

use master;

create master key encrypion by password='zhouhang';--创建主密钥

create certificate HOST_03_cert wiht subject='HOST_B certificate',

start_date='06/22/2011',

expiry_date='07/07/2030';

 

2.创建连接的端点

--主体服务器执行；

create endpoint endpoint_mirroring--创建端点，名称为endpoint_mirroring（可自定义）

state=started   --激活端点

as

tcp(listener_port=5022,listener_ip=all)--端点号，创建前先检查此端点是否被占用

for

database_mirroring

(authentication=certificate HOST_02_cert,encryption=required 

algorithm rc4,role=all );

 

 

--镜像服务器执行；

create endpoint endpoint_mirroring

state=started

as

tcp(listener_port=5022,listener_ip=all)

for

database_mirroring

(authentication=certificate HOST_03_cert,encryption=required

algorithm rc4,role=all);

 

3.备份证书以备建立互联

--主体服务器执行；

backup certificate HOST_02_cert to file='d:/sqlbackup/HOST_02_cert.cer'

 

--镜像服务器执行；

backup certificate HOST_03_cert to file='d:/sqlbackup/HOST_03_cert.cer'

 

 

4.互换证书

主体和备用服务器创建的证书互相复制

 

5.添加登陆名、用户

--主体服务器执行

create login HOST_03_login wiht password='zhouhang';

create user HOST_03_user for HOST_03_login;

create certificate HOST_03_cert authorization HOST_03_user

from file='d:/sqlbackup/HOST_03_cert.cer';

grant connect on endpoint::endpoint_mirroring to [HOST_03_login];

 

--备用服务器执行

create login HOST_02_login wiht password='zhouhang';

create user HOST_02_user for HOST_02_login;

create certificate HOST_02_cert authorization HOST_02_user

from file='d:/sqlbackup/HOST_02_cert.cer';

grant connect on endpoint::endpoint_mirroring to [HOST_02_login];

 

 

三、建立镜像关系

1、准备备机数据库

还原数据的时候需要使用选上“with nonrecover”

 

2、 手工同步登录名和密码

在第一章中提到数据库镜像的缺点之一是无法维护登录名，所以需要我们手工维护登录。

通常来说数据库都将会有若干个用户作为访问数据库的用户，并且数据库会有相应的登录名，但是在备机中缺少与之相对应的登录名，例如某业务系统使用’myuser’作为登录名访问数据库，但是在备机中没有’myuser’这个登录名，因此一旦主备切换，业务系统就无法登录数据库了，这种情况称为"孤立用户"

在主数据库中执行如下语句：

USE master;

select sid,name from syslogins;

查找出相应的用户名和sid，例如：上述的’myuser’

在备数据库中执行如下语句：

USE master;

exec sp_addlogin 

@loginame = '<LoginName>', 

@passwd = '<Password>', 

@sid = <sid> ;

这里的’LoginName’即主数据库中的登录名，sid即是上述通过SQL语句查找出的sid。

例如,查询得到的sid和name如下所示。

sid name

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

0x074477739DCA0E499C29394FFFC4ADE4 cz_account

 

则建立登录名的SQL语句：

USE master;

exec sp_addlogin 

@loginame = 'cz_account', 

@passwd = 'password', 

@sid = 0x074477739DCA0E499C29394FFFC4ADE4;

 

到此为止可以认为备机数据库的环境已经与主机同步了，还差数据库内的数据未同步。

 

2、建立镜像

--主体服务器执行；

alter database shishan set parter='tcp://192.168.1.12:5022'--备份服务器的IP地址

 

--如果主体执行不成功，尝试在备机中执行如下语句；

alter database shishan set partner='tcp://192.168.1.13:5022'--主体服务器的IP地址

 

 

四、测试操作

1、主备互换

--主机执行：

use master;

alter database <DatabaseName> set partner failover;

 

2、主服务器Down掉,备机紧急启动并且开始服务

--备机执行：

use master;

alter database <DatabaseName> set partner force_service_all_data_loss;

 

3、原来的主服务器恢复,可以继续工作,需要重新设定镜像

--备机执行：

use master;

alter database <DatabaseName> set partner resume; --恢复镜像

alter database <DatabaseName> set partner failover; --切换主备

 

4、原来的主服务器恢复,可以继续工作

--默认情况下，事务安全级别的设置为 FULL，即同步运行模式，而且SQL Server 2005 标准版只支持同步模式。

--关闭事务安全可将会话切换到异步运行模式，该模式可使性能达到最佳。

USE master;

ALTER DATABASE <DatabaseName> SET PARTNER SAFETY FULL; --事务安全,同步模式