Home / server / Questions / 278826
Accepted
GMitch
Asked: 2011-06-10 08:43:58 +0800 CST

Microsoft SQL Server Management Studio

  • 772

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?

sql-server backup overwrite

3 Answers

  1. Best Answer

    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.

    • 3

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

    • 0

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

    • 0