Post reply

backups

  • June 15, 2017 at 12:22 am

    #322054

    I have this below query which gives last backup date , I would also like to know DB's (offline,failed) which have not been backed up.
    How do I do this.I believe a left join with sysdatabases table should help but cant get this to work.Appreciate any help.

    select M.Server,M.database_name,M.last_db_backup_date,M.backup_size,M.physical_device_name from
    (

    SELECT
     A.[Server],
     A.database_name,
     A.last_db_backup_date,
     --B.backup_start_date,
     --B.expiration_date,
     B.backup_size,
     --B.logical_device_name,
     B.physical_device_name 
     --B.backupset_name,
     --B.description
    FROM
     (
     SELECT 
       CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
       msdb.dbo.backupset.database_name,
       MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
     FROM  msdb.dbo.backupmediafamily
       INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
     WHERE msdb..backupset.type = 'D'
     GROUP BY
       msdb.dbo.backupset.database_name
     ) AS A
     
     LEFT JOIN

     (
     SELECT 
     CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
     msdb.dbo.backupset.database_name,
     msdb.dbo.backupset.backup_start_date,
     msdb.dbo.backupset.backup_finish_date,
     msdb.dbo.backupset.expiration_date,
     msdb.dbo.backupset.backup_size,
     msdb.dbo.backupmediafamily.logical_device_name,
     msdb.dbo.backupmediafamily.physical_device_name, 
     msdb.dbo.backupset.name AS backupset_name,
     msdb.dbo.backupset.description
    FROM msdb.dbo.backupmediafamily
     INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
    WHERE msdb..backupset.type = 'D'
     ) AS B
     ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]
    )M

  • June 15, 2017 at 2:00 am

    #1946881

    If the database's backup is missing then you would need to RIGHT OUTER JOIN the sys.databases
    😎

    select
      M.Server
     ,SDB.name
     ,M.database_name
     ,M.last_db_backup_date
     ,M.backup_size
     ,M.physical_device_name
    from
    (

    SELECT
    A.[Server],
    A.database_name,
    A.last_db_backup_date,
    --B.backup_start_date,
    --B.expiration_date,
    B.backup_size,
    --B.logical_device_name,
    B.physical_device_name
    --B.backupset_name,
    --B.description
    FROM
    (
    SELECT
    CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
    msdb.dbo.backupset.database_name,
    MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
    FROM msdb.dbo.backupmediafamily
    INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
    WHERE msdb..backupset.type = 'D'
    GROUP BY
    msdb.dbo.backupset.database_name
    ) AS A
    LEFT JOIN

    (
    SELECT
    CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
    msdb.dbo.backupset.database_name,
    msdb.dbo.backupset.backup_start_date,
    msdb.dbo.backupset.backup_finish_date,
    msdb.dbo.backupset.expiration_date,
    msdb.dbo.backupset.backup_size,
    msdb.dbo.backupmediafamily.logical_device_name,
    msdb.dbo.backupmediafamily.physical_device_name,
    msdb.dbo.backupset.name AS backupset_name,
    msdb.dbo.backupset.description
    FROM msdb.dbo.backupmediafamily
    INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
    WHERE msdb..backupset.type = 'D'
    ) AS B
    ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]
    )M

    RIGHT OUTER JOIN sys.databases SDB
    ON SDB.name = M.database_name

  • June 15, 2017 at 6:41 am

    #1946918

    Further on the subject, if the validity of the backups isn't tested by restoring then there is a potential risk, how do you validate that the backups can actually be restored successfully? My suggestion would be to automate the restore of each backup and log the results, then in addition, do header only restore of the content of the backup folders, this will give a true picture of the available backups and they're coverage.
    😎

  • June 15, 2017 at 11:36 am

    #1947005

    Eirikur Eiriksson - Thursday, June 15, 2017 2:00 AM

    If the database's backup is missing then you would need to RIGHT OUTER JOIN the sys.databases
    😎

    select
      M.Server
     ,SDB.name
     ,M.database_name
     ,M.last_db_backup_date
     ,M.backup_size
     ,M.physical_device_name
    from
    (

    SELECT
    A.[Server],
    A.database_name,
    A.last_db_backup_date,
    --B.backup_start_date,
    --B.expiration_date,
    B.backup_size,
    --B.logical_device_name,
    B.physical_device_name
    --B.backupset_name,
    --B.description
    FROM
    (
    SELECT
    CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
    msdb.dbo.backupset.database_name,
    MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
    FROM msdb.dbo.backupmediafamily
    INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
    WHERE msdb..backupset.type = 'D'
    GROUP BY
    msdb.dbo.backupset.database_name
    ) AS A
    LEFT JOIN

    (
    SELECT
    CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
    msdb.dbo.backupset.database_name,
    msdb.dbo.backupset.backup_start_date,
    msdb.dbo.backupset.backup_finish_date,
    msdb.dbo.backupset.expiration_date,
    msdb.dbo.backupset.backup_size,
    msdb.dbo.backupmediafamily.logical_device_name,
    msdb.dbo.backupmediafamily.physical_device_name,
    msdb.dbo.backupset.name AS backupset_name,
    msdb.dbo.backupset.description
    FROM msdb.dbo.backupmediafamily
    INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
    WHERE msdb..backupset.type = 'D'
    ) AS B
    ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]
    )M

    RIGHT OUTER JOIN sys.databases SDB
    ON SDB.name = M.database_name

    Te query gives NULL for servernames for rows/dbs where there is no backup.I need the servername to also show up and the rest of the row can be null

Viewing 4 posts - 1 through 3 (of 3 total)

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