0

I need to create a batch file that will purge data from an existing file. Basically SQL server management studios does not overwrite backup data; therefore after the backups have been run and the data transferred, I need to purge the file using a batch job.

What command can I use within a batch job to delete the content of an existing file?

GMitch
  • 500
  • 4
  • 12
  • Would it be easier to rename the file? Then delete the file after X days/weeks? Forfiles does a good job at deleting files after a certain time. – Nixphoe Jun 14 '11 at 16:35

3 Answers3

3

You should definitely use Maintenance Plan functionality to do your backups. You can easily configure it to create new backup file for each run (it will append full date & time to the end of file name, e.g. mydb_backup_201106080006.bak).

One of the possible tasks of Maintenance plan is Maintenance Cleanup Task that is used to delete unwanted *.bak & *.trn (or any other) files. The accuracy (time interval that defines if file can be deleted) is hour/day/week/month/year.

You can insert Execute T-SQL Statement Task to be run before such Maintenance Cleanup Task. There (in Execute T-SQL Statement Task) you execute external command to copy/compress your backups (e.g. EXEC master.dbo.xp_cmdshell 'C:\path\to\compress.bat).

I'm doing this myself on our servers where SQL Server 2005 is still used (using WinRAR to compress). SQL Server 2008 has support for compressing backup files build-in (WinRAR does better compression but takes considerably longer time).

LazyOne
  • 3,064
  • 1
  • 18
  • 16
  • BRILLIANT!!! I didn't even think to run a maintenance plan clean up task. Thanks for the help. – GMitch Jun 14 '11 at 17:33
0

If you are backing up to a file device, on the database backup task screen, options tab there is a radio to select "Overwrite all existing backup sets." This will overwrite the backup each time it is run.

Jeremy
  • 338
  • 2
  • 12
  • Yes you are correct however this collaboratively stores all the database files under one .bak file, not individually, therefore rendering that option disabled – GMitch Jun 14 '11 at 17:18
  • Sorry I do not follow that. Are you saying you do not want a single backup file? If you do a files backup you can still choose the option "Backup to a new media set, and erase all existing backup sets". Probably I am really not understanding your question though. – Jeremy Jun 14 '11 at 17:33
  • Yes I did a poor job of explaining. I have around 30 databases and I want a .bak backup for each of them. If I select **Back up database across one or more files** I can overwrite the data. However this puts all of my databases into one .bak file. Therefore I chose **Create a backup file for every databse** In selecting this option I don't have the ability to overwrite old files. – GMitch Jun 14 '11 at 17:43
0

How are you backing up the data? Does your backup job have the ability to execute a post job script? If that's the case you can have a simple .cmd with a delete command that deletes all of the files in the directory that end with the backup extension.

Otherwise if your backups are done by a certain time you could just use Windows task scheduler to run the delete batch file, however I think the key thing here is to ensure that your backup completed successfully before you delete the files.

David Yu
  • 1,032
  • 7
  • 14
  • I have SQLMS running a daily backup of all databases to a specific file. Then Backup utility sends that folder to our storage server and gets zipped up. I will be using a windows task scheduler to run the batch job to delete the files after transfer. All the files end in .bak so would it be safe to assume I could use a command to delete all files ending with .bak? – GMitch Jun 14 '11 at 16:43
  • @Cpt. Jack: Yes it would. – joeqwerty Jun 14 '11 at 17:04