What are the challenges you face when working across database platforms? Take the survey
Options

Erase Files not working

spiderspider Posts: 3
edited October 20, 2005 4:58AM in SQL Backup Previous Versions
Hi Guys

I have the following script to backup all user databases, I can not get it to erase the old backup files, they are now 2 days old, the user has permission to delete the files, I'm sure its something silly, can someone point me in the right direction please. It just keeps reporting backup file exists.

SET NOCOUNT ON

DECLARE @dbname sysname
DECLARE @backupstring varchar(1000)
DECLARE backup_cursor CURSOR FOR
SELECT name
FROM sysdatabases
WHERE dbid > 4

OPEN backup_cursor

FETCH NEXT FROM backup_cursor
INTO @dbname

@FETCH_STATUS = 0
BEGIN
set @backupstring = '-SQL "BACKUP DATABASE TO DISK = ''d:\backuprg\'+ @dbname +'.sqb '' " ERASEFILES_ATSTART = 1'
exec master..sqlbackup @backupstring
FETCH NEXT FROM backup_cursor
INTO @dbname
END

CLOSE backup_cursor
DEALLOCATE backup_cursor

Comments

  • Options
    peteypetey Posts: 2,358 New member
    If you are using a version < 3.2.0.5, there is a bug with the ERASEFILES option, in that it compares the old backup files timestamps with the current GMT time, instead of the local date time. So if your timezone is GMT -x, then the files are not deleted when they should be.

    Contact support for the patched version.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    I finally worked it out, I was missing the WITH NAME switch before the INIT or ERASEFILES. This script works with all the User Databases.

    SET NOCOUNT ON

    DECLARE @dbname sysname
    DECLARE @backupstring varchar(1000)
    DECLARE backup_cursor CURSOR FOR
    SELECT name
    FROM sysdatabases
    WHERE dbid > 4

    OPEN backup_cursor

    FETCH NEXT FROM backup_cursor
    INTO @dbname

    @FETCH_STATUS = 0
    BEGIN
    set @backupstring = '-SQL "BACKUP DATABASE TO DISK = ''e:\backup\'+ @dbname +'.sqb'' WITH NAME = '''+ @dbname +''', INIT, COMPRESSION = 1" -E'
    exec master..sqlbackup @backupstring
    FETCH NEXT FROM backup_cursor
    INTO @dbname
    END

    CLOSE backup_cursor
    DEALLOCATE backup_cursor
Sign In or Register to comment.