0

I noticed that alot of backup jobs are scheduled using SQL Server 2008 R2, but we have a system here at work that also requires the backup of a directory in addition to the database contents. I've written a batch file that calls sqlcmd and calls a TSQL script to backup the database; the script also includes copying the directory and logging the result of the operations.

(And yes, I probably should have used powershell instead...)

I would use the SQL Server Job functionality as it has email notification of failures, event loggins and other perks that my batch file does not; but I don't see a way to work copying the directory into that in a all or nothing approach; so is it possible to work copying a directory into an SQL Server Agent Job?

  • You could use EXEC xp_cmdshell to execute the copy commands. – Max Vernon Oct 8 '14 at 16:10
  • Since these two operations can't be 100% synchronized anyway, why not perform the backups using SQL Server Agent, and back up the folder using PowerShell / C# etc.? You can make the latter as robust as you want, but copying a folder should be fairly straightforward... – Aaron Bertrand Oct 8 '14 at 16:30
1

Even though you can use xp_cmdshell to copy the directory, I would not recommend to do it using SQL Server.

You were right in thinking of using PowerShell and/or robocopy to copy files or directory to wherever you want. Powershell will even allow you to send email once the task is completed.

Currently, in my company, we use native sql server backups with compression and then have powershell scripts kick off the move to backup server on a different schedule.

  • Is it easy to connect powershell to an Office 365 email system? Surely there's some kind of scriptlet email client for that. – leeand00 Oct 8 '14 at 17:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.