BAckup history not showing into MSDB

  • Hello,

    We had an strange issue that our sql server 2012 Sharepoint Databases backup daily taken by 3rd party tool and from last few days we couldn't see the entry into MSDB database when we query the tables but when i checked the event log, it shows for all the database backup history.

    Could you please guide me that how can i know that why i can't see the backup history?

    It's started just few days back as no backup messages for those databases?

    Even when i query the backup history tables, i can see only few days back backup.

     

    Thanks for your help!

  • what happens if you run a query like this in msdb?

    SELECT bs.backup_set_id, bs.database_name, CASE bs.type WHEN 'D' THEN 'Database' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Log' WHEN 'F' THEN 'Filegroup' WHEN 'G' THEN 'Diff Filegroup' ELSE bs.type END AS backup_type,
    bs.is_copy_only AS is_copy, bs.backup_start_date, bs.backup_finish_date, DateDiff(minute, bs.backup_start_date, bs.backup_finish_date) AS backup_min, bs.name, bs.description,
    CASE mf.device_type WHEN 2 THEN 'disk' WHEN 5 THEN 'tape' WHEN 7 THEN 'virtual' WHEN 9 THEN 'Azure' ELSE 'other' END AS device_type,
    mf.physical_device_name, bs.user_name, bs.backup_size, bs.compressed_backup_size, bs.first_lsn, bs.last_lsn, bs.checkpoint_lsn, bs.database_backup_lsn
    FROM msdb.dbo.backupset bs
    INNER JOIN msdb.dbo.backupmediafamily mf ON bs.media_set_id = mf.media_set_id
    WHERE bs.database_name = 'mydatabasename'
    ORDER BY bs.backup_set_id desc
  • Thanks Chris.

    I was running similar kind of query and it was showing similar results.

    I ran your query and it's showing same results which has no backup but when i checked into event log and it shows database has been backed up.

    Not sure it's deleting history from the MSDB right away? Not sure what happening and how i can check?

     

    Thanks for your help!

  • IIRC, you posted about this on another forum.  And, if that was you, you posted that you had backup history but only up to and including the 6th of December  and then nothing after that.  My recommendation stays pretty much the same... you need to check the directory(ies) where the backups are stored and see what's happening.  You might also want to check the job that does the backups and see if it's been failing.  Depending on how it's written, it may not indicate a failure and that's why you have to check to see if you have BAK and TRN files wherever the backups are being stored AND, if they are there, test them to make sure they're actually viable.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks Jeff.

    I think it's really good idea to check the shared location and validate the files.

    Appreciate your suggestions.

     

  • Thanks Chris.

    It's same results, still missing backup for those days.

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

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