你需要差异备份吗?

本文介绍了一个SQL脚本,用于计算数据库自上次完整备份以来的数据更改率。通过此脚本,可以评估是否更适合进行完整备份还是差异备份。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

通过下面的脚本可以计算出数据库从上次完整备份之数据的更改率,在Pual之前还没有人写过类似的代码。

根据运行的结果,我们可以知道数据库的数据自上次完整备份之后的数据修改程度,如果更概率非常大的情况下,我们可以直接选择完整备份,而省掉差异备份,如果更改率非常小则可以选择差异备份。(当然如果数据库小的话即使数据更改很小也不需要差异备份)

即使不需要调整你的备份计划你也可以对数据库的状况有一个深刻的了解。

/*=====================================================================

File: SQLskillsDIFForFULL.sql

Summary: This script creates a system-wideSP SQLskillsDIFForFILL thatworks out what percentage of a database haschanged since theprevious full database backup.

Date: April 2008

SQL Server Versions:

10.0.1300.13 (SS2008 February CTP -CTP-6)

9.00.3054.00 (SS2005 SP2)

------------------------------------------------------------------------------

Copyright (C) 2008 Paul S. Randal All rights reserved.You may alter this code for your own*non-commercial* purposes. You mayrepublish altered code as long as you givedue credit.THIS CODE AND INFORMATION ARE PROVIDED"AS IS" WITHOUT WARRANTY OFANY KIND, EITHER EXPRESSED OR PLIED,INCLUDING BUT NOT LIMITEDTO THE IMPLIED WARRANTIES OFMERCHANTABILITY AND/OR FITNESS FOR APARTICULAR PURPOSE.

============================================================================*/

-- Create thefunction in MSDB

--

USE msdb;

GO

IF EXISTS(SELECT*FROMsys.objectsWHERE NAME='SQLskillsConvertToExtents')

DROP FUNCTION SQLskillsConvertToExtents;

GO

-- This functioncracks the output from a DBCC PAGE dump

-- of anallocation bitmap. It takes a string in the form

-- "(1:8) -(1:16)" or "(1:8) -" and returns the number

-- of extentsrepresented by the string. Both the examples

-- above equal 1extent.

--

CREATE FUNCTION SQLskillsConvertToExtents(

@extents VARCHAR (100))

RETURNS INTEGER

AS

BEGIN

DECLARE @extentTotal INT;

DECLARE@colon INT;

DECLARE@firstExtent INT;

DECLARE@secondExtent INT;

SET@extentTotal = 0;

SET @colon =CHARINDEX(':', @extents);

-- Check for thesingle extent case

--

IF (CHARINDEX(':', @extents, @colon + 1) = 0)

SET@extentTotal = 1;

ELSE

-- We're inthe multi-extent case

--

BEGIN

SET@firstExtent = CONVERT(INT,

SUBSTRING(@extents, @colon+ 1,CHARINDEX(')', @extents, @colon)- @colon- 1));

SET@colon =CHARINDEX(':', @extents, @colon+ 1);

SET@secondExtent = CONVERT(INT,

SUBSTRING(@extents, @colon+ 1,CHARINDEX(')', @extents, @colon)- @colon- 1));

SET@extentTotal =(@secondExtent -@firstExtent)/8+ 1;

END

RETURN@extentTotal;

END;

GO

USE master;

GO

IF OBJECT_ID('sp_SQLskillsDIFForFULL')ISNOTNULL

DROP PROCEDURE sp_SQLskillsDIFForFULL;

GO

-- This SPcracks all differential bitmap pages for all online

-- data files ina database. It creates a sum of changed extents

-- and reportsit as follows (example small msdb):

--

-- EXECsp_SQLskillsDIFForFULL 'msdb';

-- GO

--

-- Total ExtentsChanged Extents Percentage Changed

-- ---------------------------- ----------------------

-- 102 56 54.9

--

-- Note thatafter a full backup you will always see some extents

-- marked aschanged. The number will be 4 + (number of data files - 1).

-- These extentscontain the file headers of each file plus the

-- roots of someof the critical system tables in file 1.

-- The number formsdb may be round 20.

--

CREATE PROCEDURE sp_SQLskillsDIFForFULL(

@dbName VARCHAR (128))

AS

BEGIN

SET NOCOUNTON;

-- Create the temptable

--

IF EXISTS(SELECT*FROM msdb.sys.objectsWHERE NAME= 'SQLskillsDBCCPage')

DROP TABLE msdb.dbo.SQLskillsDBCCPage;

CREATE TABLE msdb.dbo.SQLskillsDBCCPage(

[ParentObject] VARCHAR(100),

[Object] VARCHAR (100),

[Field] VARCHAR (100),

[VALUE] VARCHAR (100));

DECLARE@fileID INT;

DECLARE@fileSizePages INT;

DECLARE@extentID INT;

DECLARE@pageID INT;

DECLARE@DIFFTotal INT;

DECLARE@sizeTotal INT;

DECLARE@total INT;

DECLARE@dbccPageString VARCHAR (200);

SELECT@DIFFTotal = 0;

SELECT@sizeTotal = 0;

-- Setup a cursorfor all online data files in the database

--

DECLAREfiles CURSORFOR

SELECT[file_id], [size]FROMmaster.sys.master_files

WHERE[type_desc] = 'ROWS'

AND[state_desc] = 'ONLINE'

AND[database_id] = DB_ID(@dbName);

OPEN files;

FETCH NEXT FROM files INTO @fileID,@fileSizePages;

WHILE @@FETCH_STATUS= 0

BEGIN

SELECT@extentID = 0;

-- The sizereturned from master.sys.master_files is in

-- pages - weneed to convert to extents

--

SELECT@sizeTotal = @sizeTotal + @fileSizePages / 8;

WHILE (@extentID<@fileSizePages)

BEGIN

-- Theremay be an issue with the DIFF map page position

-- on thefour extents where PFS pages and GAM pages live

-- (at pageIDs 516855552, 1033711104, 1550566656, 2067422208)

-- but Ithink we'll be ok.

-- PFS pagesare every 8088 pages (page 1, 8088, 16176, etc)

-- GAMextents are every 511232 pages

--

SELECT@pageID = @extentID +6;

-- Build thedynamic SQL

--

SELECT@dbccPageString = 'DBCCPAGE ('

+@dbName + ', '

+ CAST(@fileIDASVARCHAR)+', '

+ CAST(@pageIDASVARCHAR)+', 3) WITH TABLERESULTS,NO_INFOMSGS';

-- Empty outthe temp table and insert into it again

--

DELETEFROM msdb.dbo.SQLskillsDBCCPage;

INSERTINTO msdb.dbo.SQLskillsDBCCPageEXEC(@dbccPageString);

-- Aggregateall the changed extents using the function

--

SELECT@total = SUM([msdb].[dbo].[SQLskillsConvertToExtents]([Field]))

FROMmsdb.dbo.SQLskillsDBCCPage

WHERE[VALUE] = ' CHANGED'

AND[ParentObject] LIKE 'DIFF_MAP%';

SET@DIFFTotal = @DIFFTotal + @total;

-- Move tothe next GAM extent

SET@extentID = @extentID +511232;

END

FETCH NEXTFROM filesINTO @fileID,@fileSizePages;

END;

-- Clean up

--

DROP TABLE msdb.dbo.SQLskillsDBCCPage;

CLOSE files;

DEALLOCATEfiles;

-- Output theresults

--

SELECT

@sizeTotal AS[Total Extents],

@DIFFTotal AS[Changed Extents],

ROUND(

(CONVERT(FLOAT, @DIFFTotal)/

CONVERT(FLOAT, @sizeTotal))* 100, 2)AS [Percentage Changed];

END;

GO

-- Mark the SPas a system object

--

EXEC sys.sp_MS_marksystemobjectsp_SQLskillsDIFForFULL;

GO

-- Test to makesure everything was setup correctly

--

EXECsp_SQLskillsDIFForFULL'msdb';

GO

为了在 UNIX、Windows 和 Linux 系统上配置 NetBackup 10.1 以实现差异备份策略,你需要参考《NetBackup 10.1中文版管理指南:第II卷- UNIX/Windows/Linux》。这份官方指南提供了全面的步骤和最佳实践,以下是关键步骤的概述: 参考资源链接:[NetBackup 10.1中文版管理指南:第II卷- UNIX/Windows/Linux](https://wenku.youkuaiyun.com/doc/888sz08f78?spm=1055.2569.3001.10343) 1. **了解差异备份**: 差异备份是指备份自上次全量备份以来发生变化的数据。在 NetBackup 中实现差异备份策略之前,确保理解其工作原理和优势。 2. **安装和配置 NetBackup**: 根据指南的安装与配置部分,确保 NetBackup 已在你的系统上安装正确,包括所有必要的客户端和介质服务器。 3. **定义备份策略**: 在 NetBackup 管理控制台中创建新的策略。选择适当的策略类型,如‘策略类型’选择‘标准’,并设置‘备份类型’为‘差异备份’。 4. **配置客户端和备份窗口**: 为你的 UNIX、Windows 或 Linux 客户端配置备份时间窗口,并确保策略与客户端的备份需求相匹配。 5. **设置差异级别**: 在策略配置中,你可以设置差异备份的级别,通常有两个级别——‘前一次全量备份以来的更改’和‘自上次差异备份以来的更改’。 6. **测试备份策略**: 在实际生产环境应用之前,在测试环境中验证备份策略,确保备份按预期运行,并成功恢复数据。 7. **监控和调整性能**: 根据性能优化章节的建议,监控备份操作,并根据实际情况调整资源分配和配置以提高备份速度和效率。 通过以上步骤,你将能够在不同操作系统平台中实现高效的差异备份策略。确保在执行过程中,按照《NetBackup 10.1中文版管理指南:第II卷- UNIX/Windows/Linux》中的指引,定期进行策略评估和优化,以适应数据增长和业务需求变化。如果你在配置过程中遇到任何问题,可以参考问题排查与故障解决部分,获取详细的错误代码解析和解决步骤。 参考资源链接:[NetBackup 10.1中文版管理指南:第II卷- UNIX/Windows/Linux](https://wenku.youkuaiyun.com/doc/888sz08f78?spm=1055.2569.3001.10343)
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值