When a DB was backed up last?

  • I am looking for a script that would:

    1) identify all Dbs in full mode and tell me if they all been backed up last night (or when they were backed up last)

    2) For all dbs that are not in simple mode check if trn backups run in last hour.

    Can anyone help please?

    We have separate maintenance plans for Dbs in simple mode and Dbs in full mode, so when a new db is added to the server we might forget to add it to the appropriate maintenance plan. So that is why i would like to have this script.

    thank you

  • look at these tables in msdb:

    backupfile,

    backupfilegroup

    backupmediafamily

    backupmediaset

    backupset

  • I have checked sys.databases for the list of databases and then looked for backup files in the file system. That way I can be sure the backup occurred and also check for file growth.

    Lots of scripts in the scripts section of the site dealing with backups.

  • Hi,

    thank you for your reply

    I have over 30 servers that i have to check every day, so i can not go to the backup drives and verify the backups manually. It will be very time consuming. I am looking for a script.

    thank you

  • The script you are looking for can be built from the information provided by the two gentlemen above.

    Use sys.databases for the database names and recovery. You will want to use msdb..backupset.backup_finish_date and type for the date of the last full/log backup.

    Kyle

  • Here's a snippet from a documentation script I have. Note - if a database has never been backed up, it won't appear in this list. That can be your job to figure out, good practice. 🙂

    [font="Courier New"]DECLARE @sql nvarchar(4000),

    @maxlen1 smallint,

    @maxlen2 smallint,

    @maxlen3 smallint

    IF EXISTS (SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#tmp_backups%')

    DROP TABLE #tmp_backups

    CREATE TABLE #tmp_backups

    (

    name sysname,

    backupfinishdate datetime,

    comment varchar(50),

    location nvarchar(260)

    )

    INSERT INTO #tmp_backups

    SELECT s.name,

    b.backup_finish_date,

    CASE WHEN b.backup_finish_date > DATEADD(dd,-1,getdate())

    THEN 'Last full backup was within the last 24 hours'

    WHEN b.backup_finish_date > DATEADD(dd,-7,getdate())

    THEN 'Last full backup was within the last week'

    ELSE '***** CHECK BACKUP!!! *****'

    END,

    bmf.physical_device_name

    FROM master.sys.databases s LEFT OUTER JOIN msdb..backupset b ON s.name = b.database_name

    INNER JOIN msdb..backupmediafamily bmf ON b.media_set_id = bmf.media_set_id

    WHERE s.name <> 'tempdb'

    AND b.backup_finish_date = (SELECT MAX(backup_finish_date)

    FROM msdb..backupset

    WHERE database_name = b.database_name

    AND type = 'D') -- full database backups only, not log backups

    ORDER BY s.name

    SELECT @maxlen1 = (MAX(LEN(RTRIM(COALESCE(name, 'NULL')))) + 2)

    FROM #tmp_backups

    SELECT @maxlen2 = (MAX(LEN(RTRIM(COALESCE(comment, 'NULL')))) + 2)

    FROM #tmp_backups

    SELECT @maxlen3 = (MAX(LEN(RTRIM(COALESCE(location, 'NULL')))) + 2)

    FROM #tmp_backups

    SET @sql = 'SELECT LEFT(name, ' + LTRIM(STR(@maxlen1)) + ') AS ''DB Name'', '

    SET @sql = @sql + 'CONVERT(char(21), backupfinishdate, 120) AS ''Backup End Date'', '

    SET @sql = @sql + 'LEFT(comment, ' + LTRIM(STR(@maxlen2)) + ') AS ''Comment'', '

    SET @sql = @sql + 'LEFT(location, ' + LTRIM(STR(@maxlen3)) + ') AS ''Last Backup Location'' '

    SET @sql = @sql + 'FROM #tmp_backups '

    SET @sql = @sql + 'ORDER BY name'

    EXEC (@sql)

    TRUNCATE TABLE #tmp_backups

    -- Last log backups

    INSERT INTO #tmp_backups

    SELECT s.name,

    b.backup_finish_date,

    CASE WHEN b.backup_finish_date > DATEADD(hh,-12,getdate())

    THEN 'Last log backup was within the last 12 hours'

    WHEN b.backup_finish_date > DATEADD(dd,-1,getdate())

    THEN 'Last log backup was within the last day'

    ELSE '***** CHECK BACKUP!!! *****'

    END,

    bmf.physical_device_name

    FROM master.sys.databases s LEFT OUTER JOIN msdb..backupset b ON s.name = b.database_name

    INNER JOIN msdb..backupmediafamily bmf ON b.media_set_id = bmf.media_set_id

    WHERE s.name <> 'tempdb'

    AND b.backup_finish_date = (SELECT MAX(backup_finish_date)

    FROM msdb..backupset

    WHERE database_name = b.database_name

    AND type = 'L') -- log database backups only, not full backups

    ORDER BY s.name

    SELECT @maxlen1 = (MAX(LEN(RTRIM(COALESCE(name, 'NULL')))) + 2)

    FROM #tmp_backups

    SELECT @maxlen2 = (MAX(LEN(RTRIM(COALESCE(comment, 'NULL')))) + 2)

    FROM #tmp_backups

    SELECT @maxlen3 = (MAX(LEN(RTRIM(COALESCE(location, 'NULL')))) + 2)

    FROM #tmp_backups

    SET @sql = 'SELECT LEFT(name, ' + LTRIM(STR(@maxlen1)) + ') AS ''DB Name'', '

    SET @sql = @sql + 'CONVERT(char(21), backupfinishdate, 120) AS ''Backup End Date'', '

    SET @sql = @sql + 'LEFT(comment, ' + LTRIM(STR(@maxlen2)) + ') AS ''Comment'', '

    SET @sql = @sql + 'LEFT(location, ' + LTRIM(STR(@maxlen3)) + ') AS ''Last Backup Location'' '

    SET @sql = @sql + 'FROM #tmp_backups '

    SET @sql = @sql + 'ORDER BY name'

    EXEC (@sql)

    DROP TABLE #tmp_backups

    GO

    [/font]

    NB - this will only work for SQL Server 2005. If you want something for SQL Server 2000, you've posted in the wrong forum. 😛



    Scott Duncan

    MARCUS. Why dost thou laugh? It fits not with this hour.
    TITUS. Why, I have not another tear to shed;
    --Titus Andronicus, William Shakespeare


  • If you are using SQL 2000 the following code should do

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_NULLS ON

    GO

    /**********************************************************

    sp_GetBackupInfo without a parameter will report on all databases for a given server

    sp_GetBackupInfo 'hgp' - name of specific single database to be reported

    This code found on Sql Server Central posted by someone whose name I have forgotten

    **********************************************************/

    Create procedure sp_GetBackupInfo

    @Name varchar(100) = '%'

    --with Encryption

    as

    set NoCount on

    declare

    @result int

    --try

    select

    (substring ( database_name, 1, 32)) as Database_Name,

    abs(DateDiff(day, GetDate(), backup_finish_date)) as DaysSinceBackup,

    backup_finish_date

    from msdb.dbo.backupset

    where Database_Name like @Name

    order by Database_Name, backup_finish_date desc

    --finally

    SuccessProc:

    return 0 /* success */

    --except

    ErrorProc:

    return 1 /* failure */

    --end

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • >We have separate maintenance plans for Dbs in simple mode and Dbs in full mode, so when a new db is added to the server we might forget to add it to the appropriate maintenance plan. So that is why i would like to have this script.

    Another strategy could be to check for the recovery model in the transaction log backup job. This way you could add new databases that are automatically backed up correctly.

    I have a stored procedure that you could help you with this.

    http://ola.hallengren.com/sql-server-backup.html

    Ola Hallengren

    http://ola.hallengren.com

  • Ok...this isn't exactly what you are asking, but it does tell you if a backup wasn't done. Just for clarity, I did not write this and it came from another SQL site. Hope this helps.

    -- GET DATABASES WITHOUT FULL OR TLOG BACKUPS

    -- Louis Nguyen 2008

    -- Excludes model and tempdb

    -- Will only display if a DB has not been backedup today (day 0) or yesterday (day 1)

    -- Disclaimer: This script, is provided for informational purposes only and

    -- SQL Server Community (aka: http://WWW.SQLCOMMUNITY.COM) or the author of this

    -- script makes no warranties, either express or implied. This script,

    -- scenarios and other external web site references, is subject to change

    -- without notice. The entire risk of the use or the results of the use of this

    -- script remains with the user.

    -- dbs with no full backups

    if exists (

    select *

    from master.dbo.sysdatabases a

    left join msdb.dbo.backupset b

    on a.[name] = b.database_name and datediff(day,b.backup_finish_date,getdate())<2

    and b.type='D'

    where a.[name] not in ('model','tempdb') and b.database_name is null

    ) begin

    select cast('no full backups' as char(75)) as msg, cast(a.[name] as char(25)) as db_nm

    from master.dbo.sysdatabases a

    left join msdb.dbo.backupset b

    on a.[name] = b.database_name and datediff(day,b.backup_finish_date,getdate())<2

    and b.type='D'

    where a.[name] not in ('model','tempdb') and b.database_name is null

    end

    -- dbs with no transaction log backup

    if exists (

    select *

    from

    (

    select [name] as database_name

    from master.dbo.sysdatabases

    where databasepropertyex([name], 'recovery') in ('FULL','BULK_LOGGED') and [name] not in ('model','tempdb')

    ) a

    left join msdb.dbo.backupset b

    on a.database_name = b.database_name and b.type='L' and datediff(day,b.backup_finish_date,getdate())<2

    where b.database_name is null

    ) begin

    select cast('no tlog backups' as char(75)) as msg, cast(a.database_name as char(25)) as db_nm

    from

    (

    select [name] as database_name

    from master.dbo.sysdatabases

    where databasepropertyex([name], 'recovery') in ('FULL','BULK_LOGGED') and [name] not in ('model','tempdb')

    ) a

    left join msdb.dbo.backupset b

    on a.database_name = b.database_name and b.type='L' and datediff(day,b.backup_finish_date,getdate())<2

    where b.database_name is null

    end

  • NB - this will only work for SQL Server 2005. If you want something for SQL Server 2000, you've posted in the wrong forum. 😛

    If change "master.sys.databases" to "master.dbo.sysdatabases", it seems work for SQL2000. Hope I am not missing anything here. 😉

  • Everybody! thank you very much for your help! I was able to do what i needed.

    thank you!

Viewing 11 posts - 1 through 10 (of 10 total)

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