Not deleting old backup files???

Question

Post reply

Not deleting old backup files???

AAkula

SSCertifiable

Points: 5394
More actions
October 22, 2008 at 11:22 am

#124105

Hi,
Iam using the following stored proc to take the backups full,diff and log. and also deleting the previous day's backup file. But the previous day's backup file is not deleting. could you plz advice me if there is any error in the code???
Iam executing the procedure like...EXEC dbo.Backup 'Categories','M:\Microsoft SQL Server\MSSQL.6\MSSQL\Backup\','Full'
GO
the result Iam getting is
Full_BackupPath_of_The_Database M:\Microsoft SQL Server\MSSQL.6\MSSQL\Backup\Categories_10-22-2008.bak
(1 rows(s) affected)
13 percent processed. [SQLSTATE 01000]
22 percent processed. [SQLSTATE 01000]
31 percent processed. [SQLSTATE 01000]
40 percent processed. [SQLSTATE 01000]
54 percent processed. [SQLSTATE 01000]
63 percent processed. [SQLSTATE 01000]
72 percent processed. [SQLSTATE 01000]
81 percent processed. [SQLSTATE 01000]
90 percent processed. [SQLSTATE 01000]
Processed 176 pages for database 'Categories', file 'Categories' on file 1. [SQLSTATE 01000]
100 percent processed. [SQLSTATE 01000]
Processed 1 pages for database 'Categories', file 'Categories_log' on file 1. [SQLSTATE 01000]
BACKUP DATABASE successfully processed 177 pages in 0.480 seconds (3.020 MB/sec). [SQLSTATE 01000]
The backup set on file 1 is valid. [SQLSTATE 01000] DEL M:\Microsoft SQL Server\MSSQL.6\MSSQL\Backup\Categories_10-21-2008.bak
(1 rows(s) affected)
output
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
The system cannot find the path specified.
(null)
(2 rows(s) affected)
But the database Categories_10-21-2008.bak exist in the path M:\Microsoft SQL Server\MSSQL.6\MSSQL\Backup, but it is not deleting? could you plz advice me
Procedure
USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[Backup]
@DBName VARCHAR(100),
@BackupPath VARCHAR(2000),
@BackupType VARCHAR(4) = 'FULL'
AS
BEGIN
DECLARE @BackupName VARCHAR(255), @lp_Backup VARCHAR(255)
DECLARE @Prefix VARCHAR(50)
DECLARE @backupSetId AS INT
SET @lp_Backup = @BackupPath
IF NOT EXISTS (SELECT 1 FROM master.dbo.sysdatabases WHERE name = @DBName)
BEGIN
SELECT 'Database name does not exist'
RETURN(1)
END
IF @DBName = 'tempdb'
BEGIN
SELECT 'tempdb cannot be backedup'
RETURN(1)
END
IF @BackupType NOT IN ('FULL', 'LOG', 'DIFF')
BEGIN
PRINT 'Invalid type of Backup selected, only FULL and LOG backup is allowed'
RETURN(1)
END
IF RIGHT(@BackupPath,1) <> '\'
SET @BackupPath = @BackupPath + '\'
IF @BackupType = 'FULL'
BEGIN
SET @BackupName = @DBName + ' Full Backup'
SET @BackupPath = @BackupPath + @DBName +'_'+ CONVERT(VARCHAR(10),GETDATE(),110)+'.bak'
SELECT @BackupPath AS Full_BackupPath_of_The_Database
BACKUP DATABASE @DBName
TO DISK = @BackupPath
WITH NOFORMAT, NOINIT, NAME = @BackupName,
SKIP, NOREWIND, NOUNLOAD, STATS = 10
SELECT @backupSetId = position FROM msdb..backupset WHERE database_name= @DBName
AND backup_set_id=(SELECT MAX(backup_set_id) FROM msdb..backupset
WHERE database_name = @DBName )
IF @backupSetId IS NULL
BEGIN
RAISERROR (N'Verify failed for database', 16, 1)
END
RESTORE VERIFYONLY
FROM DISK = @BackupPath
WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
END
IF @BackupType = 'DIFF'
BEGIN
SET @BackupName = @DBName + ' Differential Backup'
SELECT @Prefix = CONVERT(VARCHAR(50), GETDATE(),120)
SELECT @Prefix = REPLACE(@Prefix, '-', '')
SELECT @Prefix = REPLACE(@Prefix, ':', '')
SELECT @Prefix = REPLACE(@Prefix, ' ', '')
SET @BackupPath = @BackupPath + @DBName + '_' + @Prefix +'.bak'
SELECT @BackupPath AS Diff_Backup_Path
BACKUP DATABASE @DBName
TO DISK = @BackupPath
WITH DIFFERENTIAL ,NOFORMAT, INIT, NAME = @BackupName,
SKIP, NOREWIND, NOUNLOAD, STATS = 10
SELECT @backupSetId = position FROM msdb..backupset WHERE database_name= @DBName
AND backup_set_id=(SELECT MAX(backup_set_id) FROM msdb..backupset
WHERE database_name = @DBName )
IF @backupSetId IS NULL
BEGIN
RAISERROR (N'Verify failed for database', 16, 1)
END
RESTORE VERIFYONLY
FROM DISK = @BackupPath
WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
END
IF @BackupType = 'LOG'
BEGIN
SELECT @Prefix = CONVERT(VARCHAR(50), GETDATE(),120)
SELECT @Prefix = REPLACE(@Prefix, '-', '')
SELECT @Prefix = REPLACE(@Prefix, ':', '')
SELECT @Prefix = REPLACE(@Prefix, ' ', '')
SET @BackupName = @DBName + ' Log Backup'
SET @BackupPath = @BackupPath + @DBName + '_Log' + @Prefix + '.trn'
SELECT @BackupPath AS Log_Backup_Path
BACKUP log @DBName
TO DISK = @BackupPath
WITH NOFORMAT, NOINIT, NAME = @BackupName,
SKIP, NOREWIND, NOUNLOAD, STATS = 10
SELECT @backupSetId = position FROM msdb..backupset WHERE database_name= @DBName
AND backup_set_id=(SELECT MAX(backup_set_id) FROM msdb..backupset
WHERE database_name = @DBName )
IF @backupSetId IS NULL
BEGIN
RAISERROR (N'Verify failed for database', 16, 1)
END
RESTORE VERIFYONLY
FROM DISK = @BackupPath
WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
END
-- DELETE OLD FULL backup.
IF @BackupType = 'FULL'
BEGIN
SET @BackupPath ='DEL ' + @lp_Backup + @DBName +'_'+ CONVERT(VARCHAR(10),GETDATE()-1,110)+'.bak'
SELECT @BackupPath
EXECUTE xp_cmdshell @BackupPath
END
IF @BackupType = 'DIFF'
BEGIN
-- DELELE OLD DIFF Files
SELECT @Prefix = CONVERT(VARCHAR(50), GETDATE()-1,120)
SELECT @Prefix = REPLACE(@Prefix, '-', '')
SELECT @Prefix = REPLACE(@Prefix, ':', '')
SELECT @Prefix = REPLACE(@Prefix, ' ', '')
SELECT @Prefix = LEFT(@Prefix,8)
SET @BackupPath = 'DEL ' + @lp_Backup + @DBName + '_' + @Prefix + '*.bak'
SELECT @BackupPath AS DEL_Diff_BackupPAth
EXECUTE xp_cmdshell @BackupPath
END
IF @BackupType = 'LOG'
BEGIN
-- DELELE OLD LOG Files
SELECT @Prefix = CONVERT(VARCHAR(50), GETDATE()-1,120)
SELECT @Prefix = REPLACE(@Prefix, '-', '')
SELECT @Prefix = REPLACE(@Prefix, ':', '')
SELECT @Prefix = REPLACE(@Prefix, ' ', '')
SELECT @Prefix = LEFT(@Prefix,8)
SET @BackupPath = 'DEL ' + @lp_Backup + @DBName + '_Log' + @Prefix + '*.trn'
SELECT @BackupPath AS LOGPath
EXECUTE xp_cmdshell @BackupPath
END
RETURN(0)
END

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLBOT SSCrazy Eights Points: 8014 More actions · Answer 1

try running

xp_cmdshell 'DEL M:\Microsoft SQL Server\MSSQL.6\MSSQL\Backup\Categories_10-21-2008.bak'

and you'll get your answer.

(hint: no spaces in dos file paths)

~BOT

Craig Outcalt

Tips for new DBAs: http://www.sqlservercentral.com/articles/Career/64632
My other articles: http://www.sqlservercentral.com/Authors/Articles/Craig_Outcalt/560258

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 2

You need to double quote your filepath\filename if it contains spaces:

DEL "M:\Microsoft SQL Server\MSSQL.6\MSSQL\Backup\Categories_10-21-2008.bak"

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

Specific to your exact stored procedure it would be changing:

SET @BackupPath = 'DEL ' + @lp_Backup + @DBName + '_Log' + @Prefix + '*.trn'

to be:

SET @BackupPath = 'DEL "' + @lp_Backup + @DBName + '_Log' + @Prefix + '*.trn"'

Of course my question would be why you would open xp_cmdshell up for a process like this. That is a significant security risk for something that can be much better done as a separate step in a job calling a vbscript or even using a CLR procedure to delete the file. At least then the actual level of external access is limited to the role of the CLR procedure where as xp_cmdshell is wide open for exploit.

AAkula SSCertifiable Points: 5394 More actions · Answer 4

Thanks Jonathan,

I would like to use vb script to delete old backup files. Could plz give me some sample vb script that can delete old backups and how can I use that script in sqlAgent jobs? I attached a screen shot, could you plz tell me which option I need to choose?

Thanks

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 5

There is a script on this article:

http://www.sqlservercentral.com/articles/Administering/usingvbscripttoautomatetasks/1171/

I use a Operating System (CmdExec) step to call the script. If you save the script as DeleteOldBackups.vbs, then the Command for the step would be:

cscript "DeleteOldBackups.vbs"

I modified/extended the one above for specifically deleteing backups in the same folder as the script. The changed code can be found:

http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=ExpressMaintenance&referringTitle=ExpressBackups#Cleanup