开发者社区> 杰克.陈> 正文

SQL Server使用文件组备份降低备份文件占用的存储空间

简介: 原文:SQL Server使用文件组备份降低备份文件占用的存储空间 对于DBA来说,备份和刷新简历是最重要的两项工作,如果发生故障后,发现备份也不可用,那么刷新简历的重要性就显现出来,哇咔咔!当然备份是DBA最重要的事情(没有之一),在有条件的情况下,我们应该在多个服务器上保留多份完备和日志备份,甚至某些公司会要求将完备数据保留到磁带或超大存储上,以保证可以恢复很久之前的数据。
+关注继续查看
原文:SQL Server使用文件组备份降低备份文件占用的存储空间

对于DBA来说,备份和刷新简历是最重要的两项工作,如果发生故障后,发现备份也不可用,那么刷新简历的重要性就显现出来,哇咔咔!当然备份是DBA最重要的事情(没有之一),在有条件的情况下,我们应该在多个服务器上保留多份完备和日志备份,甚至某些公司会要求将完备数据保留到磁带或超大存储上,以保证可以恢复很久之前的数据。

于是便有一个艰难的选择:备份空间和备份保存期,磁盘再便宜也是要钱的,尤其某些吝啬的老板宁愿多花几十万招个人也不宁愿在硬件上多投资一丁点,把不得把服务器所有资源都利用起来才高兴,在备份空间有限的情况下,我们如何合理设计备份策略以及“备份验证”策略变得尤为关键。

 

在很久之前读过一篇文章,描述某DBA为降低数据库完备占用的存储空间,采用如下方式:

1. 采用完整备份和日志备份将数据库还原到特定时间点(如每天凌晨0点)

2. 删除用户数据库上所有非聚集索引,然后压缩备份

3. 将该备份进行归档保存。

 

从业务角度来说,对于很早之前的数据,即使需要恢复,也不可能将该库恢复到特定时间点并使用恢复的新库进行生产,因此对于很早之前的备份,我们只关心数据而不关心数据上建立的那些索引,即使处于查询需要,也可以重新建立索引后再进行查询。该DBA正是以此为出发点,很多数据库上的非聚集索引能占数据库50%甚至70%的空间(我曾经看过一个表上数十个非聚集索引,部分还是包含索引,占用空间是数据的四五倍以上),删除非聚集索引方式能很有效地降低备份占用的存储空间。

=============================================================

当然上面的废话不是今天的重点,今天的重点是文件组备份。

周末与小伙伴吃饭时,好友paddy提到一个备份策略,将数据和索引拆分到不同文件组(这策略应该很多DBA都会采用),然后只备份“数据”文件组,这样在保证恢复数据的需求的前提下最大限度地降低“数据备份”的占用的存储空间。

 

演示Demo:

首先创建数据库TestDB1001,并创建两个文件组来分别存放DATA和INDEX

CREATE DATABASE [TestDB1001]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'TestDB1001', FILENAME = N'D:\SQLDATA\TestDB1001.mdf' ), 
 FILEGROUP [FG_DATA] 
( NAME = N'TestDB1001_DATA1', FILENAME = N'D:\SQLDATA\TestDB1001_DATA1.ndf' ), 
 FILEGROUP [FG_INDEX] 
( NAME = N'TestDB1001_INDEX1', FILENAME = N'D:\SQLDATA\TestDB1001_INDEX1.ndf'  )
 LOG ON 
( NAME = N'TestDB1001_log', FILENAME = N'D:\SQLDATA\TestDB1001_log.ldf')
GO

PS: 为方便演示,文件增长属性或其他相关信息被移除,演示代码请勿较真

然后创建表和插入数据,注意聚集索引和非聚集索引使用的不同的文件组

USE TestDB1001
GO
CREATE TABLE TB001
(
    C1 INT IDENTITY(1,1) NOT NULL,
    C2 INT
)
GO
ALTER TABLE TB001
ADD CONSTRAINT PK_TB001
PRIMARY KEY(c1)
ON FG_DATA
GO
CREATE INDEX IDX_C2
ON TB001
(
C2
)
ON FG_INDEX
GO

INSERT INTO TB001(C2)
SELECT 1 FROM sys.objects


对数据库进行文件组备份,仅备份PRIMARY和FG_DATA两个文件组:

BACKUP DATABASE TestDB1001 FILEGROUP = N'PRIMARY',FILEGROUP='FG_DATA' 
TO  DISK = N'D:\SQLDATA\TestDB1001_F1.bak'

 

对数据库进行第一次日志备份:

BACKUP LOG TestDB1001
TO DISK = N'D:\SQLDATA\TestDB1001_L1.bak'

 

为演示需要,第二次插入数据:

INSERT INTO TB001(C2)
SELECT 2 FROM sys.objects

 

然后进行第一次差异备份

BACKUP DATABASE TestDB1001 FILEGROUP = N'PRIMARY',FILEGROUP='FG_DATA' 
TO  DISK = N'D:\SQLDATA\TestDB1001_D1.bak' WITH DIFFERENTIAL

 

为演示需要,第三次插入数据:

INSERT INTO TB001(C2)
SELECT 3 FROM sys.objects

 

然后进行第二次日志备份:

BACKUP LOG TestDB1001
TO DISK = N'D:\SQLDATA\TestDB1001_L2.bak'


备份完成后,我们来验证备份还原的可行性,

首先进行文件组还原,注意在还原时,由于未备份FG_INDEX文件组,因此还原时不需要制定INDEX相关的文件信息

RESTORE DATABASE [TestDB1002] 
FILE = N'TestDB1001',  
FILE = N'TestDB1001_DATA1'
FROM  DISK = N'D:\SQLDATA\TestDB1001_F1.bak' 
WITH  FILE = 1,  MOVE N'TestDB1001' TO N'D:\SQLDATA\TestDB1002.mdf',  
MOVE N'TestDB1001_DATA1' TO N'D:\SQLDATA\TestDB1002_DATA1.ndf',
MOVE N'TestDB1001_log' TO N'D:\SQLDATA\TestDB1002_log.ldf',  
NOUNLOAD,  STATS = 10,NORECOVERY,PARTIAL

 

然后还原差异备份:

RESTORE DATABASE [TestDB1002] FROM DISK='D:\SQLDATA\TestDB1001_D1.bak' WITH NORECOVERY

 

最后还原日志备份:

RESTORE DATABASE [TestDB1002] FROM DISK='D:\SQLDATA\TestDB1001_L2.bak' WITH RECOVERY

 

验证数据是否正常:

SELECT C2,COUNT(1) FROM TB001
GROUP BY C2

数据验证通过,证明该方法的确可行。

========================================================

在进行文件组还原的时候,其中PARTIAL选项非常关键,其直接影响后面日志备份是否可用,如果未指定PARTIAL选项,则:

使用WITH RECOVERY选项还原差异备份,不报错,数据库仍处于“正在还原”模式下,还原信息为:

已为数据库 'TestDB1002',文件 'TestDB1001' (位于文件 1 上)处理了 72 页。
已为数据库 'TestDB1002',文件 'TestDB1001_DATA1' (位于文件 1 上)处理了 16 页。
已为数据库 'TestDB1002',文件 'TestDB1001_log' (位于文件 1 上)处理了 3 页。
通过数据库或文件还原操作,只还原了文件“TestDB1001_INDEX1”的一部分。必须成功还原整个文件后,才能应用此备份集。
此 RESTORE 语句成功地执行了一些操作,但由于需要一个或多个 RESTORE 步骤,无法使数据库在线。以前的消息说明了此时无法进行恢复的原因。
RESTORE DATABASE ... FILE=<name> 成功处理了 91 页,花费 0.059 秒(11.983 MB/秒)。

使用WITH RECOVERY选项还原日志备份,直接报错,错误消息为:

消息 4320,级别 16,状态 13,第 1 行
通过数据库或文件还原操作,只还原了文件“TestDB1001_INDEX1”的一部分。必须成功还原整个文件后,才能应用此备份集。
消息 3119,级别 16,状态 1,第 1 行
在计划 RESTORE 语句时发现了问题。以前的消息提供了详细信息。
消息 3013,级别 16,状态 1,第 1 行
RESTORE DATABASE 正在异常终止。


因此在还原文件组备份时,请务必确保使用PARTIAL选项。

========================================================

打完收工,再次感谢paddy的提点。

最近很少写博客,也没有收集妹子的动力,妹子质量下降,各位将就下。。。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
在AIX中查看文件夹占用空间大小
在AIX中查看文件夹占用空间大小 用du -sg 可看出当前文件夹的大小(以G为单位);用du -sg filename 可看出当前文件夹下所有文件或文件夹的大小(以G为单位);du -sk ,du -sm 则分别以KB,MB为单位。
667 0
Sql Server 存储过程使用技巧
1、创建带Try。。。Catch的存储过程模板   Copy下面的代码,然后新建查询,就可以写sql语句,执行完后,一个你自己的存储过程就建立好了! USE [DB]--设定对应的数据库 GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER O...
745 0
11g中关于控制文件自动备份的改进
在之前做一个测试演示的时候,使用的是11gR2的库,在说rman的备份配置的时候有一个功能时控制文件的自动备份, CONFIGURE CONTROLFILE AUTOBACKUP ON/OFF; 然后自己简单介绍了下,说controlfile autobackup功能还是蛮实用的,一般还是建议开启。
771 0
【转】用oledb读取dbf文件报错--“外部表不是预期的格式” [
用oledb读取dbf文件报错--“外部表不是预期的格式”我的代码如下string ole_connstring=String.Empty;ole_connstring = @"Provider=Microsoft.
1253 0
MySQL查看当前使用的配置文件my.cnf的方法
MySQL查看当前使用的配置文件my.cnf的方法 MySQL实例在启动时,会先读取配置参数文件my.cnf。my.cnf一般会放在MySQL的安装目录中,用户也可以放在其他目录加载。
5649 0
+关注
杰克.陈
一个安静的程序猿~
10427
文章
2
问答
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载