如何实现SQL2005 远程备份

　　要实现 SQL 远程备份,必须处理好本机远程访问的问题：

　　第一、远程计算机与本机最好在同一网段内，如果跨了网段(或VLAN)，需交换机路由支持，甚至修改本地路由表。

　　第二、为本机提供 SQL 服务的“用户名”和“密码”必须是[远程计算机]的[合法用户]，且能对[远程计算机]的[备份文件夹]进行[完全操作]。

　　一般情况下，在安装 SQL 服务时使用的是系统帐户(多半是 administrator)，如果刚好是远程计算机的合法用户，则处理起来就简单多了。

　　否则，在进行远程备份时必须使用 xp_cmdshell 调用 MS-DOS 的 net use 命令，使用对方的[合法用户]进行登记，以操作[备份文件夹]。

　　第三、至于[远程计算机]的[备份文件夹]，可以事先在[远程计算机]上将[备份文件夹]进行共享，并指定可完全访问的用户名。

　　如果不想让无关的人看到这个[备份文件夹]，可在共享时将共享名后加上$，例如：备份文件夹$

　　或者，在使用 net use 进行[合法用户]登记时，直接使用对方超级用户，这样可直接访问[备份文件夹]。

　　例如：\COMPUTERE$备份文件夹

　　下面是我写的数据库备份脚本，仅用于 SQL2005 环境。

　　[主要功能]

　　在无法利用[差异备份]+[完整备份]不过多影响业务数据库性能的前提下，提高[完整备份]的频率，将[完整备份]进行多处异地存放，

　　如有必要可进行压缩以减小体积，同时删除过期备份，最后对整个操作的过程进行记录，生成日志文件。

　　[注意事项]

　　1、数据库备份前的[一致性检查]需要使用 SQL2000 的 isql.exe ，数据压缩时使用 7-Zip ,这两个文件必须事先指定路径，详见脚本内的说明。

　　2、异地备份的命名格式为：备份文件夹This_Backup数据库名+_BackUp_+日期+时间.bak 。This_Backup 可在 {组合 @备份路径名称} 小节处修改。

　　3、脚本中没有对异地备份的路径进行校验，自己修改时请先用 net use 进行测试。

　　[简单参考]

　　SQL 2005 下开启 xp_cmdshell的办法 [EXEC sp_configure 'xp_cmdshell', 0 ----关闭]

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

　　net use 的使用

　　“开始菜单”->“运行”-> 输入 cmd 进入 MS-DOS 窗口，输入以下命令并回车：(“用户名”和“密码”之间有一空格，注意大小写!)

　　net use \COMPUTER /user:用户名 密码

　　这个操作可以在本机以 /user: 后指定的身份访问 \COMPUTER [远程计算机]

　　*如果指定的身份是[远程计算机]上的超级用户，则可以通过 \COMPUTERC$ 直接访问对方的 C: 盘

　　**这个异名登记注册的过程可在本机开机后执行一次，并长期有效，直到重启为止。(在 MS-DOS 和 SQL 中运行的效果是一样。)

　　[生成日志]

　　在控制台配置 SQL 计划任务时，在“步骤”“高级”选项，选择并指定“输出文件”，方式为“追加”。可记录整个操作过程。

　　[定时运行]

　　下面的脚本定义了8个异地备份路径，所以，可每3小时运行机制一次，在控制台配置 SQL 计划任务时，运行频率为“每3小时”，

　　开始时间在 '00:00:00' 和 '03:00:00' 之间的任意一个时间点，只要不与其他任务相重合就行了。开始日期为当天。

　　/*

　　SQL2005 环境下的数据库备份脚本

　　[说明]

　　业务数据库采用的是“简单”模式，无法实现“日志差异”备份。

　　但是，如果合理地安排和利用现有的磁盘资源，还是可以通过提高备份频率来弥补的。

　　[注意]

　　1、因为 SQL2005 安全的原因，需要开启 xp_cmdshell 调用外部命令来处理备份文件。

　　2、数据库备份前的[一致性检查]需要使用 SQL2000 的 isql.exe ，请注意修改相应的[登录用户名]和[密码]。还有 [isql.exe 的路径]

　　************ SQL2005 不再提供 isql.exe , 所以，isql.exe 可以与【压缩程序】放在一起 ************

　　3、所有“★”标记处要根据实际情况修改

　　4、所有 print ' ' 仅参与格式输出，以方便日志查看，没有实际意义。

　　[安排]

　　根据备份操作时对数据库的影响(每3小时运行一次)和备份速度，以及存放时间，具体安排如下：

　　时间h 磁盘 空间 保留时间 备注

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

　　0-3 \10.20.17.249D$ 100G 7 天 100M铜缆

　　3-6 \202.202.202.26Z$ 327G 10 天 *每月1号和15号存放 00:00 时的备份(30天后压缩)(手工处理)

　　6-9 \202.202.202.22D$ 104G 6 天

　　9-12 \202.202.202.23D$ 100G 6 天

　　12-15 \202.202.202.32D$ 20G 1 天

　　15-18 \202.202.202.33D$ 134G 9 天

　　18-21 \202.202.202.10D$ 50G 2 天

　　21-24 \202.202.202.36Z$ 20G 2 天

　　[SQL参考]

　　BACKUP DATABASE [master] TO DISK = N'd:master.bak' WITH RETAINDAYS = 6, NOFORMAT, NOINIT, NAME = N'master-备份test', SKIP, NOREWIND, NOUNLOAD, STATS = 10

　　SQL 2005 下开启 xp_cmdshell的办法[EXEC sp_configure 'xp_cmdshell', 0 ----关闭]

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

　　*/

　　CREATE Proc usp_job_backup_this4

　　(@TEST int = 0) ----如果 @test=1 ，则所有 EXEC 都不执行，改为 Print 输出.

　　AS

　　----

　　SET NOCOUNT ON

　　----参数设置

　　Declare @备份路径名称 VarChar(99)

　　Declare @保留时间 VarChar(3)

　　Declare @数据库名 VarChar(20)

　　Select @数据库名='SQLSERVER' -----★如有必要，请注意大小写!★

　　----检查参数

　　IF not exists (Select 1 from master.dbo.sysdatabases where name =@数据库名)

　　Begin

　　Select '错误提示：'='当前系统数据库中没有找到用户指定的数据库: '+@数据库名+ ' ,备份无法进行 ,在此结束任务!'

　　Print ' '

　　Print ' '

　　Return

　　End

　　----根据前面说明中的要求，配置 ★@备份路径名称★

　　----设置 @CHECKDB = 0 (并不是每次都有条件执行 DBCC CHECKD )

　　Declare @CHECKDB int

　　Select @CHECKDB = 0

　　----

　　IF Convert(VarChar,GetDate(),24) between '00:00:00' and '03:00:00'

　　Begin

　　Select @备份路径名称='\10.20.17.249D$' , @保留时间='7'

　　----每月1号和15号修改 @备份路径名称 和 @保留时间 (999表示无期限或手工处理备份)

　　IF DATEPART(dd,GetDate()) = 1 Select @备份路径名称='\202.202.202.26Z$' , @保留时间='999'

　　IF DATEPART(dd,GetDate()) = 15 Select @备份路径名称='\202.202.202.26Z$' , @保留时间='999'

　　----★每月的备份启用 DBCC CHECKD★

　　Select @CHECKDB = 1

　　End

　　----

　　IF Convert(VarChar,GetDate(),24) between '03:00:00' and '06:00:00'

　　Begin

　　Select @备份路径名称='\202.202.202.26Z$' , @保留时间='10'

　　----★在服务器负荷较少的时候运行 DBCC CHECKD★

　　Select @CHECKDB = 1

　　End

　　IF Convert(VarChar,GetDate(),24) between '06:00:00' and '09:00:00'

　　Begin

　　Select @备份路径名称='\202.202.202.22D$' , @保留时间='5'

　　End

　　IF Convert(VarChar,GetDate(),24) between '09:00:00' and '12:00:00'

　　Begin

　　Select @备份路径名称='\202.202.202.23D$' , @保留时间='5'

　　End

　　IF Convert(VarChar,GetDate(),24) between '12:00:00' and '15:00:00'

　　Begin

　　Select @备份路径名称='\202.202.202.32D$' , @保留时间='1'

　　End

　　IF Convert(VarChar,GetDate(),24) between '15:00:00' and '18:00:00'

　　Begin

　　Select @备份路径名称='\202.202.202.33D$' , @保留时间='7'

　　End

　　IF Convert(VarChar,GetDate(),24) between '18:00:00' and '21:00:00'

　　Begin

　　Select @备份路径名称='\202.202.202.10D$' , @保留时间='2'

　　End

　　IF Convert(VarChar,GetDate(),24) between '21:00:00' and '24:00:00'

　　Begin

　　Select @备份路径名称='\202.202.202.36Z$' , @保留时间='2'

　　End

　　/*

　　----登记远程用户名和密码，这个操作在 MS-DOS 中为每个远程地址运行一次就行了，不用每次执行。

　　net use \COMPUTER /user:用户名 密码

　　----删除所有登记信息

　　net use * /delete

　　*/

　　----组合 @备份路径名称

　　Select @备份路径名称= @备份路径名称 + 'This_Backup' + ltrim(rtrim(@数据库名))

　　Select @备份路径名称= ltrim(rtrim(@备份路径名称))

　　----建立 @备份路径名称 文件夹

　　Declare @RUN VarChar(1000)

　　Select @RUN='EXECUTE xp_create_subdir N''' + @备份路径名称 + ''''

　　IF @TEST = 1 Print @RUN ELSE EXEC(@RUN)

　　-----组合 @备份文件名称

　　Declare @备份文件名称 VarChar(60)

　　Select @备份文件名称=Convert(char(8),GetDate(),112) + replace(convert(char(6),GetDate(),108),':','')

　　Select @备份文件名称= @数据库名 + '_BackUp_' + @备份文件名称 + '.bak'

　　Select @备份文件名称=ltrim(rtrim(Convert(VarChar,@备份文件名称)))

　　----组合 备份语句

　　Select @RUN=''

　　Select @RUN= 'BACKUP DATABASE '+ @数据库名 + ' TO DISK = N''' + @备份路径名称+''+@备份文件名称 +

　　''' WITH RETAINDAYS = ' + @保留时间 + ' , NOFORMAT, NOINIT, NAME = N''' + @备份文件名称 +

　　''', SKIP, NOREWIND, NOUNLOAD, STATS = 10'

　　----显示备份任务

　　Print @RUN

　　----打开 xp_cmdshell [ 说明: 当 value_in_use = 1 时可以使用 xp_cmdshell ]

　　IF (Select value_in_use from sys.configurations where name = 'xp_cmdshell') <> 1

　　Begin

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

　　End

　　----清理过期的备份,以保证有足够的磁盘空间可用。

　　Begin

　　----为游标的输出值准备变量

　　Declare @DOS_Command VarChar(200) , @backup_set_id int

　　----定义游标

　　Declare my_cursor cursor

　　for

　　----按 backup_start_date 清理过期的备份

　　Select top 7 'pushd ' + @备份路径名称 + ' & dir ' +Replace(name,'.bak','.*')+ ' & del /q ' +Replace(name,'.bak','.*') , backup_set_id

　　from msdb.dbo.backupset

　　where database_name= @数据库名

　　and backup_start_date < dateadd(dd,Convert(int,@保留时间-1)*-1,Convert(varchar,GetDate(),23)) ---- 过了 00:00:00 就算是过期了

　　and description = @备份路径名称 ---- 相同路径下

　　order by backup_start_date desc

　　----按 expiration_date 清理过期的备份还没有写出来 :-)

　　----打开游标

　　open my_cursor

　　----提第一行数据,并赋值.S

　　fetch next from my_cursor into @DOS_Command , @backup_set_id

　　----检查游标集是否还有数据(0表示已成功找到数据)

　　while @@fetch_status = 0

　　Begin

　　/*在这里处理找到的数据*/

　　----删除备份

　　Select @DOS_Command ,@backup_set_id , expiration_date from msdb.dbo.backupset where backup_set_id = @backup_set_id

　　IF @TEST = 0 exec xp_cmdshell @DOS_Command

　　/*为循环内部的变量再次赋值*/

　　fetch next from my_cursor into @DOS_Command , @backup_set_id

　　end

　　----关闭游标

　　close my_cursor

　　----删除游标

　　deallocate my_cursor

　　----

　　END

　　/*

　　在数据库备份之前，应该进行数据的一致性检查：

　　1。运行检查点进程 checkpoint [强制将当前数据库的所有脏页写到磁盘上。]

　　2。检查数据库 dbcc checkdb

　　3。检查页面 dbcc checkalloc

　　4。检查系统表 dbcc checkcatalog

　　然后再进行数据库备份。

　　【DBCC CHECKDB】 是大量占用 CPU 和磁盘的操作。每一个需要检查的数据页都必须首先从磁盘读入内存。另外，DBCC CHECKDB 使用 tempdb 排序。

　　建议在服务器负荷较少的时候运行 DBCC CHECKDB。如果在负荷高峰期运行 DBCC CHECKDB，那么事务吞吐量性能和 DBCC CHECKDB 完成时间性能都会受到影响。

　　【DBCC CHECKALLOC】 对数据库中的分配和页使用(包括索引视图)情况进行检查。

　　只用于向后兼容性的 NOINDEX 选项也适用于索引视图。

　　如果已经执行 DBCC CHECKDB，则不必执行 DBCC CHECKALLOC。

　　DBCC CHECKDB 是 DBCC CHECKALLOC 的超集，除了对索引结构和数据完整性进行检查之外，它还包括对分配进行检查。

　　*/

　　----★一致性检查[只保留检查的最终结果到日志文件，注意修改 isql.exe 使用的(用户名)和(密码)还有 isql.exe 所在的路径★

　　Declare @CHECK VarChar(1000)

　　Print '1.运行检查点进程 ' + Convert(varchar,GetDate(),21)

　　CHECKPOINT

　　Print '2.检查数据库 ' + Convert(varchar,GetDate(),21)

　　IF @CHECKDB = 1

　　Begin

　　Select @CHECK='C:Progra~17-Zipisql.exe -S '+@@SERVERNAME+' -d '+@数据库名+' -U sa -P ★密码★ R -Q "dbcc checkdb ('+@数据库名+') " -o "%temp%check.log" -w 9999 & find "CHECKALLOC" "%temp%check.log" '

　　IF @TEST = 1 Print @CHECK ELSE EXEC xp_cmdshell @CHECK

　　End

　　Print '3.检查页面 ' + Convert(varchar,GetDate(),21)

　　IF @CHECKDB = 0

　　Begin

　　Select @CHECK='C:Progra~17-Zipisql.exe -S '+@@SERVERNAME+' -d '+@数据库名+' -U sa -P ★密码★ -Q "dbcc checkalloc ('+@数据库名+') " -o "%temp%check.log" -w 9999 & find "CHECKALLOC" "%temp%check.log" '

　　IF @TEST = 1 Print @CHECK ELSE EXEC xp_cmdshell @CHECK

　　End

　　Print '4.检查系统表 ' + Convert(varchar,GetDate(),21)

　　Select @CHECK='C:Progra~17-Zipisql.exe -S '+@@SERVERNAME+' -d '+@数据库名+' -U sa -P ★密码★ -Q "dbcc checkcatalog ('+@数据库名+')" '

　　IF @TEST = 1 Print @CHECK ELSE EXEC xp_cmdshell @CHECK

　　----执行备份操作

　　Print '5.执行备份操作 ' + Convert(varchar,GetDate(),21)

　　IF @TEST = 0 EXEC (@RUN)

　　----获取当前备份的 @backupSetId，并更新备份日志,将 @备份路径名称 记录到 description

　　Declare @backupSetId as int

　　Select @backupSetId = backup_set_id from msdb..backupset where database_name=@数据库名

　　and backup_set_id=(Select max(backup_set_id) from msdb..backupset where database_name=@数据库名 )

　　IF @backupSetId is null Begin select '验证失败。找不到数据库“'+@数据库名+'”的备份信息。' end

　　Update msdb.dbo.backupset set description = @备份路径名称 where backup_set_id = @backupSetId

　　----当备份文件小于 500M 时压缩数据库(太大了就没有压缩的必要了，太费时间了!)

　　Declare @backup_size VarChar(20)

　　Select @backup_size = Convert(VarChar,backup_size/(1024*1000)) ----将 backup_size 换成兆

　　from msdb.dbo.backupset where backup_set_id = @backupSetId

　　Select @backup_size = LEFT(@backup_size, PATINDEX ('%.%',@backup_size)-1 ) ----提取小数点间的数值，计算单位为兆(M)

　　IF Convert(int,@backup_size) < 500 ----★这个大小可以自己定★

　　Begin

　　Select '数据库大小为 '+ Convert(VarChar,@backup_size) +' M , 启用【压缩程序】压缩数据库备份文件: '+ Convert(varchar,GetDate(),21)

　　Declare @压缩程序 VarChar(50) ----★必须包含绝对路径★

　　Select @压缩程序='C:Progra~17-Zip7z.exe'

　　----进入备份文件所在文件夹以后，将备份文件压缩成 .zip 文件，如果存在同名文件，则直接覆盖。最后删除原文件。

　　/* 这下面的是语法说明，详见相应的HELP文档资料。

　　-mx9 x=[0 | 1 | 3 | 5 | 7 | 9 ] Sets level of compression. (Default valus = 5)

　　-aoa Overwrite All existing files without prompt.

　　-aos Skip extracting of existing files.

　　-aou aUto rename extracting file (for example, name.txt will be renamed to name_1.txt).

　　-aot auto rename existing file (for example, name.txt will be renamed to name_1.txt).

　　*/

　　Declare @DOS_RUN VarChar(1000)

　　Select @DOS_RUN = 'PUSHD "' + @备份路径名称 + '" & "' + @压缩程序 + '" a ' + Replace(@备份文件名称,'.bak','') + '.zip ' + @备份文件名称 + ' -mx9 -aot & del "'+ @备份文件名称 +'" & POPD '

　　Print @DOS_RUN

　　IF @TEST = 0 EXEC xp_cmdshell @DOS_RUN

　　End

　　----如有必要，可以在完成操作之后关闭 xp_cmdshell

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

　　----记录所有工作的完成时间

　　declare @RunTime int

　　declare @DW VarChar(6)

　　Select @RunTime = datedIFf(mi,backup_start_date,backup_finish_date) from msdb.dbo.backupset where backup_set_id=@backupSetId

　　Select @DW = ' 分钟'

　　IF @RunTime <= 0

　　Begin

　　Select @RunTime = datedIFf(ss,backup_start_date,GetDate()) from msdb.dbo.backupset where backup_set_id=@backupSetId

　　Select @DW = ' 秒钟'

　　End

　　Select '以上所有工作花费了 ' + Convert(VarChar,@RunTime) + @DW +' ,现在时间是: '+ Convert(VarChar,GetDate(),21) + ' [ Select * from msdb.dbo.backupset where backup_set_id = ' + Convert(VarChar,@backupSetId) + ' ] '

　　Print ' '

　　Print ' '

　　Print ' '

　　/*

　　----清理日志表记录

　　Select * from msdb.dbo.backupset

　　Select * from msdb.dbo.restorehistory

　　----

　　删除 backupset 和 restorehistory 记录表中所有早于指定日期的备份集的条目。

　　由于执行备份或还原操作后会在备份和还原历史记录表中添加一些行，

　　因此使用 sp_delete_backuphistory 可以减小 msdb 数据库中历史记录表的大小。

　　----

　　Declare @dt datetime

　　Select @dt = cast(N'01/01/2009 00:00:00' as datetime)

　　exec msdb.dbo.sp_delete_backuphistory @dt

　　----删除作业的历史记录

　　EXEC msdb.dbo.sp_purge_jobhistory @oldest_date='01/01/2009 00:00:00'

　　EXEC msdb..sp_maintplan_delete_log null,null,'01/01/2009 00:00:00'

　　*/

评论总数：0 条 [ 查看全部 ] 网友评论