SQL Backup

Posted on 2014-08-08
Last Modified: 2014-08-08
Is it possible for the SQL backup file will be override each time rather than appending to the existing one ? Where can I modify this setting ?

Is it possible to send out an notification email after each backup ?

Question by:AXISHK
    LVL 32

    Expert Comment

    Right click on the backup task and change it..

    Sure. But you must correctly setup Database Mail first.

    Author Comment

    Can you show the setting through a screenshot ?

    How can the Database mail linked to my SQL Backup job ?

    LVL 14

    Accepted Solution


    You can use property window to change from Append to Overwrite
    as shown in the image

    and for mail notification you can configure DBMAIL as shown in below link

    Hope this will help
    LVL 32

    Expert Comment

    Please read the Database Mail documentation first. Otherwise even with a cooking receipt it's later hard to understand and modify.

    Author Comment

    Tks. How to link the job to a email alert ?

    Tks again.
    LVL 32

    Assisted Solution

    LVL 35

    Assisted Solution

    by:David Todd

    Take a look at what Ola Hallengren's script does. It adds the datetime etc to the backup filename, so each backup creates a separate unique file.

    I no longer write my own backup routines. Instead I use Ola's.


    PS What the others said about using Database mail, adding that to the SQL Agent profile, then adding operators and getting a notification on a job that fails.

    Brent Ozar's sp_Blitz checks for enabled scheduled jobs that don't have a failure notification.
    LVL 23

    Assisted Solution

    If you ever do a backup via SQL, add the "INIT" option (and make sure you don't have "NOINIT" set) and it will overwrite the previously existing backup.  I agree with others though - use Ola Hallengren's maintenance script - it is excellent.

    BACKUP DATABASE [AdventureWorks2008R2]
    	TO DISK = N'F:\Backup\AdventureWorks2008R2_FULL.bak'
    	, COPY_ONLY
    	, INIT
    	, NAME = N'AdventureWorks2008R2-Full Database Backup'
    	, SKIP
    	, STATS = 5

    Open in new window

    LVL 22

    Expert Comment

    by:Steve Wales
    Just be aware that if you are overwriting the previous backup ... and that's ALL you are doing, you are exposing yourself to a serious failure.

    Say you start your backup at 8PM and it takes 45 minutes.

    Each night you overwrite your backup.

    What happens if you have a critical failure at 8:30 PM?

    You've overwritten your last good backup and your current backup hasn't finished yet.

    Be aware of that.   You can set up maintenance plans to clean up old backups and set up your backups to write a new file very night with date and time appended (or use Ola Hallengren's scripts as a base as indicated by David Todd above).

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    746 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

    15 Experts available now in Live!

    Get 1:1 Help Now