Executing batch file from sql server job

Question

I want to create backup job on sql server. And i want to execute batch file in job. I just wonder the part of executing batch file from sql job. Do you have any idea?

Any help would appreciated.

use MyDb
go

BACKUP DATABASE MyDb TO DISK = 'C:\BackUps\MyDb.bak' WITH differential 
go

-- Call my batch file (which will zip MyDb.bak file)

Answer 1

If you do this is as a SQL Server Agent job (from SSMS) it makes calling batch files a lot easier. Just add a step of type "Operating System (CmdExec)" and point it to your .bat file.

Answer 2

If you're using besides sql server 2008 enterprise (or above) then xp_cmdshell is the way to do it, although you'll have to enable it first with sp_configure.

If you are lucky enough to be on mssql 2008 enterprise then the backup compression feature is what you should be using.

You may want to do something a bit more advanced then calling a static.bat file. Here are some useful samples:
Sample sql script to zip and transfer database backup file
How to take SQL database backup in .zip format