Post reply

Vendor supplied system''s backup job

  • January 24, 2007 at 11:33 am

    #115913

    Here's the backup statement from a vendor supplied database.

    BACKUP DATABASE [PRODUCTION] TO DISK = @fullpath

    WITH INIT , NOUNLOAD , NAME = @name, NOSKIP , STATS = 10, NOFORMAT;

    @fullpath is a local disk, and @name is the database name + the current date. My question is about all the options. Most of them appear to be useless since the backup is going to disk, and each time this is run, the file name is different. Am I correct in this? Can this statement be rewritten to:

    BACKUP DATABASE [PRODUCTION] TO DISK = @fullpath WITH NAME = @name;

    and have the same effect? I don't care about printing messages as the DB is being backed up but like having the backup name in the backup table in msdb. Or am I completely misunderstanding the media header information?

    Thanks!

  • January 24, 2007 at 11:56 am

    #685304

    Tim,

    If you are writing to disk then you might want to keep the INIT command as this will cause it to overwrite rather than append to an existing backupset (file) on disk.

    NOUNLOAD, NOSKIP, NOFORMAT are all tape related so can be discarded. STATS is totally optional and doesnt change the backup.

    Hope this helps,

    - James

    --
    James Moore
    Red Gate Software Ltd

  • January 24, 2007 at 12:06 pm

    #685306

    Haha. Funny you should be the one to reply. I'm looking at their backup jobs with the intention of moving them to SQL Backup. Great product at a great price. 😀

    Anyway, when this command runs, the backup goes to a different file every time. Part of the filename variable is a datestamp. So in this case, wouldn't the init command have zero effect, too?

  • January 24, 2007 at 12:42 pm

    #685315

    Yes, it would have no effect. Some people add all of that code...just in case.

    It doesn't hurt anything.

    BTW- you can find out more about the syntax from the BOL (Books OnLine). It comes with SQL Server and can usually be found at:

    Start>Programs>Microsoft SQL Server>Books OnLine.

    Use the Index tab and enter BACKUP Database. Choose the Transact SQL option.

    -SQLBill

  • January 24, 2007 at 1:00 pm

    #685327

    Thanks for the confirmation. I'd read through the BOL documentation on all the options, but just wanted confirmation that I was reading it all correctly.

  • January 24, 2007 at 11:39 pm

    #685426

    If I am not mistaken all these option are automatically added when you create the backup using BACKUP wizard.

    May be they have created the backup job using backup wizard...

     

    MohammedU
    Microsoft SQL Server MVP

  • January 25, 2007 at 5:50 am

    #685486

    That may be possible. When the system first got here, I assumed that their people knew what they were doing with SQL Server and didn't bother to look at it carefully. The deeper I go, though, the more mistakes I find.

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

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