Overwrite old SQL Backups


I am using the following procedure to backup a database, with a scheduled task and sqlcmd. I also created a batch file that runs after the backup and deletes all files older than x amount of days. In the procedure, for full backups, I added the "with init" to delete all older backups, but it doesn't seem to work. When I run the backup, a new backup is created every time.

Could someone tell me what I need to change with the procedure?

Thanks, T

USE [AbraEmployeeSelfService]
/****** Object:  StoredProcedure [dbo].[sp_BackupDatabase]    Script Date: 07/07/2009 15:40:43 ******/

-- =============================================
-- Author: Edgewood Solutions
-- Create date: 2007-02-07
-- Description: Backup Database
-- Parameter1: databaseName
-- Parameter2: backupType F=full, D=differential, L=log
-- =============================================
ALTER PROCEDURE [dbo].[sp_BackupDatabase]  
       @databaseName sysname, @backupType CHAR(1)

       DECLARE @sqlCommand NVARCHAR(1000)
       DECLARE @dateTime NVARCHAR(20)

       SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','') +
       REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')  

       IF @backupType = 'F'
               SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +
               ' TO DISK = ''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\' + @databaseName + '_Full_' + @dateTime + '.BAK'' WITH INIT'
       IF @backupType = 'D'
               SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +
               ' TO DISK = ''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\' + @databaseName + '_Diff_' + @dateTime + '.BAK'' WITH DIFFERENTIAL'
       IF @backupType = 'L'
               SET @sqlCommand = 'BACKUP LOG ' + @databaseName +
               ' TO DISK = ''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\' + @databaseName + '_Log_' + @dateTime + '.TRN'''
       EXECUTE sp_executesql @sqlCommand
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Using SQL 2005 there is an easier way to do this. You can create a maintenance plan that will do the same. Open Management and right click Maintenance Plans. If it is a basic plan, like backups and transactionals, db maintenance, etc, try the wizard.
Streppa09Author Commented:
I am using SQL 2005 Express, in which the option doesn't appear to available in this version. My plan was get rid of the extra batch file to clean up the old DB backups, in hopes that the stored procedure could do both. I am positive that it can, but my expirence with SQL is lacking. If there is a better way to perform these functions, I am open to suggestions.

Any help would be appreciated.

Thanks, T
Geoff BryanDeveloperCommented:
If you include the current date in the filename of each backup, the Init option will not overwrite any older backup, because each time you run it it is using a different filename!

If you make the filename the same each time you do a full backup, the existing file will be overwritten.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vbgb is correct. The easiest way here is to save them as the same name thereby writing over them each time it's run.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.