Streppa09
asked on
Overwrite old SQL Backups
Hello,
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]
GO
/****** Object: StoredProcedure [dbo].[sp_BackupDatabase] Script Date: 07/07/2009 15:40:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ========================== ========== =========
-- 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)
AS
BEGIN
SET NOCOUNT ON;
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\Backu p\' + @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\Backu p\' + @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\Backu p\' + @databaseName + '_Log_' + @dateTime + '.TRN'''
EXECUTE sp_executesql @sqlCommand
END
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]
GO
/****** Object: StoredProcedure [dbo].[sp_BackupDatabase] Script Date: 07/07/2009 15:40:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ==========================
-- 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)
AS
BEGIN
SET NOCOUNT ON;
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\Backu
IF @backupType = 'D'
SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +
' TO DISK = ''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backu
IF @backupType = 'L'
SET @sqlCommand = 'BACKUP LOG ' + @databaseName +
' TO DISK = ''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backu
EXECUTE sp_executesql @sqlCommand
END
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.
ASKER
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
Any help would be appreciated.
Thanks, T
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
vbgb is correct. The easiest way here is to save them as the same name thereby writing over them each time it's run.