你需要差异备份吗？

通过下面的脚本可以计算出数据库从上次完整备份之数据的更改率，在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