Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 124
  • Last Modified:

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
0
henryk123
Asked:
henryk123
2 Solutions
 
Aneesh RetnakaranDatabase 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 HIT DirectorCommented:
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now