随笔 - 55 文章 - 0 评论 - 10

2009年3月15日

SQL Server 2005异地备份 - 转-已验证

本方案采用备份至本地然后copy到文件服务器的方法。

SQL server 2005打了sp2的补丁后好像存储过程xp_cmdshell是不能直接用的

显示高级选项（仅需执行一次）

EXEC sp_configure 'show advanced options', 1

RECONFIGURE

GO*

允许执行xp_cmdshell

EXEC sp_configure 'xp_cmdshell', 1

RECONFIGURE

添加映射驱动器

declare @string nvarchar(200)

set @string = 'net use z: \\192.168.1.2\D$\db_backup "123456" /user:fileserver\administrator'

exec master..xp_cmdshell @string

其中192.168.1.2为文件服务器的地址，db_backup为该服务器的共享文件夹，fileserver为机器名，administrator 123456 分别为共享时设置的用户名密码。

备份数据库至本地

declare @date datetime

set @date = GetDate()

declare @str nvarchar(100)

set @str = 'd:\mydb'+ convert(nvarchar(12), @date, 112) +'.bak'

backup database mydb to disk=@str with init

With init为覆盖同名文件（本例设计为1天执行一次，不会出现覆盖的情况）。

拷贝到文件服务器

declare @str1 nvarchar(100)

set @str1 = 'copy '+ @str +' z:'

exec master..xp_cmdshell @str1

删除映射以及本地备份

exec master..xp_cmdshell 'net use z: /delete'

declare @str2 nvarchar(100)

set @str2 = 'del '+@str+''

exec master..xp_cmdshell @str2

7关闭允许执行cmdshell

EXEC sp_configure 'xp_cmdshell', 0

RECONFIGURE

建立sql server 作业执行步骤2-7，成功备份

posted @ 2009-03-15 15:57 大鱼阅读(380) 评论(0) 编辑

SQL Server 2005 镜像功能实现 - 转 -已验证

主机、镜像分别MASTER KEY。

主机执行：

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

CREATE CERTIFICATE HOST_A_cert WITH SUBJECT = 'HOST_A certificate' ,

START_DATE = '01/01/2008';

镜像执行：

USE master;

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

CREATE CERTIFICATE HOST_B_cert WITH SUBJECT = 'HOST_B certificate',

START_DATE = '01/01/2008';

这个MASTER KEY对于每个sql server实例唯一的，如果数据库中已经创建了MASTER KEY，可以用如下的sql删除之后重新执行：

DROP MASTER KEY

主机、镜像分别创建EndPoint

主机执行：

CREATE ENDPOINT Endpoint_Mirroring

STATE = STARTED

TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )

FOR

DATABASE_MIRRORING

( AUTHENTICATION = CERTIFICATE HOST_A_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );

镜像执行：

CREATE ENDPOINT Endpoint_Mirroring

STATE = STARTED

TCP ( LISTENER_PORT=5022 , LISTENER_IP = ALL )

FOR

DATABASE_MIRRORING

( AUTHENTICATION = CERTIFICATE HOST_B_cert , ENCRYPTION = REQUIRED ALGORITHM AES , ROLE = ALL );

EndPoint 貌似也是唯一的，如果已经提示错误如没有权限之类的，可以先删除创建过的EndPoint（如果你用图形界面创建过镜像则ENDPOINT的名称默认为"镜像"）

DROP ENDPOINT 镜像

备份证书，将主机和镜像的证书互换

主机执行

BACKUP CERTIFICATE HOST_A_cert TO FILE = 'D:\HOST_A_cert.cer';

镜像执行

BACKUP CERTIFICATE HOST_B_cert TO FILE = 'D:\HOST_B_cert.cer';

将备份好的证书文件相互copy

同步login

主机执行

CREATE LOGIN HOST_B_login WITH PASSWORD = 'password';

CREATE USER HOST_B_user FOR LOGIN HOST_B_login;

CREATE CERTIFICATE HOST_B_cert AUTHORIZATION HOST_B_user FROM FILE = 'D:\HOST_B_cert.cer';

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_B_login];

镜像执行

CREATE LOGIN HOST_A_login WITH PASSWORD = 'password';

CREATE USER HOST_A_user FOR LOGIN HOST_A_login;

CREATE CERTIFICATE HOST_A_cert AUTHORIZATION HOST_A_user FROM FILE = 'D:\HOST_A_cert.cer';

GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [HOST_A_login];

经过以上步骤，两个sql server 实例的准备工作就差不多了，加入我们要同步的数据库为billing。
1. 首先将主机上的billing数据库完整备份，在镜像机上创建同名数据库，用主机的备份还原（选择覆盖现有数据库以及RESTOR WITH NORECOVERY），还原后数据库的状态为"正在还原"。
2. 将主机的billing数据库的事务日志备份，同样在镜像机上还原。
下面我们就要开始执行镜像的同步了，

镜像执行

ALTER DATABASE Billing SET PARTNER = 'TCP://主机IP:5022';

主机执行

ALTER DATABASE Billing SET PARTNER = 'TCP://镜像IP:5022';

如遇错误，首先保证防火墙等是否将相关端口屏蔽，sql server是否允许远程连接等，如果还是没有解决问题，如提示'TCP://镜像IP:5022'无法访问，

那么————————————"重启"！

完成后主机数据库显示"主题-已同步"，镜像显示"镜像-已同步/正在还原"。

也许你还会用到如下语句：

关闭镜像

ALTER DATABASE Billing SET PARTNER OFF

在主机执行一下sql切换主机和镜像

USE MASTER

ALTER DATABASE Billing SET PARTNER FAILOVER

posted @ 2009-03-15 15:54 大鱼阅读(172) 评论(0) 编辑

本方案采用备份至本地然后copy到文件服务器的方法。

公告