SQL backup using maintenanc plan

Hello,

SQL Server 2012.
I'd like to use maintenance plan and setup daily full backup for my databases.
Working fine but every day is creating new BAK file with date stamp. I'd like to change settings and have overwrite option ON so only one newest file exists and is overwrite every day.

How to do that ?

thanks

henry
henryk123Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
Since your backups have datestamp in their name, I bet you should create another maintenance task to delete the files older than 1 days from that location.
0
 
Steve WalesSenior Database AdministratorCommented:
Overwriting a previous back is a very bad idea.

Let's say your backup takes 30 minutes to complete and you start it at 1AM.

Between the time your backup starts at 1 AM and when it finishes at 1:30 AM you do not have any good backups of your database.  What happens if you have a critical failure between 1:00 and 1:30 ?  You have no backup to restore from.

Aneesh's advice above is sound.
0
 
Chris HConnect With a Mentor Infrastructure ManagerCommented:
I would delete the old and create a new using the SQL maintenance plan wizard.  Under Define Backup Tasks, you have the option to append or overwrite  "If backup file exist".

https://msdn.microsoft.com/en-us/library/ms191002.aspx

If backup files exist list
Specify how to handle existing backups. Select Append to add the new backups after any existing backups in the file or on the tape. Select Overwrite to remove the old content of a file or tape, and replace it with this new backup.

If I was deleting my daily backup, as mentioned above, I would make sure between creation and deletion, I was offloading the backup to another media source.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I'd like to change settings and have overwrite option ON so only one newest file exists and is overwrite every day.
Why you want to overwrite the last backup? Do you have any procedure to move the backups to another place?
Otherwise, what for you need backups if you will delete them in the very next day?
0
All Courses

From novice to tech pro — start learning today.