2

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)
uzay95
  • 161
  • 1
  • 5

2 Answers2

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.

Chris_K
  • 3,444
  • 6
  • 43
  • 45
0

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

Community
  • 1
Nick Kavadias
  • 10,796
  • 7
  • 37
  • 47