Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
1  
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. –  Aaron Bertrand Mar 10 at 20:51
    
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. –  Spacemoses Mar 10 at 21:04

1 Answer 1

up vote 3 down vote accepted

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

share|improve this answer
    
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. –  Max Vernon Mar 10 at 20:39
    
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! –  Spacemoses Mar 10 at 21:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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