Solved

SQL backup using maintenanc plan

Posted on 2015-01-22
4
94 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 250 total points
ID: 40565250
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
ID: 40565270
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
ID: 40565284
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 49

Expert Comment

by:Vitor Montalvão
ID: 40566046
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

756 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