BAckup history not showing into MSDB

Question

BAckup history not showing into MSDB

pdsqsql

Old Hand

Points: 358
More actions
December 13, 2019 at 5:44 pm

#3707744

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!

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

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

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

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

pdsqsql Old Hand Points: 358 More actions · Answer 2

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!

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 3

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)

pdsqsql Old Hand Points: 358 More actions · Answer 4

Thanks Jeff.

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

Appreciate your suggestions.

pdsqsql Old Hand Points: 358 More actions · Answer 5

Thanks Chris.

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