Log Backups in SQL 2005: Removing old Backup Files

• Timothy Ford

  SSC Enthusiast

  Points: 134

  More actions

  September 29, 2006 at 1:34 pm

  #170524

  I am attempting to create a backup model as follows in SQL 2005 on a database under Full Recovery model:

  1. Full data backup to backup device @ 0600 daily.

  2. Log backup every X minutes daily, deleting backups older than 25 hours.

  I am accomplishing #1 via the following command:

  BACKUP DATABASE [Foo] TO  [Foo_Device] WITH NOFORMAT, INIT,  SKIP, NOREWIND, NOUNLOAD,  STATS = 10

  I need to know the preferred method for accomplishing the corresponding log backup identified in #2.  Do I write to the same backup device?  A different backup device?  Write .trn files to a subfolder?  I could do this in SSIS using the Maintenance Cleanup Task, but I am going to have to set this up for many databases on this server all writing backups to their own subfolders in the backup directory; essentially ruling-out that option.

  Any help is appreciated!

  Tim Ford

• Aaron Ingold

  SSCrazy Eights

  Points: 9070

  More actions

  September 29, 2006 at 1:37 pm

  #663450

  Tim-

  I showed a technique for handling this situation in the following article. Adapt to your needs:

  http://www.sqlservercentral.com/columnists/aingold/workingaround2005maintenanceplans.asp

  Hope this helps.

• Timothy Ford

  SSC Enthusiast

  Points: 134

  More actions

  September 29, 2006 at 1:47 pm

  #663455

  Thanks Aaron, this is right up my alley.  I can append this functionality into the database I use to compile metadata off of all my SQL Servers.

  - Tim