SQL Server Performance

Compound Back up file with INIT

Discussion in 'SQL Server 2005 General DBA Questions' started by Muhammadk, Feb 22, 2008.

  1. Muhammadk New Member

    Morning Gurus,
    Here is my question and I hope that it make sense. I have a back up device created that points to a .bak file. I want to take back up to that device and I want to retain it for 3 days. So basically I want to have 3 days worth of backups in the same file. If I use INIT it overwrites it and if I use NOINIT it just keeps adding the backups past 3 days. Here is the TSQL I am using.

    use "DBNAME"

    BACKUP DATABASE ["DBNAME"]
    TO ["BackupDeviceName"]
    WITH RETAINDAYS = 3, init
    Is there anyway to do it except Maintenance plan?
    Thanks in advance for your help
    Muhammad
  2. MohammedU New Member

    What do you mean by except maintenance plan?
    Why do you want you append the backup to the same file? In my experience it is better to create new file each time with datetime prefix or rename the file before backup.
    You can make use xp_delete_file file to delete old files which is used my MP.
    Ex: declare @Dt_DateAndTime Varchar(100), @backupfile sysname, DECLARE @ThreeDaysAgo VARCHAR(50)
    select @Dt_DateAndTime = convert(char(8), getdate(), 112)+''+ Replace(convert(char(12), getdate(), 14),':','')
    select @Dt_DateAndTime
    select @backupfile = '\ServerNamec$ackupFilename'+@Dt_DateAndTime+'.bak'
    backup database <dbname>
    to disk = @backupfile
    with init
    SELECT @ThreeDaysAgo = CAST(DATEADD(d, -3, GETDATE()) AS VARCHAR)
    EXECUTE master.dbo.xp_delete_file 0,N'D:Backupsdb',N'bak',@ThreeDaysAgo


  3. Muhammadk New Member

    Thanks Mohammed for your response. I have a maintenance plan that does exactly the same thing. I wanted to use a back up device and cause backup device point to a file all the backup goes to the same file and the file keep growing. Is there any way to clean up that file i.e deleting backups that are older then x days from that one particular file?
    The reason to use backup device? Easier to manage.
    Thanks
    Muhammad
  4. ykram511 New Member

    Hi,
    I'm facing the same requirement but unable to find the solution. I was wondering if you ever got an answer, somehow, somewhere? :)
  5. satya Moderator

Share This Page