通过下面的脚本可以计算出数据库从上次完整备份之数据的更改率，在Pual之前还没有人写过类似的代码。
根据运行的结果，我们可以知道数据库的数据自上次完整备份之后的数据修改程度，如果更概率非常大的情况下，我们可以直接选择完整备份，而省掉差异备份，如果更改率非常小则可以选择差异备份。
www.2cto.com
及时不需要调整你的备份计划你也可以对数据库的状况有一个深刻的了解。
/*=====================================================================
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*FROM sys.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.
--
www.2cto.com
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 www.2cto.com
RETURN@extentTotal;
END;
GO
USE master;
GO
IF OBJECT_ID('sp_SQLskillsDIFForFULL')ISNOT NULL
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
-- www.2cto.com
-- Note thatafter a full backup you will always see some extents
-- marked aschanged. The number will be 4 + (number of data