SQL Server 备份迁移策略
标签:SQL SERVER/MSSQL SERVER/数据库/DBA/xp_cmdshell/备份压缩
概述
当备份空间不是很充裕的情况下需要找方法将备份文件拷贝到专用的备份机器上去,特别是存储空间不够优越的企业更是如此,接下来就分享两种迁移备份文件的方法。
目录
服务器配置选项
查询服务器配置
使用xp_cmdshell方法之前必须先开启对应的服务器配置选项,查询系统中每个服务器范围的配置选项值使用以下语句。
--系统中每个服务器范围的配置选项值对应一行 SELECT * FROM sys.configurations ORDER BY name DESC GO
开启xp_cmdshell
---开启xp_cmdshell sp_configure 'show advanced options', 1;--开启sp_configure系统存储过程高级选项,默认为0 go reconfigure;---指定如果配置设置不需要服务器停止并重新启动,则更新当前运行的值.RECONFIGURE 还会检查新的配置值中是否有无效值(如果是RECONFIGURE WITH OVERRIDE则会跳过错误检查) go sp_configure 'xp_cmdshell', 1; go reconfigure; go sp_configure 'show advanced options', 0; go
开启allow updates
在服务器范围的配置里面如果‘allow updates’的vlues值是‘1’的话,会阻止对系统表的更新,即上面开启xp_cmdshell会被阻止,所以必须先开启allow updates
----开启允许更新系统表功能(当更改系统配置选项时提示不运行对系统目录即时更新时需要开启改功能) sp_configure 'show advanced options', 1; GO sp_configure 'allow updates',0 ---开启允许更新系统表 go sp_configure 'show advanced options', 0; go ----关闭更新系统表功能 sp_configure 'show advanced options', 1; GO sp_configure 'allow updates',1 go sp_configure 'show advanced options', 0; go
xp_cmdshell方法
先建立一个备份数据库的存储过程,然后通过作业调用存储过程执行备份。
USE [master] GO CREATE 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 int SET @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=1 SET @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) end RESTORE VERIFYONLY FROM DISK = @FullFileName WITH FILE = @backupSetId, NOUNLOAD, NOREWIND EXEC 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 @SQLStr2 END
---图片解释
执行备份
USE [master] EXEC sp_bakupDataBase 'AdventureWorks2008R2'
dos方法
如果远程备份空间不够充足,需要将备份的数据库进行压缩后再拷贝,先通过正常的备份将数据库备份到本地,然后使用RAR.EXE压缩软件,压缩并拷贝到远程电脑,将下面的批处理保存成bat后缀格式,然后可以通过任务计划调用批处理进行定时操作。
@echo off rem 为注释语法 timeout 为window 2003版本才有的指定 xp不兼容 setlocal enabledelayedexpansion set /a t=0*3600 :start for /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% /nobreak rem goto :start
---图片解释
总结
凡事都有利有弊,对于第一种方案开启XP_cmdshell存在一定的安全风险,当你的服务启动账户的权限过高的话别人就可以通过XP_cmdshell来进行很多危险的操作,包括新增账户、新建文件夹、删除文件等,所以在使用完之后建议关闭 XP_cmdshell。如果备份空间足够充裕不需要这么麻烦,第二种方法对服务器的压力比较大而且迁移时间也很长,不建议使用。
备注: 作者:pursuer.chen 博客:http://www.cnblogs.com/chenmh 本站点所有随笔都是原创,欢迎大家转载;但转载时必须注明文章来源,且在文章开头明显处给明链接,否则保留追究责任的权利。 《欢迎交流讨论》 |
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语,封装的思维:从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热?
· 从 HTTP 原因短语缺失研究 HTTP/2 和 HTTP/3 的设计差异
· 周边上新:园子的第一款马克杯温暖上架
· 分享 3 个 .NET 开源的文件压缩处理库,助力快速实现文件压缩解压功能!
· Ollama——大语言模型本地部署的极速利器
· DeepSeek如何颠覆传统软件测试?测试工程师会被淘汰吗?
· 使用C#创建一个MCP客户端