SQL数据库异地备份

方法一：做“数据库维护计划”

1.本地服务器（计算机管理里）建立一个操作系统用户，例如sa（用户名和密码需要跟远程计算机所建立的用户情况相同），赋予此用户administrators组权限，加上相应的密码。

2.在企业管理器→右键点击(local)(windows nt)选择属性→在“安全性”选项里的“启动服务帐户”选择使用“本帐号”，在里面填入第一步建立的用户和密码（sa）。（这里的功能是：用这个跟远程计算机用户名密码一样的用户来启动本地sqlserver，就像做了net use）

3.待sa用户重启了sqlserver服务后，在数据库维护计划里的“指定备份磁盘目录”这一步手工输入远程计算机地址及共享文件夹名\\10.2.0.12\backup（10.2.0.12为远程计算机的ip，backup为远程计算机开放的共享文件夹名）

4.更改本地服务器时间，测试异地备份效果

SQL Server数据库异地备份的实现

将一台服务器A上的一个数据库data备份自动备份到服务器B的目录

1.机器A,B创建一个同名的windows用户,用户组设置为administrators,并设置相同的密码,做为备份文件夹文件夹的有效访问用户,操作:

我的电脑》控制面板》管理工具》计算机管理》用户和组》右键用户》新建用户

--建立一个隶属于administrator组的登陆windows的用户

2.在B机器器上,新建一个共享目录,做为备份文件的存放目录,操作:

我的电脑--D:\ 新建一个目录,名为: BAK

--右键这个新建的目录

--属性--共享

--选择"共享该文件夹"

--通过"权限"按纽来设置具体的用户权限,保证第一步中创建的用户具有对该文件夹的所有权限

--确定

3.设置 MSSQLSERVER 及 SQLSERVERAGENT 服务的启动用户

开始--程序--管理工具--服务

--右键 MSSQLSERVER

--属性--登陆--选择"此账户"

--输入或者选择第一步中创建的windows登录用户名

--"密码"中输入该用户的密码

--确定

--同样的方法设置 SQLSERVERAGENT

4.在A机器上完成对B机器BAK目录的映射

5.查询分析器中执行下面的语句,检验是否成功:

   exec master..xp_cmdshell 'dir 映射的盘符'

6.A服务器上做备份计划

 

SQL异地备份失败，归根结底是权限问题

如：

    SQLServer备到FileServer上

    那你的SQLServer的启动用户必须在FileServer上有足够的权限!

--------------------^^^---------------------------------

1、SQLServer上新建一SQLUser用户权限大一点。

2、FileServer上建同一用户对某一文件夹有足够权限。

3、两机的SQLUser密码相同(方便一点)

4、将SQLServer改为SQLUser启动

    (管理工具-->服务-->mssql项-->属性-->指定用户及密码)

5、\\ip\文件夹\ShareBak.Bak就可以了

通过A实现远程对B计算机数据的备份

前提

1.有权限访问远程计算机

2.本地服务器的sqlserver agent服务保证处于启动状态

新建维护计划向导--选择目标服务器--服务器（选择要远程备份的机子），下一步--备份数据库（完整）--选择要备份的数据库和备份位置，建立执行计划时间--OK，

A备份到B（不同计算机之间的异地备份）

前提：

1 本地服务器的sqlserver agent服务保证处于启动状态

2.远程计算机（计算机管理里）建立一个普通权限用户，并为其加上密码（例如用户名为sa，密码为1234），同时在那台机器的非系统盘里建一个名为backup的共享文件夹，为了安全另外设置这个文件夹只有这个sa用户可以访问（需要ntfs格式才可以设置安全）

注：必须保证目标文件夹在用户账号下有可写权限

方法二：新建一个作业，如下（此方法已经通过局域网测试）

declare @sql varchar(500)

select @sql='\\10.2.0.12\backup\dbname'+'_db_'+convert(varchar(10),getdate(),112) +

substring(convert(varchar(10),getdate(),108),1,2) +'.bak'

exec master..xp_cmdshell 'net use \\10.2.0.12\backup 1234 /user:remotehost\sa'

backup database dbname to disk=@sql

--备份数据库，这里的10.2.0.12为远程计算机的ip，backup为共享文件夹名，dbname为本地sqlserver服务器要备份的数据库，remotehost为远程计算机的计算机名，1234为密码,sa用户名，请相应替换成实际环境的数据

go

declare @sql varchar(500)

select @sql='del '+'\\10.2.0.12\backup\dbname'+'_db_'+convert(varchar(10),dateadd(day,-7,getdate()),112) +

substring(convert(varchar(10),dateadd(day,-7,getdate()),108),1,2) +'.bak'

exec master..xp_cmdshell @sql

go

--删除7天前的备份，也就是只保留7个最新备份

以上步骤操作完后，在查询分析器里执行如果出现

SQL Server 阻止了对组件 'xp_cmdshell' 的 过程'sys.xp_cmdshell' 的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 'xp_cmdshell'。有关启用 'xp_cmdshell' 的详细信息，请参阅 SQL Server 联机丛书中的 "外围应用配置器"错误。

请先执行以下脚本，然后再执行作业文件：

sp_configure 'show advanced options',1
reconfigure
go
sp_configure 'xp_cmdshell',1
reconfigure
go

此脚本执行成功提示为：

配置选项 'show advanced options' 已从 1 更改为 1。请运行 RECONFIGURE 语句进行安装。
配置选项 'xp_cmdshell' 已从 1 更改为 1。请运行 RECONFIGURE 语句进行安装。

如果以上全部执行成功，则新建一个作业，指定执行计划，已达到自动异地备份效果。

新建作业具体步骤：SQL SERVER代理--作业--新建作业(常规输入作业名称)--点击步骤---新建一个步骤（输入名称，选择要备份的数据库，然后把以上执行备份代码放到命令框里）--确定--点击计划--新建计划（指定备份的时间，周期等等）---确定---OK--执行计划（如果执行成功--OK ，否则，检查代码）