msde tsql backup question

  • I'm using a tsql script to create a job and schedule it to auto backup the databases.

    It's appending the file each day but I want it to create a new BAK file each day. I thought the WITH INIT would do that. But maybe my syntax is wrong?

    How can I either get it to overlay the BAK file instead of append, or put a time stamp into the name of the BAK file?  Thanks for any ideas.

    This is what I'm using as part of a sql script:

    USE msdb

    EXEC sp_add_jobstep @job_name = 'mybackupjob',

    @step_name = 'Backup BESMgmt Data',

    @subsystem = 'TSQL',

    @command = 'backup database BESMgmt to disk = ''c:\Backups\BESMgmt_db.BAK'' with INIT',

    @on_success_action = 3'

    @retry_attempts = 5'

    @retry_interval = 5

    go

     

     

     

     

     

     

  • You need to set the ExpireDate/RetainDays.  From Backup in BOL:

    If EXPIREDATE or RETAINDAYS is not specified, expiration is determined by the media retention configuration setting of sp_configure. These options only prevent SQL Server from overwriting a file. Tapes can be erased using other methods, and disk files can be deleted through the operating system. For more information about expiration verification, see SKIP and FORMAT in this topic.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • I don't see a clear example of the syntax to do this. Would the command be like this:

    @command = 'backup database BESM to disk = 'c:\Backups\BESM_db.BAK' WITH INIT, RETAINDAYS = 2',

     

    Thanks!

  • Yes, however, if you wish to overwrite your backup file each day, you will need to set your RETAINDAYS to zero or 1.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • Hello! Iam wondering how i can make a script some wold take one tabeln in row tod do a backup in a database and save it to a device

    like A: och C: but the size should be defined to exampel 40mb or something.

    I hope for answer !

  • Hasan,

    You should create a new post for your question.  It is more likely to get answered in its own post since it is a new question.

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Viewing 6 posts - 1 through 5 (of 5 total)

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