Strange behavior with backups

Question

I am noticing a strange behavior with backups. The scenario goes like this:

1.  I create a new database, call it test_db.

2.  I set the recovery model to full.

3.  If I try to create a transaction log backup, it obviously fails because a transaction log backup requires a valid full backup to be on hand.

4.  I create a full backup

5.  I can now create a transaction log backup

6.  I use exec sp_delete_database_backuphistory @database_name = 'test_db'.  This completes with no errors, and querying the msdb database confirms that the backup history has been removed for test_db.

7.  At this point, I can still create a transaction log backup.  Without a full backup available, this should fail, but does not.  Does anyone know why this would be?

Answer 1

The backup history in MSDB is only to help you find your backups and do restores.  It does not actually control whether you can take a transaction log backup.  For instance if you moved the database to another server after taking the full backup, log backups need to still work.

When you take the first full backup after switching the recovery model to FULL, the change is recorded in the database.

David

---

David http://blogs.msdn.com/b/dbrowne/

Answer 2

I don't know how or who told you that backup history has any relevance to actual backup that takes place. msdb is only repository which stores the successful backup information it is has nothing to do with what type of backup can happen.

---

Cheers,

Shashank

Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

My TechNet Wiki Articles
MVP