Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL backup using maintenanc plan

Posted on 2015-01-22
4
Medium Priority
?
116 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 1000 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 23

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 1000 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 52

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

730 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