Deleting expired transaction log backups in SQL 2005

• Timothy Ford

  SSC Enthusiast

  Points: 134

  More actions

  September 29, 2006 at 1:25 pm

  #170300

  MODERATOR, I SEE THIS IS IN THE WRONG FORUM.  I'LL RE-POST UNDER THE DBA FORUM.  YOU CAN REMOVE THIS POST.

  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. 

  Any help is appreciated!

  Tim Ford

   

• EugeneZ-162636

  Default port

  Points: 1448

  More actions

  September 30, 2006 at 11:47 am

  #663504

  try RETAINDAYS

  or backup log as part of Mainteneces plan where you can specify when to delete the old backup files too

   

  BACKUP

  LOG [Northwind] TO

  DISK

  = N'C:\Program Files\Microsoft SQL Server\BACKUP\Northwind.bak'

  WITH

  RETAINDAYS = 1, NOFORMAT, NOINIT,

  NAME

  = N'Northwind-Transaction Log Backup',

  SKIP

  , NOREWIND, NOUNLOAD, STATS = 10

  GO