asked on

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?

Thanks,

Bill

ASKER CERTIFIED SOLUTION

PadawanDBA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JestersGrind

One option is the use xp_cmdshell and the copy command.
https://msdn.microsoft.com/en-us/library/ms175046.aspx

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.

Greg

Vitor Montalvão

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?

bjennings

ASKER

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ão

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 Perkins

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

bjennings

ASKER

Thank you so much!

bjennings

ASKER

Thank you everyone for your help!