1

I have set up a job within MSQL studio to backup all of my databases to a specific file. Then I take that file, compress it, and send it to a backup device. I am currently hoping to make this entire process automated on a weekly basis. However, here is my problem. The job I have created in MSQL Studio currently runs a full backup of the databases, however, it does not overwrite the old data. I have been through the wizard time and time again but can't figure out how to me the process overwrite the old files. The purpose of doing this is to save space on the server.

Can anyone help me figure out how to make the backup job overwrite old files?

Improve this question

3 Answers 3

Reset to default
3

If you're using the Maintenance Plan Wizard to create the job then once you click the Back up databases across one or more files option a if backup files exist: drop-down menu will be enabled. Set that menu to Overwrite and that should do it for you.

If you've already created the maintenance plan, open up the maintenance plan and edit the Backup Up Database task. You should see the same option that I described above.

If you're creating one file per database then there is no way to overwrite a pre-existing backup file using SQL native backup. New files will be created for each file. You'll have to script something or use a third party tool that supports this functionality.

Improve this answer
2
  • I am aware of that option. However that compiles all of my databases into one .bak file. I select the option Create a backup file for ever database and it doesn't allow me to overwrite old data. I was wondering if there is a way to manipulate the job through the modify option.
    – GMitch
    Jun 9, 2011 at 17:00
  • @Cpt. Jack See my edit.
    – squillman
    Jun 9, 2011 at 17:17
0

We have done something like this and we didn't want to override the only backup on the server until the process was finished. We created a backup job, then a job to zip the backup with gzip (sql 2005), and then removed the old gzip backup with forfiles. We have been doing this for some time with no issues.

forfiles guide http://technet.microsoft.com/en-us/library/cc753551%28WS.10%29.aspx

Improve this answer
0

Try T-SQL scripting BACKUP DATABASE statement http://msdn.microsoft.com/en-us/library/ms186865.aspx

BACKUP DATABASE SomeDB TO DISK = 'c:\backupdb.bak' WITH INIT; GO

This will backup a DB to a single file and overwrite all always.

Or maybe this free tool http://sqlbackupandftp.com/ - it creates a zip files and one can limit how many files to keep.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .