check backups dbs

  • nba ninetytwo

    Valued Member

    Points: 65

    Hi all!

    i need a script to check the result (success or failed) of the backups dbs in sql server 2005.

    I would like to schedule it, and to put the result in a table or a file.

    thanks for your help!

  • Adam Haines

    SSC-Insane

    Points: 23197

    This is a script I pulled from this site some time ago. It displays databases with no backup history and also displays the history of the databases that do have history. It will tell you the end data of the backup and where or not the file exists.

    /*

    Server Backup History Report

    (Full DB Backups only)

    1. lists all databases with no backup history

    2. lists last backup for other databases

    includes Date, User, Size, Duration, Age, Finish Date & Location

    Includes system databases

    Excludes TempDB

    Excludes backup history data where backupmediafamily.device_type = 7

    these are typically created by Veritas BackupExec

    Tested on SQL Server 2000 (SP3)

    Brett Ivery 24/03/2006 created

    11/04/2006 added file existence check

    */

    SET nocount ON

    -- drop temp proc if exists

    IF object_id('tempdb..#prFileExists') IS NOT NULL

    DROP PROCEDURE #prFileExists

    GO

    -- START create temp proc to check backup file existence

    CREATE PROCEDURE dbo.#prFileExists

    @path varchar(300) ,

    @p2 int OUTPUT

    AS BEGIN

    /*

    DECLARE @EXISTS bit

    EXECUTE dbo.#prFileExists 'c:\boot.ini', @EXISTS OUTPUT

    SELECT @EXISTS AS [EXISTS]

    */

    DECLARE @RES varchar(500)

    DECLARE @EXEC varchar(1000)

    IF object_id('tempdb..#cmdshell') IS NOT NULL

    DROP TABLE #cmdshell

    CREATE TABLE #cmdshell (

    res varchar (100)

    )

    SET @EXEC = 'IF exist "' + @path + '" (echo Found) ELSE (echo NOT Found)'

    INSERT INTO #cmdshell (res)

    EXEC master..xp_cmdshell @EXEC

    DELETE #cmdshell WHERE res IS NULL

    SET @RES = (SELECT res FROM #cmdshell)

    IF @RES = 'Found'

    SET @p2 = 1

    ELSE

    SET @p2 = 0

    END

    GO

    -- END create temp proc to check backup file existence

    --Declare variables and temp table for existing backup data

    DECLARE @i int

    DECLARE @EXISTS bit

    DECLARE @location varchar(260)

    SET @i = 0

    DECLARE @Tmp TABLE (

    [ID] [int] identity(1,1) NOT NULL,

    [FileExists] bit DEFAULT 0,

    [DBName] [varchar] (30) NULL ,

    [UserName] [varchar] (30) NULL ,

    [BackupSize] [varchar] (13) NULL ,

    [Duration] [varchar] (10) NULL ,

    [BackupAge] [int] NULL ,

    [FinishDate] [varchar] (20) NULL ,

    [Location] [varchar] (260) NULL ,

    [device_type] [tinyint] NULL

    )

    -- return data about databases with no backup history

    PRINT ' ================================================================================================================================'

    PRINT ' ' + @@servername + ' - Database Backup History (SQL Backups only)'

    PRINT ' '

    if exists(

    SELECT

    DB.Name

    FROM

    master..sysdatabases DB

    left join

    (

    select database_name

    from msdb..BACKUPSET BS

    join msdb..backupmediaset MS

    on

    BS.media_set_id = MS.media_set_id

    join msdb..backupmediafamily MF

    on

    BS.media_set_id = MF.media_set_id

    WHERE

    type = 'D'

    and mf.device_type <> 7

    group by database_name

    ) BS

    on BS.database_name = DB.name

    where

    BS.Database_name is null

    and

    not DB.Name = 'TempDB'

    )

    SELECT

    DB.Name as [Databases With No Backup History]

    FROM

    master..sysdatabases DB

    left join

    (

    select database_name

    from msdb..BACKUPSET BS

    join msdb..backupmediaset MS

    on

    BS.media_set_id = MS.media_set_id

    join msdb..backupmediafamily MF

    on

    BS.media_set_id = MF.media_set_id

    WHERE

    type = 'D'

    and mf.device_type <> 7

    group by database_name

    ) BS

    on BS.database_name = DB.name

    where

    BS.Database_name is null

    and

    not DB.Name = 'TempDB'

    else

    SELECT

    '- None -' as [Databases With No Backup History]

    -- get existing backup history data

    -- (into table variable for later modification)

    PRINT ' Databases With Backup History'

    PRINT ' -------------------------------- '

    INSERT @Tmp(

    [DBName]

    , [UserName]

    , [BackupSize]

    , [Duration]

    , [BackupAge]

    , [FinishDate]

    , [Location]

    , [device_type]

    )

    SELECT

    cast(database_name AS varchar(30)) AS [DBName],

    cast(user_name AS varchar(30)) AS [UserName],

    str(cast(backup_size AS decimal(20,2)) / 1048576 ,10,2) + ' MB' AS [BackupSize],

    cast(datediff(n,backup_start_date,backup_finish_date) AS varchar(5)) + ' min.' AS [Duration],

    cast(datediff(dd,backup_finish_date,Getdate()) AS varchar(10)) AS [BackupAge],

    convert(varchar(20),backup_finish_date) AS [FinishDate],

    physical_device_name AS [Location],

    mf.device_type

    FROM

    master..sysdatabases DB

    JOIN

    msdb..BACKUPSET BS

    ON DB.name = BS.database_name

    JOIN msdb..backupmediaset MS

    ON

    BS.media_set_id = MS.media_set_id

    JOIN msdb..backupmediafamily MF

    ON

    BS.media_set_id = MF.media_set_id

    JOIN

    (

    SELECT

    max(backup_set_id) AS backup_set_id

    FROM

    msdb..BACKUPSET BS

    JOIN msdb..backupmediaset MS

    ON

    BS.media_set_id = MS.media_set_id

    JOIN msdb..backupmediafamily MF

    ON

    BS.media_set_id = MF.media_set_id

    WHERE

    type = 'D'

    AND mf.device_type <> 7

    GROUP BY database_name

    ) MaxBackup

    ON

    BS.backup_set_id = MaxBackup.backup_set_id

    WHERE

    type = 'D'

    -- loop through the results and update the FileExists field

    -- (calling temp proc for each row)

    SELECT @i = min(ID) FROM @Tmp WHERE ID > @i

    WHILE @i IS NOT NULL BEGIN

    IF @i IS NOT NULL BEGIN

    -- PRINT cast(@i AS varchar(20))

    SET @location = (SELECT location FROM @Tmp WHERE ID = @i)

    EXECUTE #prFileExists @location, @EXISTS OUTPUT

    UPDATE @Tmp SET FileExists = @EXISTS WHERE ID = @i

    END

    SELECT @i = min(ID) FROM @Tmp WHERE ID > @i

    END

    -- return the results

    SELECT

    [DBName]

    , [UserName]

    , [BackupSize]

    , [Duration]

    , [BackupAge] AS [BackupAge (Days)]

    , [FinishDate]

    , [FileExists]

    , [Location]

    FROM

    @Tmp

    ORDER BY

    [BackupAge] DESC

    -- drop the temp proc

    IF object_id('tempdb..#prFileExists') IS NOT NULL

    DROP PROCEDURE #prFileExists

    GO

    PRINT ' ================================================================================================================================'

  • Jorge Segarra

    SSCrazy

    Points: 2561

    You can right click on the Job that runs the backup and go to properties. On the left hand side choose Notifications. There you can set SQL server to email you when the job fails/succeeds or finishes (of which the email will tell you result of the job). By default SQL Server already writes to the Application event log when the job fails. If you want it to email you you need to set up operators and configure Database mail.

    =============================================================
    /* Backups are worthless, Restores are priceless */

    Get your learn on at SQL University!
    Follow me on Twitter | Connect on LinkedIn
    My blog: http://sqlchicken.com
    My book: Pro Server 2008 Policy-Based Management

  • colin.Leversuch-Roberts

    SSC Guru

    Points: 52551

    "The only good backup is one that has been sucessfully restored" If you want to check backups restore them, absolutely nothing else guarantees a backup.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

  • Adam Haines

    SSC-Insane

    Points: 23197

    Collin has a very good point here. The only sure fire way to determine if a backup is "good" is to restore; however, you can also add some additional processing to the script to aid in checking the validity of a backup. You can tell whether or not a backup is valid by using the RESTORE VERIFYONLY option. DISCLAIMER: Just because SQL says the backup is valid it does not mean that you are 100% safe; it simple means the basic check criteria were met.

    e.g.

    RESTORE VERIFYONLY

    FROM DISK = N'C:\test.bak'

    More info on RESTORE VERIFYONLY

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/cba3b6a0-b48e-4c94-812b-5b3cbb408bd6.htm

  • nba ninetytwo

    Valued Member

    Points: 65

    i will do the tests!

    thanks to all!!

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply