How dependent are backups on msdb tables like backupset?

Question

I am trying to get some practice making a backup maintenance plan and restoring those backups. I have a script that churns through the most recent full database backup and any subsequent transaction log backups, all of which are in a single appended backup file.

My question is in two parts. First, is it possible to determine everything I need to know to restore a database from my backup file itself, without querying the msdb backup tables? Second, If I was to accidentally run sp_delete_backuphistory and wipe out all history, how bad of a day would I have?

Bonus question: How do expiration dates work when I have a backup that I continually append to? Does the expired data get deleted on subsequent backups, or do I need to create a new backup file, or something else? If I was creating a new file for every backup, I could simply check that files expiration date and delete it, but I am more confused with the appended backup sets.

• Aaron Bertrand: Why are you using a single appended backup file? This just makes all kinds of things harder - the file will get bigger and bigger, so hard to move, also more cumbersome to delete backups within that file. Why not just have each backup represented as a separate file with a timestamp in its name? Cleanup etc. is so much easier to automate this way, using any tool/language.
• ses011: Yes, that's what I've realized. It would be nice if you could easily clean out expired backups from the appended file, but that probably requires a third party tool, if anything. I am going to try working with individual files.

Answer 1

If you have a single file with all of a set of backups in that single file, you should be able to use the command:

 RESTORE HEADER ONLY FROM backup device ...

This will provide you all of the details within that file, including the log file LSNs and other details you might need.

http://technet.microsoft.com/en-us/library/ms178536.aspx

The EXPIREDATE and the RETAINDAYS are just data that you include with the backup. You have to decide how to use that information. In an appended file, it is mystifying indeed. You could perhaps use the information to skip over backups that expired. (For me, each backup is a separate file.) See Tibor Karaszi's comments at:

http://sqlblog.com/blogs/tibor_karaszi/archive/2012/07/08/backup-and-the-evil-retaindays-option.aspx

• Hannah Vernon: Just for clarity, RESTORE DATABASE is not in any way dependent on anything stored in msdb. You can take a backup file from instance 'A' and restore it to instance 'B', assuming 'B' is not a lower SQL Server version, without issue.
• ses011: I think this clarifies it nicely. The expiration really isn't there for much other than bookkeeping and if I am using appended backups, the header information should give me everything I need. I think I will be leaning toward having individual files for each backup. Thanks!