Options
ERASEFILES dosen't erase files...
David Smith
Posts: 13
With the below script, I backup one of our databases, it mirrors the backup to a standby server and also copies to a backup file server. I got tired of cleaning out the old copies on the backup server and after re-reading the help file discovered the FILEOPTIONS parameter, however instead of actualy cleaning up the files at the local, standby and backup servers it now doesn't erase old files at all. A slight variant is backing up multiple database and specifying two COPYTO locations, with one COPYTO , the FILEOPTIONS parameter was being honoured but with two it isn't.
EXECUTE master..sqlbackup N'-SQL "BACKUP DATABASE [db1]
TO DISK = ''F:\sqlbackup\db1\db1_db_<DATETIME yyyymmddhhnnss>.sqb''
WITH INIT, COMPRESSION = 2, THREADCOUNT = 2,
ERASEFILES_ATSTART = 12h, FILEOPTIONS = 7,
MIRRORFILE = ''\\mirrorserver\shipping\SQL2005-1\DB1\db1_db_<DATETIME yyyymmddhhnnss>.sqb'',
COPYTO = ''\\backupserver\sql2005-1\db1\'',
MAILTO = ''davids@locahost'',
MAILTO_ONERROR=''servers@localhost'',
VERIFY"'
SQL Backup log file
21/08/2007 8:00:00 a.m.: Backing up db1 (full database) to:
21/08/2007 8:00:00 a.m.: F:\sqlbackup\db1\db1_db_20070821080000.sqb
21/08/2007 8:00:00 a.m.: \\mirrorserver\shipping\SQL2005-1\db1\db1_db_20070821080000.sqb
21/08/2007 8:00:00 a.m.: BACKUP DATABASE [db1] TO DISK = 'F:\sqlbackup\db1\db1_db_<DATETIME yyyymmddhhnnss>.sqb' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', INIT, MIRRORFILE = '\\mirrorserver\shipping\SQL2005-1\db1\db1_db_<DATETIME yyyymmddhhnnss>.sqb', VERIFY, ERASEFILES = 12h, MAILTO = 'davids@localhost', MAILTO_ONERROR = 'servers@localhost', COPYTO = '\\backupserver\sql2005-1\db1\', FILEOPTIONS = 7, COMPRESSION = 2, THREADCOUNT = 2
21/08/2007 8:00:05 a.m.: Database size : 36.250 MB
21/08/2007 8:00:05 a.m.: Compressed data size: 9.706 MB
21/08/2007 8:00:05 a.m.: Compression rate : 73.23%
Processed 3224 pages for database 'db1', file 'db1_Data' on file 1.
Processed 2 pages for database 'db1', file 'db1_Log' on file 1.
BACKUP DATABASE successfully processed 3226 pages in 3.426 seconds (7.713 MB/sec).
21/08/2007 8:00:36 a.m.:
21/08/2007 8:00:36 a.m.: Validating files:
21/08/2007 8:00:36 a.m.: F:\sqlbackup\db1\db1_db_20070821080000.sqb
21/08/2007 8:00:36 a.m.:
21/08/2007 8:00:36 a.m.: RESTORE VERIFYONLY FROM DISK = 'F:\sqlbackup\db1\db1_db_20070821080000.sqb'
The backup set on file 1 is valid.
21/08/2007 8:00:36 a.m.: Copied F:\sqlbackup\db1\db1_db_20070821080000.sqb to \\backupserver\sql2005-1\db1\db1_db_20070821080000.sqb.
21/08/2007 8:00:36 a.m.: SQL Backup process ended.
21/08/2007 8:00:36 a.m.: Mail sent successfully to: davids@localhost
EXECUTE master..sqlbackup N'-SQL "BACKUP DATABASE [db1]
TO DISK = ''F:\sqlbackup\db1\db1_db_<DATETIME yyyymmddhhnnss>.sqb''
WITH INIT, COMPRESSION = 2, THREADCOUNT = 2,
ERASEFILES_ATSTART = 12h, FILEOPTIONS = 7,
MIRRORFILE = ''\\mirrorserver\shipping\SQL2005-1\DB1\db1_db_<DATETIME yyyymmddhhnnss>.sqb'',
COPYTO = ''\\backupserver\sql2005-1\db1\'',
MAILTO = ''davids@locahost'',
MAILTO_ONERROR=''servers@localhost'',
VERIFY"'
SQL Backup log file
21/08/2007 8:00:00 a.m.: Backing up db1 (full database) to:
21/08/2007 8:00:00 a.m.: F:\sqlbackup\db1\db1_db_20070821080000.sqb
21/08/2007 8:00:00 a.m.: \\mirrorserver\shipping\SQL2005-1\db1\db1_db_20070821080000.sqb
21/08/2007 8:00:00 a.m.: BACKUP DATABASE [db1] TO DISK = 'F:\sqlbackup\db1\db1_db_<DATETIME yyyymmddhhnnss>.sqb' WITH NAME = '<AUTO>', DESCRIPTION = '<AUTO>', INIT, MIRRORFILE = '\\mirrorserver\shipping\SQL2005-1\db1\db1_db_<DATETIME yyyymmddhhnnss>.sqb', VERIFY, ERASEFILES = 12h, MAILTO = 'davids@localhost', MAILTO_ONERROR = 'servers@localhost', COPYTO = '\\backupserver\sql2005-1\db1\', FILEOPTIONS = 7, COMPRESSION = 2, THREADCOUNT = 2
21/08/2007 8:00:05 a.m.: Database size : 36.250 MB
21/08/2007 8:00:05 a.m.: Compressed data size: 9.706 MB
21/08/2007 8:00:05 a.m.: Compression rate : 73.23%
Processed 3224 pages for database 'db1', file 'db1_Data' on file 1.
Processed 2 pages for database 'db1', file 'db1_Log' on file 1.
BACKUP DATABASE successfully processed 3226 pages in 3.426 seconds (7.713 MB/sec).
21/08/2007 8:00:36 a.m.:
21/08/2007 8:00:36 a.m.: Validating files:
21/08/2007 8:00:36 a.m.: F:\sqlbackup\db1\db1_db_20070821080000.sqb
21/08/2007 8:00:36 a.m.:
21/08/2007 8:00:36 a.m.: RESTORE VERIFYONLY FROM DISK = 'F:\sqlbackup\db1\db1_db_20070821080000.sqb'
The backup set on file 1 is valid.
21/08/2007 8:00:36 a.m.: Copied F:\sqlbackup\db1\db1_db_20070821080000.sqb to \\backupserver\sql2005-1\db1\db1_db_20070821080000.sqb.
21/08/2007 8:00:36 a.m.: SQL Backup process ended.
21/08/2007 8:00:36 a.m.: Mail sent successfully to: davids@localhost
Comments
FILEOPTIONS=7 includes the option to only delete copied backups when the archive bit is clear. If you look at your backup folder in Explorer and turn on the list view, this should show an A on all files that have the archive bit set; these will not be deleted.
You mat want to set FILEOPTIONS=5 to ignore checking the archive bit.
Thanks
David