ITPUB论坛-中国最专业的IT技术社区

 手机号登录  找回密码
 注册
查看: 16514|回复: 16

[原创] SQL SERVER BACKUP DATABASE 加快备份速度方法

[复制链接]
论坛徽章:
6
2009日食纪念
日期:2009-07-22 09:30:00ITPUB9周年纪念徽章
日期:2010-10-08 09:32:262011新春纪念徽章
日期:2011-02-18 11:43:352012新春纪念徽章
日期:2012-01-04 11:53:29秀才
日期:2015-08-06 10:47:08
发表于 2011-3-11 13:30 | 显示全部楼层 |阅读模式
很多SQL SERVER DBA 在备份的时候,一般的撰写格式,都是如下

BACKUP DATABASE AdventureWorks
TO DISK = 'Z:\SQLServerBackups\AdvWorksData.bak'
   WITH FORMAT,CHECKSUM;

对于比较大的数据库,备份时间比较长,

最近在研究了BACKUP DATABASE 参数后,发现可以通过设置一下参数,加快备份速度:

1.备份的时候,设置多个备份集,比如
TO DISK = N'd:/1.bak',DISK = N'd:/2.bak',DISK = N'd:/3.bak',DISK = N'd:/4.bak'

2.使用BUFFERCOUNT 和MAXTRANSFERSIZE 选项。

BUFFERCOUNT  :指定用于备份操作的 I/O 缓冲区总数。可以指定任何正整数;但是,较大的缓冲区数可能导致由于 Sqlservr.exe 进程中的虚拟地址空间不足而发生“内存不足”错误。
缓冲区使用的总计空间由下面公式确定:buffercount*maxtransfersize。

MAXTRANSFERSIZE  :指定要在 SQL Server 和备份介质之间使用的最大传输单元(字节)。可能的值是 65536 字节 (64 KB) 的倍数,最多可到 4194304 字节 (4 MB)。
3.启用备份压缩选项(COMPRESSION)

该选项只在SQL Server 2008 Enterprise版本和SQL Server 2008 R2 Standard 以及所有更高版本都支持。

注意:压缩选项会需要额外的CPU资源,因此建议不要在系统繁忙时候使用。

4。做为DBA,你可能需要保证数据的安全,一般会加上校验和参数(CHECKSUM),保证数据安全。

测试比较:

数据库大小:53282.19 MB(包含日志文件)

数据文件大小是:32GB左右

使用标准备份方法:

BACKUP DATABASE [test]
TO DISK = N'd:/1.bak' WITH CHECKSUM;

需要用时04:08

使用优化参数的备份命令

BACKUP DATABASE [test]
TO DISK = N'D:\test_FULL_20110311_050001_1.bak',
DISK = N'D:\test_FULL_20110311_050001_2.bak',
DISK = N'D:\test_FULL_20110311_050001_3.bak',
DISK = N'D:\test_FULL_20110311_050001_4.bak'
WITH CHECKSUM, COMPRESSION, BUFFERCOUNT = 50, MAXTRANSFERSIZE = 4194304

备份用时:110.350 秒

速度提高了50%

如果不使用CHECKSUM参数,备份速度还可以更快,但是做为DBA,数据的安全是第一位的,因此推荐加上CHECKSUM参数。

以上提起的参数只适用于SQL SERVER 2005和SQL SERVER 2008

以上只是个人研究,有什么问题请各位大侠多提宝贵意见




本人博客:http://space.itpub.net/?8183550

[ 本帖最后由 edwardking888 于 2011-3-11 13:40 编辑 ]
论坛徽章:
114
授权会员
日期:2005-10-30 17:05:332013年新春福章
日期:2013-02-25 14:51:24青年奥林匹克运动会-铁人三项
日期:2014-09-17 10:51:20奔驰
日期:2013-08-01 21:18:36宝马
日期:2013-12-04 21:52:282014年新春福章
日期:2014-02-18 16:41:11马上有车
日期:2014-02-18 16:41:11马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14
发表于 2011-3-11 20:23 | 显示全部楼层
备份出来的文件多大?

32G 数据库2分钟备份完成, 这个存储是什么? 270M/秒?
从4分钟优化到110秒,起作用最大的是什么参数或设置?

[ 本帖最后由 花好月不圆 于 2011-3-11 20:25 编辑 ]

使用道具 举报

回复
论坛徽章:
6
2009日食纪念
日期:2009-07-22 09:30:00ITPUB9周年纪念徽章
日期:2010-10-08 09:32:262011新春纪念徽章
日期:2011-02-18 11:43:352012新春纪念徽章
日期:2012-01-04 11:53:29秀才
日期:2015-08-06 10:47:08
 楼主| 发表于 2011-3-11 22:41 | 显示全部楼层
原帖由 花好月不圆 于 2011-3-11 20:23 发表
备份出来的文件多大?

32G 数据库2分钟备份完成, 这个存储是什么? 270M/秒?
从4分钟优化到110秒,起作用最大的是什么参数或设置?




DELL R710 服务器 6个硬盘,做RAID 10,
没有使用额外存储,速度大约是300M/S左右。
起最大作用的是BUFFERCOUNT 和MAXTRANSFERSIZE
当然,同时写入多个文件也是有用处的。

使用道具 举报

回复
论坛徽章:
4
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:52数据库板块每日发贴之星
日期:2011-01-29 01:01:012011新春纪念徽章
日期:2011-02-18 11:43:33数据库板块每日发贴之星
日期:2011-02-26 01:01:01
发表于 2011-3-12 00:14 | 显示全部楼层
学习了

使用道具 举报

回复
论坛徽章:
6
2009日食纪念
日期:2009-07-22 09:30:00ITPUB9周年纪念徽章
日期:2010-10-08 09:32:262011新春纪念徽章
日期:2011-02-18 11:43:352012新春纪念徽章
日期:2012-01-04 11:53:29秀才
日期:2015-08-06 10:47:08
 楼主| 发表于 2011-3-12 22:15 | 显示全部楼层

使用道具 举报

回复
论坛徽章:
7
ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:442010新春纪念徽章
日期:2010-03-01 11:07:242011新春纪念徽章
日期:2011-02-18 11:43:342012新春纪念徽章
日期:2012-01-04 11:50:44ITPUB十周年纪念徽章
日期:2011-11-01 16:20:28灰彻蛋
日期:2012-05-31 10:18:54
发表于 2011-3-13 10:59 | 显示全部楼层
这是因为,微软发现现在DB的瓶颈在I/O,而不是CPU,所以通过CPU消耗来减少I/O操作,其实表压缩和索引压缩,非常好.

使用道具 举报

回复
论坛徽章:
6
2009日食纪念
日期:2009-07-22 09:30:00ITPUB9周年纪念徽章
日期:2010-10-08 09:32:262011新春纪念徽章
日期:2011-02-18 11:43:352012新春纪念徽章
日期:2012-01-04 11:53:29秀才
日期:2015-08-06 10:47:08
 楼主| 发表于 2011-3-13 11:14 | 显示全部楼层
6楼说的对,
但是表压缩和索引压缩
比较适合数据仓库环境

使用道具 举报

回复
论坛徽章:
11
祖国60周年纪念徽章
日期:2009-10-09 08:28:00懒羊羊
日期:2015-03-04 14:52:112013年新春福章
日期:2013-02-25 14:51:24奥运会纪念徽章:沙滩排球
日期:2012-10-18 09:21:46ITPUB十周年纪念徽章
日期:2011-11-01 16:24:042011新春纪念徽章
日期:2011-02-18 11:43:362012新春纪念徽章
日期:2012-01-04 11:53:54ITPUB9周年纪念徽章
日期:2010-10-08 09:31:222010新春纪念徽章
日期:2010-03-01 11:20:05生肖徽章2007版:兔
日期:2009-11-18 16:38:26
发表于 2011-3-15 08:53 | 显示全部楼层
学习了,顶,可惜偶现在的数据库还是sql2000,要升级到sql2005没有许可证,不知道这个方法在sql2000上可以吗?

使用道具 举报

回复
论坛徽章:
0
发表于 2011-3-15 13:48 | 显示全部楼层
压缩在2008里才有。

使用道具 举报

回复
论坛徽章:
6
2009日食纪念
日期:2009-07-22 09:30:00ITPUB9周年纪念徽章
日期:2010-10-08 09:32:262011新春纪念徽章
日期:2011-02-18 11:43:352012新春纪念徽章
日期:2012-01-04 11:53:29秀才
日期:2015-08-06 10:47:08
 楼主| 发表于 2011-3-16 10:13 | 显示全部楼层
恩!

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表