SQL Server 备份迁移策略

标签：SQL SERVER/MSSQL SERVER/数据库/DBA/xp_cmdshell/备份压缩

当备份空间不是很充裕的情况下需要找方法将备份文件拷贝到专用的备份机器上去，特别是存储空间不够优越的企业更是如此，接下来就分享两种迁移备份文件的方法。

目录

• 概述
• 服务器配置选项
  • 查询服务器配置
  • 开启xp_cmdshell
  • 开启allow updates
• xp_cmdshell方法
  • 执行备份
• dos方法
• 总结

使用xp_cmdshell方法之前必须先开启对应的服务器配置选项，查询系统中每个服务器范围的配置选项值使用以下语句。

--系统中每个服务器范围的配置选项值对应一行SELECT * FROM sys.configurationsORDER BY name DESCGO

---开启xp_cmdshellsp_configure 'show advanced options', 1;--开启sp_configure系统存储过程高级选项,默认为0goreconfigure;---指定如果配置设置不需要服务器停止并重新启动，则更新当前运行的值.RECONFIGURE 还会检查新的配置值中是否有无效值(如果是RECONFIGURE WITH OVERRIDE则会跳过错误检查)gosp_configure 'xp_cmdshell', 1;goreconfigure;gosp_configure 'show advanced options', 0;go

在服务器范围的配置里面如果‘allow updates’的vlues值是‘1’的话，会阻止对系统表的更新，即上面开启xp_cmdshell会被阻止，所以必须先开启allow updates

----开启允许更新系统表功能(当更改系统配置选项时提示不运行对系统目录即时更新时需要开启改功能)sp_configure 'show advanced options', 1;GOsp_configure 'allow updates',0 ---开启允许更新系统表gosp_configure 'show advanced options', 0;go----关闭更新系统表功能sp_configure 'show advanced options', 1;GOsp_configure 'allow updates',1gosp_configure 'show advanced options', 0;go

先建立一个备份数据库的存储过程，然后通过作业调用存储过程执行备份。

USE [master] GOCREATE PROCEDURE [dbo].[SP_bakupDataBase] (@dbname nvarchar(50))as Declare @strCmdShell varchar(300) Declare @FullFileName Varchar(200) Declare @strPath NVARCHAR(200)Declare @ToFileName varchar(200) Declare @SQLStr varchar(500) Declare @SQLStr2 varchar(500) Declare @FlagDel INT declare @backupSetId as intSET @strPath = convert(NVARCHAR(19),getdate(),120)SET @strPath = REPLACE(REPLACE(convert(NVARCHAR(19),getdate(),120), ':' , ''),' ','_')SET @FullFileName='E:/'+@dbname+'/'+@dbname+'_backup_'+@strPath+'.bak' SET @ToFileName='//192.168.1.1/E$/备份' SET @FlagDel=1SET @SQLStr='copy '+@FullFileName+' '+@ToFileName SET @SQLStr2='del ' +@FullFileName BackUp DataBase @dbname To Disk= @FullFileName WITH NOINIT , NOUNLOAD , NOSKip, NOFORMAT , STATS = 10,CHECKSUM,CONTINUE_AFTER_ERROR---验证备份集是否有效select @backupSetId = position from msdb..backupset where database_name=@dbname and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=@dbname )IF @backupSetId is null begin raiserror(N'验证失败。找不到数据库数据库的备份信息。', 16, 1) endRESTORE VERIFYONLY FROM  DISK = @FullFileName WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWINDEXEC master.dbo.xp_cmdshell 'net use //192.168.1.1/E$ 123456 /user:192.168.1.1/user' EXEC Master..xp_cmdshell @SQLStr IF (@FlagDel =1)BEGIN EXEC master.. xp_cmdshell @SQLStr2END

---图片解释

USE [master] EXEC sp_bakupDataBase 'AdventureWorks2008R2'

如果远程备份空间不够充足，需要将备份的数据库进行压缩后再拷贝，先通过正常的备份将数据库备份到本地，然后使用RAR.EXE压缩软件，压缩并拷贝到远程电脑，将下面的批处理保存成bat后缀格式，然后可以通过任务计划调用批处理进行定时操作。

@echo offrem  为注释语法 timeout 为window 2003版本才有的指定 xp不兼容setlocal enabledelayedexpansionset /a t=0*3600:startfor /f "delims=" %%a in ("dir /b 'D:/DataBase/person/person*'") do ("c:/Program Files/WinRAR/winrar.exe" a -as -r -EP1 -ibck -df "//192.168.1.1/DataBase/person/person_.rar"  -m3 -agyyyymmddhhmmss "D:/DataBase/person/p*"rem del /q /f "D:/DataBase/person/p*")rem timeout /t %t% /nobreakrem goto :start

---图片解释

凡事都有利有弊，对于第一种方案开启XP_cmdshell存在一定的安全风险，当你的服务启动账户的权限过高的话别人就可以通过XP_cmdshell来进行很多危险的操作，包括新增账户、新建文件夹、删除文件等，所以在使用完之后建议关闭XP_cmdshell。如果备份空间足够充裕不需要这么麻烦，第二种方法对服务器的压力比较大而且迁移时间也很长，不建议使用。