Comments
2 comments
-
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. -
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
Add comment
Please sign in to leave a comment.
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