Vendor supplied system''s backup job

Question

Vendor supplied system''s backup job

Tim Coker

SSC Eights!

Points: 825
More actions
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!

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

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

James Moore (Red Gate) SSCrazy Points: 2644 More actions · Answer 1

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

Tim Coker SSC Eights! Points: 825 More actions · Answer 2

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?

SQLBill SSC Guru Points: 51440 More actions · Answer 3

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

Tim Coker SSC Eights! Points: 825 More actions · Answer 4

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.

MohammedU SSCoach Points: 19644 More actions · Answer 5

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

Tim Coker SSC Eights! Points: 825 More actions · Answer 6

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.