Trigger Powershell Command to Copy SQL Backups to Network Drive

Question

I've got two servers on a workgroup and I need to transfer my SQL Server 2008 Backups from the local drive of one to a network drive on another. Ideally, I'd like to have SQL call the Powershell script after it does the backup. Anyone have a script that does this? Is there a better way?

I tried to have SQL Backup straight to the network drive but it doesn't seem I can do that without them being on the same domain.

Answer 1

Powershell can certainly do that, but I find ROBOCOPY easier, just use the /MIR option to automatically purge older ones after creating a Maintenance cleanup plan on the SQL server to keep get rid of backups more than X days, and you'll always have copies on the netowrk drive;

You can set up the copy as a Windows Scheduled Task, or as a SQL Agent task via an Operating System command.

Answer 2

I use Powershell to invoke RichCopy. If you break your big files up into multiple chunks with a RAR utility, RichCopy can really haul bits.

Another approach is to use the PowerShell v2 BITS module, although I've not tried it yet. Start with "import-module filetransfer" and "get-help add-filetransfer –example"

As for SQL calling the script, try having it call everything explicitly:

%windir%\system32\windowspowershell\v1.0\powershell.exe -nologo -command "&{C:\path\to\Script.ps1}"