sql server 异地备份与删除 (保留用于参考)

第一种方法

declare @sql varchar(500)
select @sql='\\192.168.0.6\G$\0.10Bak\CallCenter2013'+'_db_'+convert(varchar(10),getdate(),112) +'.bak'
exec master..xp_cmdshell 'net use \\192.168.0.6\G$\0.10Bak glcti /user:192.168.0.6\administrator'
backup database CallCenter2013 to disk=@sql   --生成备份文件
go

declare @sql varchar(500)
select @sql='del '+'\\192.168.0.6\G$\0.10Bak\CallCenter2013'+'_db_'+convert(varchar(10),dateadd(day,-7,getdate()),112) +'.bak'
exec master..xp_cmdshell @sql --删除7天前的备份，也就是只保留7个最新备份
go

注意：如果sql2005xp_cmdshell变错，要开启xp_cmdshell

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE

第二种写法不过没带删除功能

declare @strsql varchar(1000) 
declare @strdirname varchar(50) 
declare @strcmd varchar(50) 
declare @strsend varchar(1000) 
declare @strdate varchar(50) 
exec master..xp_cmdshell 'net use \\192.168.0.6\G$ glcti /user:192.168.0.6\administrator' 
set @strsql='backup database CallCenter2013 to disk=''\\192.168.0.6\G$\0.10Bak\' 
set @strdirname=replace(substring(convert(varchar(20),getdate(),120),1,10),'-','')+'12' 
set @strcmd='md \\192.168.0.6\G$\0.10Bak\' 
set @strcmd=@strcmd+@strdirname 
exec master..xp_cmdshell @strcmd 
print @strsql 
set @strsql=@strsql+@strdirname+'\CallCenter2013.bak'' with init,nounload,noskip,noformat' 
print @strsql 
exec (@strsql) 

下面是好几种方法，但都不带删除功能

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

第一种实现方法如下：(sql server 2000)
这是一个很常见的需求，网上也有相关的解决方法，主要是通过映射网络盘和执行cmdshell命令来实现！今天所说的实现方法稍有不同，思路来源于最近在深入研究的SQL Server服务帐号模式和安全机制，相关文章见Blog！本文对SQL Server 2000环境中具体实现方法做相关阐述，对于SQL Server 2005,实现办法相同! 
 
一：基本思路
　　 1：要实现异地备份，必须使用域用户帐号来启动SQL Server服务以及SQL Server Agent服务，因为本地系统帐户无法访问网络。
　　 2：在异地机器中建立一个与SQL Server服务器中启动SQL Server服务的域用户帐号同名帐号,且密码保持相同。在异地机器中建立一个共享文件夹，并设置合适权限。
　　注意：新建帐号针对的是工作组模式，如果是基于域模式，那就无须再建帐号
　    3：在SQL Server服务器中建立异地备份的维护计划，在“完全备份”和“事务日志备份”中，使用输入异地共享文件夹的UNC路径(\\共享机器IP\共享文件夹)。
　　4：配置好维护计划中其它设置。
　　上面表述可能不太直观，下面以实际实验来演示如何做异地备份。
二：异地备份的演示
       1：在SQL Server服务器中以域用户帐户启动SQL Server服务以及SQL Server Agent服务。(管理工具->服务->登陆中设置)
       本实验中直接使用administrator帐户，可以根据实际环境切换其它域用户帐户。(登陆身份：此帐户 中设置)
       2：在文件备份服务器中建立一个共享文件夹，共享权限中删除everyone用户，加入administrator用户，权限设置(可读，可写)。
       3：在SQL Server服务器中，打开企业管理器，新建数据库维护计划： 
　　4：在维护计划向导中，选择计划备份的数据库，设置好数据库优化信息和检查完整性等步骤。
　　5：在向导的“指定数据库备份计划”设置好调度计划。
　　6：在“指定备份磁盘目录”界面，手动输入文件备份服务器共享文件夹UNC路径。
       7：在“事务日志备份计划”界面，根据实际环境决定如何备份。
       8：按向导设置好其它步骤，完成异地备份维护计划。
 
三：测试异地备份是否成功。
         1：检查共享文件夹中备份文件是否存在。
         2：如果没有备份成功，请查看SQL Server日志，并检查权限设置以及用户名和密码是否完全一致。
------------
-------------
--------------
第二种实现方法如下：
-------------------------
SQL Server 2005自动异机备份
本人在网络上查了一些资料之后终于以很傻的方式解决了SQL 2005的异机备份问题啦，现在俺就给大伙抽
两句：
SQL Server 2005数据库系统支持三种备份方式：完整备份、差异备份和日志备份。
  本人目前的实现方式是：第一：每周1次完全备份，备份时
间为每周6的0点整，并且将本次的完整备份文件传送到另外的文件服务器上。第二：每天1次差异备份，
备份时间定为晚上的0点整，并且将此备份传送到文件服务器上。
自动备份实施方案：
   关于数据库的备份，SQL Sever 2005提供了可视化向导和利用Backup语句两种备份方式。下面就两种
备份方式实施上面的备份策略。
可视化向导创建备份：
SQL Sever 2005可以通过“维护计划向导”来创建维护计划，实现自动化备份数据库。具体步骤如下：
（1）在“管理”--“维护计划”上右键弹出菜单，选“维护计划向导”，设置“维护计划的名称”，在
里面填写计划的名称（自己自定义哦）。设置代理执行维护计划账户和口令
（2）选择维护计划类型，比如数据库备份（完整备份），定义维护计划任务，在这里需要为备份文件选
择适当的位置和文件名（比如：TEST.BAK）,并在“如果备份文件存在（*）”的备选项改为“覆盖”。
（3）设置计划执行周期。设置为每周六的0点，并保存好维护计划操作报告。
（4）确定--完成。
这样就完成了数据库的完整备份，按照上面的步骤，设置数据库的差异备份和日志备份。完成后先收工执
行测试。
也可用Backup语句创建备份
用Backup语句可以备份整个数据库、事物日志，备份这些对象的语法较复杂些，但是 都大同小异：
完整备份：
GO
BACKUP DATABASE TEST(数据库的名称哦) TO DISK = 'E:\BACKUP\TEST.BAK(备份文件哦)'  \这个是指定
备份的位置的哦
WITH INIT    '这里的INIT 表示的是覆盖现有的备份集哦
GO
这样之后添加计划，设置执行的周期（每周一次）和时间（晚上0点哦）等。
然后确定--完成。
同理可以用上面的方式来实现差异备份和日志备份，并根据备份的策略来设置执行的周期和时间，他们的
语句分别是：
差异备份：
USE TEST
GO 
BACKUP DATABASE TEST 
TO DISK = 'E:\BACKUP\TEST.BAK'   \指定备份的位置
WITH DEFFERENTIAL  \指定备份的类型为差异备份哦
GO
日志备份
BACKUP LOG TEST
TO DISK = 'E:\BACKUP\LOG.LDF'   \指定备份的位置
GO
异机存放备份文件
  处于安全考虑，即使在本机上做了备份之后，仍存在机器本身意味事件而导致备份文件的损坏或者丢失


的可能，所以在其他的机器上也存放一份备份文件  方为更安全，这个就利用WINDOWS的任务计划和一个


copy批处理文件即可实现。可以利用下面的两种方式来完成异机存放的目的（本人两种都试过，其中第一


种为方便哦）：
（1）以域服务器的方式：
把备份集加入的SQL Sever 集成系统服务器中后，以域账户登录，按照下面的步骤即可实现备份文件的自


动传送：


1.在备份机上新建一个.BAT文件，编辑如下内容保存：
copy \\192.168.1.100\E$\BACKUP\TEST.BAK E:\BAK\


说明：192.168.1.100就是集成系统服务器的IP地址，E$\BACKUP\TEST.BAK是集成系统数据库的完整备份


，E:\BAK\是完整备份文件传送过来的保存位置。
2.在备份机上添加任务计划，找到刚才新建的批处理文件，周期改为“每
周”，把时间也改掉，输入域用户名和密码。
3.确定---完成。这样就可以实现对数据库完整备份的异机存放目的。




（2）除了用域用户登录的方式，还可以用NET命令把备份的文件COPY到需要放入的目录下，但是这种情况


的话要保证备份机的IPC$要开启，并且两台机器的用户和密码一致（主要是要保证重启机器后不用再重新


映射哦）。
具体步骤：
1. 在集成系统服务器上，开始---运行--输入CMD，进入命令行窗口。
2.在命令行窗口下输入如下命令：
Net use z:\\192.168.1.100\e$ '123456' /user:administrator
说明：192.168.1.100是备份机的IP地址，123456为密码，administrator是他的用户名，上面语句的意思


就是把备份机的E盘映射到本地的Z盘。
3.在集成系统服务器上建立.bat文件来实现文件的传递，编辑如下内容并保存为自定义文件名.BAT
copy E:\backup\test.bak z:\ 自定义文件名.BAT
4.添加任务计划，找到刚才定义的.bat文件，周期改为“每周”，把“起始时间”改为晚上的0点，日期


改为“星期六”，输入机器的用户名和密码。
5.确定---完成。就可以吧完全备份文件test.bak存放到备份的机器上啦。
  同样，利用上面的3、4、5步完成差异文件的自动传送。
如果用net的方式的话，必须开放139和445端口，对服务器的安全性带来一定的危险
------------
-------------
---------------
第三种实现方法：(sql server 2005)
---------------------------------
实例说明：
环境：win2k+sqlserver 2K+查询分析器
SQLSERVER服务实例名称:mainserver
需要备份的数据库名称: msdb
本地机器名称（Client端）：david
本地用户:zf 密码：123
本地域名：domain
本地提供备份需求的文件夹:e:/test
第一步: 建立共享文件夹
在程序代码中调用（或者CMD窗口）　net share test=e:/test
或者用NetShareAdd这个API
简要说明：
net share　　 : 是Windows内部的网络命令。
作用：建立本地的共享资源，显示当前计算机的共享资源信息。
语法：参见 net share /?
第二步: 建立共享信用关系
master..xp_cmdshell 'net use　//david/test 123 /user:domain/zf'
简要说明：
1：xp_cmdshell ：是SQLSERVER的扩展存储过程。
作用，以操作系统命令行解释器的方式执行给定的命令字符串，
并以文本行方式返回任何输出。
语法：参见SQLSERVER联机帮助
2:net use　　　: 是Windows内部的网络命令。 
作用，将计算机与共享资源连接或断开，或者显示关于计算机
连接的信息。该命令还控制持久网络连接。
语法：参见 net use /?
第三步:备份数据库
backup database msdb to disk='//david/test/msdb.bak'
这个不需要说明吧，语法参见SQLSERVER联机帮助
第四步: 删除共享文件夹
在程序代码中调用（或者CMD窗口）　net share test /delete
或者用NetShareDel这个API
结果:
已处理 1376 页，这些页属于数据库 'msdb' 的文件 'MSDBData'（位于文件 1 上）。
已处理 1 页，这些页属于数据库 'msdb' 的文件 'MSDBLog'（位于文件 1 上）。
BACKUP DATABASE 操作成功地处理了 1377 页，花费了 3.653 秒（3.086 MB/秒）。
这样mainserver服务器上的msdb就备份到了david机器的E:/test/msdb.bak文件了，使用起来很简单吧？恢复数据库操作也是一样，只要将第三个步骤的语句改为'restore database msdb from disk='//david/test/msdb.bak'就可以啦。。你看完了也可以试试呀？！（最简单的测试工具查询分析器+CMD窗口） 
　　备注：xp_cmdshell 这个扩展存储过程只能SA级别的用户调用，而且是SQLSERVER的安全隐患之一，许多DBA都喜欢将其删除或者禁用，所以开发人员使用时要倍加小心哦。
---------------
------------------
-------------------
第四种实现方法：
----------------------
这里提供两种方法:
1,写成存储过程，建立作业定时备份
--在sql中映射一下 
exec master..xp_cmdshell 'net use z: \\computer_name\D$ "密码" /user:computer_name\administrator' 
/*--说明: 
z:是映射网络路径对应本机的盘符,与下面的备份对应 
\\computer_name\D$是要映射的网络路径 
computer_name是远程的计算机名, 
administrator是登陆的用户名 
密码 指定的administrator用户的密码 
--*/ 
--备份；with init覆盖 ¦noinit添加 
backup database 库名 to disk='E:\H_BACKUP.bak' with init 
--COPY 
exec master..xp_cmdshell 'copy E:\H_BACKUP.bak z:' 
--删除（这句可以去掉） 
--exec master..xp_cmdshell 'del E:\H_BACKUP.bak' 
--完成后删除映射 
exec master..xp_cmdshell 'net use z: /delete'
2,调度
企业管理器 --管理 --SQL Server代理 --右键作业 --新建作业 --"常规"项中输入作业名称 --"步骤"项 --新建 --"步骤名"中输入步骤名 --"类型"中选择"Transact-SQL 脚本(TSQL)" --"数据库"选择执行命令的数据库 --"命令"中输入要执行的语句: 
declare @strsql varchar(1000) 
declare @strdirname varchar(50) 
declare @strcmd varchar(50) 
declare @strsend varchar(1000) 
declare @strdate varchar(50) 
exec master..xp_cmdshell 'net use \\192.168.0.151\d$ Password /user:192.168.0.151\administrator' 
set @strsql='backup database new_his to disk=''\\192.168.0.151\d$\serverd\' 
set @strdirname=replace(substring(convert(varchar(20),getdate(),120),1,10),'-','')+'12' 
set @strcmd='md \\192.168.0.151\d$\serverd\' s
et @strcmd=@strcmd+@strdirname 
exec master..xp_cmdshell @strcmd 
--print @strsql 
set @strsql=@strsql+@strdirname+'\new_hisbackup.dat'' with init,nounload,noskip,noformat' -
-print @strsql 
exec (@strsql) 
----其中写的IP地址及共享目录，网友自行修改,Password一定要正确 --确定 --"调度"项 --新建调度 --"名称"中输入调度名称 --"调度类型"中选择你的作业执行安排 --如果选择"反复出现" --点"更改"来设置你的时间安排 然后将SQL Agent服务启动,并设置为自动启动,否则你的作业不会被执行 设置方法: 我的电脑--控制面板--管理工具--服务--右键 SQLSERVERAGENT--属性--启动类型--选择"自动启动"--确定.
-------------
--------------
---------------
第五种方法：
--------------------
问题： 
一个客户的sql server 2k数据库想备份到另外一台服务器上，SQL Server 中可以将sql server及代理服务 的服务帐户设置为域用户，就可以在备份时将备份文件写入指定的网络路径下。但用户在使用域用户运行SQL Server时，反映系统运行不稳定，还是用系统帐户，这样网络备份就无法实现了。 
方案: 
以前有同事试验用在本机指定网络驱动器，然后将备份文件写入网络驱动器，实现了异机备份。 
环境说明: 
硬件环境：数据库服务器 192.168.1.5；备份服务器 192.168.1.3 
操作系统：都是Win 2k ad +sp4 
其他：备份服务器 192.168.1.3，有c：；d：；E：三个硬盘 
具体做法: 
1、在备份服务器的设置备份目录及共享该目录 
1.1 备份服务器上创建了一个本地用户：DBBak ，密码：12345 
1.2 在备份服务器的E：下创建一个目录backup ,下有子目录 \complete ;\log;\dif 
1.3 共享 backup目录，共享名为backup，权限只有用户DBBak可以完全控制，everyone只读 
2、在数据库服务器上 
设定一个备份任务，备份的脚本如下： 
/******** 
--任务执行的脚本 
********/ 
/* --在数据库服务器上将\\192.168.1.3\backup\ 映射为本地驱动器 Y： 
具体命令见window帮助： 
语法: 
net use [{DeviceName | *}] [\\ComputerName\ShareName[\volume]] [{Password | *}]] [/user:[DomainName\]UserName] [/user:[DottedDomainName\]UserName] [/user:[UserName@DottedDomainName] [/savecred] [/smartcard] [{/delete | /persistent:{yes | no}}] 
net use [DeviceName [/home[{Password | *}] [/delete:{yes | no}]] 
net use [/persistent:{yes | no}] 
*/ 
Exec Xp_Cmdshell 'net use Y: \\192.168.1.3\backup 12345 /user:192.168.1.3\DBBak' 
备份方法有两种： 
一、直接备份在本地硬盘，然后将备份文件压缩，然后将压缩后的文件直接移动到备份服务器上,适用于本地硬盘空间充裕的情况下 
--备份完成后，将备份文件压缩，压缩完成后，删除原文件 
C:\Progra~1\Winrar\Rar A -Df -Ep Log2006.rar Y:\log\*.* 
若在sql中执行见： 
declare @sql varchar(100) 
declare @Filepath varchar(50) 
set @filePath ='D\backup\test' 
backup database pubs to disk =@filePath+'pubs.bak' 
Set @Sql = 'C:\Progra~1\Winrar\Rar A -Df -Ep ' + @Filepath + ' ' + @Filepath + '\*.*' 
Exec Xp_Cmdshell @Sql,No_Output 
Set @Sql = 'rd /S /Q ' + @Filepath 
Exec Xp_Cmdshell @Sql 
Exec Xp_Cmdshell net use Y: \\192.168.1.3\backup 12345 /user:192.168.1.3\DBBak'' 
Exec Xp_Cmdshell 'Move /Y d:\dbbak\*.rar Y:\' 
Exec Xp_Cmdshell 'Net Use z: /delete' 
二、直接将备份文件放到备份服务器上 
--执行如下存储过程，以备份开始日期为目录，将数据库日志的备份文件放到备份服务器上，如：\\192.168.1.3\e$\backup\servername\log\200601091200\ *.trn 
exec s_Sys_Backup 'Y:\log','log',2 
------------
-------------
-----------------
第六种方法：
---------------------------
SQL Server的异地联机备份
我要投稿作者：佚名    技术文章来源：网络    点击数：    更新时间：2011-11-24          ★★★【字体：小 大】
-
SQL Server 数据库远程备份


一、       建立备份作业
备份A服务器上的SQL Server数据库databasex 到B服务器的共享目录databack中。
步骤如下：
--SQL Server代理
--右键作业
--新建作业
--"常规"项中输入作业名称
--"步骤"项
--新建
--"步骤名"中输入步骤名
--"类型"中选择"Transact-SQL 脚本(TSQL)"
--"数据库"选择执行命令的数据库databasex--"命令"中输入要执行的语句:
declare @strsql varchar(1000)
declare @strfilename varchar(50)
declare @strcmd varchar(50)
declare @strsend varchar(1000)
declare @strdate varchar(50)
exec master..xp_cmdshell 'net use Y: \\B\databack "密码字符串" /user: B\用户名'
set @strsql='backup database databasex to disk=''Y:\'
set @strfilename=databasex+replace(substring(convert(varchar(20),getdate(),120),1,10),'-','')+'.bak'
--print @strsql
set @strsql=@strsql+@strfilename+''' with init'
--print @strsql
exec (@strsql)
--完成后删除映射
exec master..xp_cmdshell 'net use Y: /delete'
二、许可xp_cmdshell
 SQL Server 2005及以上版本中引入的xp_cmdshell 选项是服务器配置选项，使系统管理员能够控制是否可以在系统上执行xp_cmdshell 扩展存储过程。默认情况下，xp_cmdshell 选项在新安装的软件上处于禁用状态，但是可以使用基于策略的管理或运行sp_configure 系统存储过程来启用它，如下面的代码示例所示：
 复制代码
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
----------
-------------
----------------