SQl Express daily backup-overwrite exisitng file

With the help of expert Vitor Montalvão, I was able to set up a daily backup of a SQL Express 2012 database.

I now need to know if there is a way to have the daily backup overwrite the existing backup. It seems that the SQL command is appending the backup file since each day it is doubling in size.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Personally, I'd move the backup file to a second fixed name file.
Everyday, you move the new backup file to the second file.
You always have a current backup, and a second one in case something went wrong the previous day. Use a simple batch file for this.
Éric MoreauSenior .Net ConsultantCommented:
Have you ever looked at what others have done to automate this task? I have been using https://community.landesk.com/support/docs/DOC-6103 for years and I am very satisfied with all the options.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, I also wouldn't overwrite the existing file. If you do so you can only go back one day. Imagine that you want to restore the database from 2 or 3 days before?
Anyway if you want to go with this you just need to add WITH INIT keyword to the end of the BACKUP statement:
BACKUP DATABASE Hunt_Vision TO DISK='C:\Hunt_Vision-Backup\Hunt_Vision.bak' WITH INIT

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

cja-tech-guyAuthor Commented:
The daily backup gets copied to tape each night so I would be able to go back 7 days or to a monthly copy, if necessary.   I will add the new statement and test it.  Do I need to change anything in the Task Scheduler set up that runs it?


Vitor MontalvãoMSSQL Senior EngineerCommented:
The daily backup gets copied to tape each night so I would be able to go back 7 days or to a monthly copy
Great. This is how it should be done.

Do I need to change anything in the Task Scheduler set up that runs it?
No. Add the WITH INIT and save the script. Next time will run and overwrite the existing file.
cja-tech-guyAuthor Commented:
I just ran the task manually and the backup is only 3 GB as it should be.  I will see how it works tonight when it runs itself.  I will reopen the question if necessary.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Ok. The WITH INIT means that the backup file need to be initialized each time the backup runs.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.