你需要差异备份吗？

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

 

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

 

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

 

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

    File: SQLskillsDIFForFULL.sql

 

   Summary: This script creates a system-wideSP SQLskillsDIFForFILL that    works out what percentage of a database haschanged since the    previous 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 may    republish altered code as long as you givedue credit.    THIS CODE AND INFORMATION ARE PROVIDED"AS IS" WITHOUT WARRANTY OF    ANY KIND, EITHER EXPRESSED OR  PLIED,INCLUDING BUT NOT LIMITED    TO THE IMPLIED WARRANTIES OFMERCHANTABILITY AND/OR FITNESS FOR A    PARTICULAR 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