SQL 2005, creating a backup job for a database

Hi everyone, I am having some problems getting an existing backup job to run  on our MS SQL 2005 server.

The job starts and finishes with no errors, but instead of replacing the existing backup data, it adds to it. Being a fairly large database, it can fill the disk space up within a week, which is not what is needed.

The backup statement is here:

USE [msdb]
GO
/****** Object:  Job [K1 Full Backup]    Script Date: 09/21/2006 10:32:13 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [Database Maintenance]    Script Date: 09/21/2006 10:32:13 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'Database Maintenance' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'Database Maintenance'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'K1 Full Backup',
            @enabled=1,
            @notify_level_eventlog=0,
            @notify_level_email=0,
            @notify_level_netsend=0,
            @notify_level_page=0,
            @delete_level=0,
            @description=N'Full backup of the K1 Database',
            @category_name=N'Database Maintenance',
            @owner_login_name=N'WARWYN\Administrator', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Database Backup]    Script Date: 09/21/2006 10:32:13 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Database Backup',
            @step_id=1,
            @cmdexec_success_code=0,
            @on_success_action=1,
            @on_success_step_id=0,
            @on_fail_action=2,
            @on_fail_step_id=0,
            @retry_attempts=0,
            @retry_interval=0,
            @os_run_priority=0, @subsystem=N'TSQL',
            @command=N'BACKUP DATABASE K1
TO DISK = ''E:\backup\k1backup.bak''
exec init',
            @database_name=N'K1',
            @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'K1 Backup',
            @enabled=1,
            @freq_type=4,
            @freq_interval=1,
            @freq_subday_type=1,
            @freq_subday_interval=0,
            @freq_relative_interval=0,
            @freq_recurrence_factor=0,
            @active_start_date=20060602,
            @active_end_date=99991231,
            @active_start_time=180000,
            @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
      IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

The Exec init', statement i entered myself, in the GUI part of editing the job.

I have the question to ask of getting the full backup overwriting the existing backup (and not appending to it), and succesfully modifying this job (or making a new job), i am not sure how to save this and get it used?

Any help would be great!Start Free Trial