Powershell – Trigger Powershell Command to Copy SQL Backups to Network Drive

backuppowershellsql-server-2008

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.

Best Answer

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.