March 7, 2017 at 3:28 am
Hi all,
For a test I created a database, made a full backup, which after a while was followed by a differential backup.
Next I deleted the backup file and removed the backup information in the MSDB using sp_delete_backuphistory and sp_delete_database_backuphistory.
Next I still could create a differential backup. So my question: does anyone know where the information of the last full backup is stored?
Thanks for an answer in advance.
Kind regards,
Henk Maeghs
March 7, 2017 at 3:32 am
MSDB just contains the history about what backups were taken of what databases when. All databases know whether they've had a full backup, what full backup the diffs belong to and where the last log backup ended. That information is in the databases themselves. If it was in MSDB, then things like log shipping wouldn't work.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 7, 2017 at 3:46 am
GilaMonster - Tuesday, March 7, 2017 3:32 AMMSDB just contains the history about what backups were taken of what databases when. All databases know whether they've had a full backup, what full backup the diffs belong to and where the last log backup ended. That information is in the databases themselves. If it was in MSDB, then things like log shipping wouldn't work.
Thanks for your quick and helpful reply Gail.
Do you know where this information is stored? And can this information be retrieved / queried?
Regards,
Henk
March 7, 2017 at 3:54 am
In msdb, look at tables named "backup<<something>>", eg "backupset". This is where the information relating to backups is stored. It's all perfectly readable using normal T-SQL queries. There's MSDN documentation for the backup tables.
A simple query might be to see when the latest backups were done for databases:
USE msdb;
SELECT bs.database_name,
bs.type,
MAX(bs.backup_start_date)
FROM dbo.backupset AS bs
GROUP BY bs.database_name,
bs.type
ORDER BY type,
database_name;
Alternatively, you might want a more complex query that correlates the databases backed up with your scheduled backup jobs (link to a blog post I wrote)
Thomas Rushton
blog: https://thelonedba.wordpress.com
March 7, 2017 at 4:20 am
ThomasRushton - Tuesday, March 7, 2017 3:54 AMIn msdb, look at tables named "backup<<something>>", eg "backupset". This is where the information relating to backups is stored. It's all perfectly readable using normal T-SQL queries. There's MSDN documentation for the backup tables.A simple query might be to see when the latest backups were done for databases:
USE msdb;
SELECT bs.database_name,
bs.type,
MAX(bs.backup_start_date)
FROM dbo.backupset AS bs
GROUP BY bs.database_name,
bs.type
ORDER BY type,
database_name;Alternatively, you might want a more complex query that correlates the databases backed up with your scheduled backup jobs (link to a blog post I wrote)
Thanks Thomas,
In your reply you're assuming the information is present in the MSDB, but in my scenario I deleted all the info. Next I could still make a differential backup (strange because I presumed there was no information anymore about the full backup), so the information about the full backup must be stored in the database itself (instead of only the MSDB) as indicated by Gail.
So my question is still: where in the database is this information stored?
Regards,
Henk
March 7, 2017 at 4:42 am
It's in the database header pages, and it's not accessible.
Deleting info from MSDB and still being able to take a differential backup should not be a surprise. If the data about backups was only in msdb, log shipping wouldn't work (the secondary wouldn't know anything about where the primary's last backup left off), a database mirroring failover would break backup chains (it doesn't) and the offloaded backups of availability groups wouldn't work at all.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 7, 2017 at 4:53 am
GilaMonster - Tuesday, March 7, 2017 4:42 AMIt's in the database header pages, and it's not accessible.Deleting info from MSDB and still being able to take a differential backup should not be a surprise. If the data about backups was only in msdb, log shipping wouldn't work (the secondary wouldn't know anything about where the primary's last backup left off), a database mirroring failover would break backup chains (it doesn't) and the offloaded backups of availability groups wouldn't work at all.
Ok Gail, clear answer. Thanks for your help.
Regards,
Henk Maeghs
March 7, 2017 at 5:17 am
You can see some of the information by running:
DBCC DBINFO( <<dbname>> ) WITH tableresults
That'll show (among other things) last backup & last log backup date/time (which may help you find the file), but no information about where the backup file was.
Thomas Rushton
blog: https://thelonedba.wordpress.com
March 8, 2017 at 9:33 pm
And you can certainly run RESTORE HEADERONLY from disk='disk file location' and RESTORE FILELISTONLY FROM disk='path to disk' to get some information about the database backup from the backup files themselves.
March 9, 2017 at 12:54 am
dbaduck - Wednesday, March 8, 2017 9:33 PMAnd you can certainly run RESTORE HEADERONLY from disk='disk file location' and RESTORE FILELISTONLY FROM disk='path to disk' to get some information about the database backup from the backup files themselves.
Ok guys, thanks all for your help.
Henk
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply