SQL Server Performance

Backup Device

Discussion in 'General DBA Questions' started by danny123, Dec 14, 2007.

  1. danny123 New Member

    hi there,
    In my backup device i have the full backup and 10 transaction log backups.
    Is it possible to delete the transaction log backups from the backup device and keep the full backup.
    Please advice.
  2. Luis Martin Moderator

    What recovery model do you have?
  3. danny123 New Member

    Thanks for the support!
    The recovery model is set to Full.
  4. techbabu303 New Member

    Why do want to delete tlog backups ?
  5. danny123 New Member

    What happened is i took the Full backup of database and then we want to get rid of the data for yr 2005.
    Now the transaction log backup all together with Full backup have taken so much of space that its we are getting short of space.
    Thats why we jus want to keep the full backup and not the transaction log backups.
  6. techbabu303 New Member

    In Our enviornament we ship TLog backups to backup servers geographiaclly situated more than 100 miles away where they are backup in tape drives.
    Have you create this backups in maintainence plans ?
  7. danny123 New Member

    Thanks for support!
    We are doing the same that backups are on tapes on some other server. But the database i am talking about here is just an log database for website hits. So we dont need the data for 2005 once we took the Full backup.
    Now i have the full backup as well as the transaction log backups on same device which i dont want. I want to get rid of only transaction logs from the backup devices .
    I dont know that if its even possible to delete only transaction log from device and keeping the full backup.
  8. satya Moderator

    Once you have the relevant databases full database backup then previous log backups will be redundant and you can drop them without any further doubts, if you are able to test this full backup by restoring on to the test server.
    Do not assume taking backup and storing on a location is sufficient, you should be able to test only after restoring the database onto the standby server to confirm the consistency of that backup file.
  9. danny123 New Member

    I cant take the full backup again as the data for 2005 is deleted after taking the full backup once.
    This is how it is
    --->Full backup
    --> Transaction Log Backup 1
    -> Transaction Log Backup 2
    -> Transaction Log Backup 3
    -> Transaction Log Backup 4
    Now i want to delete all tracnsation log backups and keep the Full backup on the backup device.
    Is it possible to delete only transaction log backups from backup deivce?

  10. satya Moderator

    Yes, without any issue and my suggestion is to ensure the data backup is not compromised.
    If they are defined with devices then you could us SP_DROPDEVICE, I think.
  11. danny123 New Member

    Thanks for the support!
    Can you please tell me the steps how to delete only those TLog backups from backup device as i dont see any option any where to select them and delete them.
  12. satya Moderator

  13. danny123 New Member

    But this way i will loose my Full backup also. I only want to delete the transaction logs backup from the backup device.
    And with INIT also over write the existing full backup which i dont want. I want to keep the Full Backup and delete the Transaction log Backups from the device.
  14. satya Moderator

    There is a confusion, run SP_HELPDEVICE and post here.
    Are you writing to the same device?
    Are you not writing to the different files?
  15. danny123 New Member

    Yes i am taking backup on the same device and there is only one backup device.
    On the same device i took the Full Backup, then i took the TLong backup on same device and now i want to get rid of TLog Backups and keep full backup.
  16. danny123 New Member

    I just need to know whether i can even do this or not?
  17. techbabu303 New Member

    Hi Staya/Danny,
    Iam confused on this , cant he simply delete the tlog backups from drive where it is stored ?
    Is there something iam missing here, if you could explain it would clear my curious mind.
  18. danny123 New Member

    On drive the backup device is saved as on single file I cant open it and delete the transaction log separatly.
    May be i can use some third party tool but other than that i dont see how can i open the backup device on the drive and delete the TLog only.
  19. techbabu303 New Member

    Hey Danny,
    Seems you have no other option...
    But can you do what staya asked you to do using the system procedure and post here, just for my understanding.

Share This Page