Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

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

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

Print | posted on Thursday, October 21, 2010 4:34 PM | Filed Under [ SQL Server 2008 Algorithms Administration ]

Feedback

Gravatar

# re: How to determine if you should use incremental or differential backup?

Don't you mean FULL or differential?
10/22/2010 9:47 PM | Simon Sabin
Gravatar

# re: How to determine if you should use incremental or differential backup?

I sure do. Will change title now...
Thank you Simon.
10/22/2010 11:26 PM | Peso
Gravatar

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

Saw this in Steve's Database Weekly. Your script is using the wrong iterator - should be 511232 not 510232. And it will stop on the first offline file instead of going to the next online one. I suspect it will stop when it hits a log file too. See www.sqlskills.com/... for how to only iterate over online data files.

Thanks
10/23/2010 9:43 AM | Paul S. Randa;
Gravatar

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

Wow! Your vesion is much better. Of course!
10/24/2010 8:59 AM | Peso
Gravatar

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

So what is your decision point?
Is the intent to use this in a scheduled task that takes either a full or diff backup based on the percent changed?
10/25/2010 5:54 PM | Bob
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET