Solved

SQL backup using maintenanc plan

Posted on 2015-01-22
4
71 Views
Last Modified: 2015-02-16
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
Comment
Question by:henryk123
4 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 250 total points
Comment Utility
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
 
LVL 22

Expert Comment

by:Steve Wales
Comment Utility
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
 
LVL 16

Assisted Solution

by:choward16980
choward16980 earned 250 total points
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now