How to determine if you should use full or differential backup?

Posted by Peter Larsson on SQL Team See other posts from SQL Team or by Peter Larsson
Published on Thu, 21 Oct 2010 14:34:48 GMT Indexed on 2010/12/06 16:58 UTC
Read the original article Hit count: 372

Or ask yourself, "How much of the database has changed since last backup?".
Here is a simple script that will tell you how much (in percent) have changed in the database since last backup.

-- Prepare staging table for all DBCC outputs

DECLARE @Sample TABLE

        (

            Col1 VARCHAR(MAX) NOT NULL,

            Col2 VARCHAR(MAX) NOT NULL,

            Col3 VARCHAR(MAX) NOT NULL,

            Col4 VARCHAR(MAX) NOT NULL,

            Col5 VARCHAR(MAX)

        )

 

-- Some intermediate variables for controlling loop

DECLARE @FileNum BIGINT = 1,

        @PageNum BIGINT = 6,

        @SQL VARCHAR(100),

        @Error INT,

        @DatabaseName SYSNAME = 'Yoda'

 

-- Loop all files to the very end

WHILE 1 = 1

    BEGIN

        BEGIN TRY

            -- Build the SQL string to execute

            SET     @SQL = 'DBCC PAGE(' + QUOTENAME(@DatabaseName) + ', ' + CAST(@FileNum AS VARCHAR(50)) + ', '

                            + CAST(@PageNum AS VARCHAR(50)) + ', 3) WITH TABLERESULTS'

 

            -- Insert the DBCC output in the staging table

            INSERT  @Sample

                    (

                        Col1,

                        Col2,

                        Col3,

                        Col4

                    )

            EXEC    (@SQL)

 

            -- DCM pages exists at an interval

            SET    @PageNum += 511232

        END TRY

 

        BEGIN CATCH

            -- If error and first DCM page does not exist, all files are read

            IF @PageNum = 6

                BREAK

            ELSE

                -- If no more DCM, increase filenum and start over

                SELECT  @FileNum += 1,

                        @PageNum = 6

        END CATCH

    END

 

-- Delete all records not related to diff information

DELETE

FROM    @Sample

WHERE   Col1 NOT LIKE 'DIFF%'

 

-- Split the range

UPDATE  @Sample

SET     Col5 = PARSENAME(REPLACE(Col3, ' - ', '.'), 1),

        Col3 = PARSENAME(REPLACE(Col3, ' - ', '.'), 2)

 

-- Remove last paranthesis

UPDATE  @Sample

SET     Col3 = RTRIM(REPLACE(Col3, ')', '')),

        Col5 = RTRIM(REPLACE(Col5, ')', ''))

 

-- Remove initial information about filenum

UPDATE  @Sample

SET     Col3 = SUBSTRING(Col3, CHARINDEX(':', Col3) + 1, 8000),

        Col5 = SUBSTRING(Col5, CHARINDEX(':', Col5) + 1, 8000)

 

-- Prepare data outtake

;WITH cteSource(Changed, [PageCount])

AS (

    SELECT      Changed,

                SUM(COALESCE(ToPage, FromPage) - FromPage + 1) AS [PageCount]

    FROM        (

                    SELECT CAST(Col3 AS INT) AS FromPage,

                            CAST(NULLIF(Col5, '') AS INT) AS ToPage,

                            LTRIM(Col4) AS Changed

                    FROM    @Sample

                ) AS d

    GROUP BY    Changed

    WITH ROLLUP

)

-- Present the final result

SELECT  COALESCE(Changed, 'TOTAL PAGES') AS Changed,

        [PageCount],

        100.E * [PageCount] / SUM(CASE WHEN Changed IS NULL THEN 0 ELSE [PageCount] END) OVER () AS Percentage

FROM    cteSource

© SQL Team or respective owner