Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
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
up vote 3 down vote accepted

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).

share|improve this answer
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

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.

share|improve this answer
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

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.

share|improve this answer
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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.