• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 35
  • Last Modified:

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
0
bjennings
Asked:
bjennings
1 Solution
 
PadawanDBAOperational DBACommented:
I usually use robocopy as a Windows scheduled task : https://technet.microsoft.com/en-us/library/cc733145.aspx
0
 
JestersGrindCommented:
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
0
 
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?
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
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.
0
 
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.
0
 
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
0
 
bjenningsAuthor Commented:
Thank you so much!
0
 
bjenningsAuthor Commented:
Thank you everyone for your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now