Post reply

SQL backup Script is appending instead of overwriting

  • August 11, 2016 at 1:58 pm

    #329184

    I have been running this Backup script for several years but yesterday I had to transfer the database to another server, so I also copied the backup script and tasks from the old server to the new server, but now the hourly backups are spending instead of over writing the destination file. The only thing that changed in the script was the location of the database. Here is the script I am using.

    BACKUP DATABASE [Cencon] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Cencon SQL Backup hourly.bak' WITH NOFORMAT, NOINIT, NAME = N'Cencon-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

    Any help would be appreciated

    Thank You

    Michael D. Winn

    mwinn@ftsius.com

  • August 11, 2016 at 2:04 pm

    #1895106

    it's an easy fix.

    change the NOINIT TO INIT

    that is append vs create/overwrite.

    mwinn (8/11/2016)

    I have been running this Backup script for several years but yesterday I had to transfer the database to another server, so I also copied the backup script and tasks from the old server to the new server, but now the hourly backups are spending instead of over writing the destination file. The only thing that changed in the script was the location of the database. Here is the script I am using.

    BACKUP DATABASE [Cencon] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Cencon SQL Backup hourly.bak' WITH NOFORMAT, NOINIT, NAME = N'Cencon-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10

    GO

    Any help would be appreciated

    Thank You

    Michael D. Winn

    mwinn@ftsius.com

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • August 11, 2016 at 2:11 pm

    #1895108

    Thanks, I will try that, not sure why it worked for all these years prior to yesterday though.

  • August 11, 2016 at 11:05 pm

    #1895156

    mwinn (8/11/2016)

    Thanks, I will try that, not sure why it worked for all these years prior to yesterday though.

    To be honest, it couldn't have UNLESS you changed the file name each time, which I strongly recommend, BTW.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply