1

I ran this script looking at the history of my backups for the past month.

select * from msdb.dbo.backupset order by backup_start_date

No problem, but if I want to go back to Jan 2020, there are no records.

So why does the Agent Log, with Log Type = 'DATABASE' go back further?

Thanks

Improve this question

1 Answer 1

Reset to default
2

The Agent Log is limited by the number of entries, not by a date. The default is 1000 rows, with 100 rows per job. You can see this via Right-click SQL Server Agent in SSMS -> Properties -> History.

So, if you've had fewer than that number of events since January, the history will go back further. You can change that limit in the dialog noted above.

Many DBAs will configure a job to purge backup history on a rolling month or two month basis, sometimes coordinated with how long backups are kept on local storage.

Improve this answer
3
  • When you said "archive", I think the penny dropped. I do fortnightly move of my backup files from the server to a NAS server, to free up space, as I don't want to keep 2 hourly full backups on the server (ISO requirement for us to do this and company policy to backup every 2 hours). So by moving the backups away from the server, then this is why the history cannot go that far back?
    – Fandango68
    May 23, 2020 at 12:10
  • You have a requirement for taking two backups of your database every hour? What does this achieve that daily full + hourly (or more often) transaction logs doesn't? Are you sure those ISO requirements have been interpreted correctly? But to answer the question, no, moving backup files off the local filesystem does not alter the history unless that move process is explicitly coded to do so.
    – alroc
    May 23, 2020 at 14:16
  • No. We do a FULL backup every 2 hours and transactional backups every 15mins. Diff backs are done once a week. It's a business critical system. If it fails, the most we've lost is 15mins
    – Fandango68
    May 25, 2020 at 1:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.