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: 332
Filed under:
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.
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
