Automate copying sql bak files to network Share

Hello Everyone,

I am running a maintenance job to backup all my databases to the c: drive.  I want to be able to then run a job to copy the bak file to a network share.  Does anyone know of a script or process to backup the bak file to a network share?


Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PadawanDBAOperational DBACommented:
I usually use robocopy as a Windows scheduled task :

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
One option is the use xp_cmdshell and the copy command.

See example B.

You can schedule it to run after the backup job.  Also, I believe the account that SQL Server Agent is running as must have permissions to the network share.  

Vitor MontalvãoMSSQL Senior EngineerCommented:
I highly recommend you to not enabling the xp_cmdshell. There's a big reason for Microsoft don't enable it by default and it's in the MSDN: "Because malicious users sometimes attempt to elevate their privileges by using xp_cmdshell, xp_cmdshell is disabled by default"

Why just not try to backup directly to the network share?
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

bjenningsAuthor Commented:
Hello Vitor,

I thought backing up directly to a network share was against best practice.  If there was a network hiccup, the backup would fail.
Vitor MontalvãoMSSQL Senior EngineerCommented:
I thought backing up directly to a network share was against best practice
No. Nowadays the local network speed is fast enough. Years ago could be a pain in the *ss but I don't think it's a big issue at the present.

If there was a network hiccup, the backup would fail.
Also if you have some issue in the local disks. Nothing is 100% error free so you just need to find a way to receive alert when it fails and have a procedure to fix or workaround.
Anthony PerkinsCommented:
I disagree, especially if you are using compression.  You want to backup locally and optionally compress the backups and then copy them to a network share.  

While on the subject I would strongly urge you to take a look at Ola Hallengren's scripts:
SQL Server Backup
bjenningsAuthor Commented:
Thank you so much!
bjenningsAuthor Commented:
Thank you everyone for your help!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.