sql数据库备份恢复如何提高备份速度

发布时间:2020-07-20编辑:脚本学堂
有关sql server数据库备份与恢复时,如何提高数据库备份速度的方法,主要集中在数据传输选项,即备份操作的IO缓冲区数与最大传输单元、数据库压缩compression,需要的朋友参考下。

如何提高sql server数据库的备份速度?

要系统跑的更快,无非就是:优化系统,或更好更强大的服务器,特别是更多的cpu、更大的内存、更快的硬盘。

提高备份的速度也是一样的,一般备份数据库,不太可能是系统宕机,为什么呢?
因为备份数据库所使用的系统资源不会占用太多,所以,要想提高备份的速度,那么只有允许备份使用更多的内存才行,通过下面的2个选项,给备份分配更多的资源,那么备份速度就大大提高了。

一、数据传输选项:
1、缓冲区使用的总计空间由下面公式确定:
buffercount*maxtransfersize

2、BUFFERCOUNT = { buffercount | @buffercount_variable }
指定用于备份操作的 I/O 缓冲区总数。 可以指定任何正整数;但是,较大的缓冲区数可能导致由于 Sqlservr.exe 进程中的虚拟地址空间不足而发生“内存不足”错误。

3、MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }
指定要在 SQL Server 和备份介质之间使用的最大传输单元(字节)。 可能的值是 65536 字节 (64 KB) 的倍数,最多可到 4194304 字节 (4 MB)。

在sql server数据库备份中,还可以更快吗?当然可以的。

其实在备份的时候,之所以慢,是和IO的性能相关的,由于IO是整个计算机体系中最慢的,所以才导致备份也慢,但如果我们能减少写入磁盘的数据,不就可以提高性能了嘛,当然,在减少写入数据的同时,需要通过消耗更多的CPU资源来进行压缩,减小备份的数据大小。

二、compression选项 数据库压缩

指定对于此备份是否执行备份压缩。
从 SQL Server 2008 R2 开始,SQL Server 2008 R2 Standard 和所有更高版本都支持备份压缩。安装时,默认行为是不进行备份压缩。但此默认设置可通过设置 backup compression default 服务器配置选项进行更改。

默认情况下,压缩备份时,将执行校验和(也就是checksum选项)以检测是否存在介质损坏情况。

在SQL SERVER 的参数可知,默认服务器是不启用备份压缩的。
 

select name, 
       description, 
       value_in_use --是否启用此参数
from sys.configurations 
where name ='backup compression default'
 

如果要启用备份压缩,可以运行如下的命令:
 

exec sp_configure 'backup compression default',1 
reconfigure 
go

还能再快吗?

这次是运用IO的并行特性,也就是如果在服务器上有多个物理硬盘,那么可以把一个备份文件条带化,比如,有3个硬盘,那么就把原来的备份文件,分成3份,分别存储到3个硬盘上。

实验代码

1、先建库,建表,插入大量数据:
 

use master
go
 
if DB_ID('db_test') is not null
   drop database db_test
go
 
CREATE DATABASE db_test
ON
(
    NAME = db_test_DATA,
    FILENAME = 'E:db_test.mdf'
)
 
LOG ON
(
    NAME = db_test_LOG,
    FILENAME = 'E:db_test.ldf'
)
GO
 
use db_test
go
 
select * Into test
from sys.objects
go
 
insert into test
select *
from test
go 20

2、普通的完整备份,耗时213秒:
 

backup database db_test
to disk = 'e:db_test.bak'
with format
/*
已为数据库 'db_test',文件 'db_test_DATA' (位于文件 1 上)处理了 356448 页。
已为数据库 'db_test',文件 'db_test_LOG' (位于文件 1 上)处理了 3 页。
BACKUP DATABASE 成功处理了 356451 页,花费 213.018 秒(13.072 MB/秒)。
*/

3、增大了IO缓冲区的完整备份,耗时142秒:
 

backup database db_test
to disk = 'e:db_test1.bak'
with format,
     buffercount = 10,
     maxtransfersize = 4194304
/*
已为数据库 'db_test',文件 'db_test_DATA' (位于文件 1 上)处理了 356448 页。
已为数据库 'db_test',文件 'db_test_LOG' (位于文件 1 上)处理了 1 页。
BACKUP DATABASE 成功处理了 356449 页,花费 142.101 秒(19.597 MB/秒)。
*/

4、增大IO缓冲区、压缩完整备份,耗时56秒:
 

backup database db_test
to disk = 'e:db_test2.bak'
with format,
     buffercount = 10,
     maxtransfersize = 4194304,
     compression
/*
已为数据库 'db_test',文件 'db_test_DATA' (位于文件 1 上)处理了 356448 页。
已为数据库 'db_test',文件 'db_test_LOG' (位于文件 1 上)处理了 1 页。
BACKUP DATABASE 成功处理了 356449 页,花费 56.089 秒(49.648 MB/秒)。
*/

5、增大IO缓冲区、压缩、条带化完整备份,耗时55秒,由于是笔记本电脑,就一个硬盘,所以效果不明显:
 

backup database db_test 
to disk = 'c:db_test_stripping1.bak', 
   disk = 'd:db_test_stripping2.bak', 
   disk = 'e:db_test_stripping3.bak'
with format,
     buffercount = 10,
     maxtransfersize = 4194304,
     compression  
/*
已为数据库 'db_test',文件 'db_test_DATA' (位于文件 1 上)处理了 356448 页。
已为数据库 'db_test',文件 'db_test_LOG' (位于文件 1 上)处理了 1 页。
BACKUP DATABASE 成功处理了 356449 页,花费 55.060 秒(50.576 MB/秒)。
*/
 

通过在笔记本电脑上实验,可以看出明显的差别,特别是使用了compression选项后,比最原始的完整备份,速度提高了4倍。
服务器上,应该能提高更多。