September 6, 2012 at 9:22 am
Hi all!
I've been trying to automate my backups to a shared network drive. I have found a solution that works which is:
EXEC xp_cmdshell 'net use F: \\VBOXSRV\Public'
Though I find it less secure because to do that I have to enable the xp_cmdshell command which I don't like too much
EXEC sp_configure 'show advanced options', 1
EXEC sp_configure 'xp_cmdshell', 1
I've trying to backup directly to the shared drive using the UNC path but it sometimes works, and sometimes fails without me knowing the reason, the error when it fails is as follows...
TITLE: Microsoft SQL Server Management Studio
------------------------------
Backup failed for Server 'WIN-JAQ310LI003'. (Microsoft.SqlServer.SmoExtended)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Backup+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
System.Data.SqlClient.SqlError: Cannot open backup device '\\VBOXSRV\Public\SQL_BACKUP\hello_backup'. Operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
As I am doing my tests in a virtual machine and I try to make the backup as soon as it boots perhaps it is just a matter of time for the host to realize of the shared drives in the network.
What I am looking for is an advice of what to do or what is the best way to automate this daily task ?
Which is the best way of getting this accomplished?
Is it safe backing up in a network drive?
Thanks!
September 6, 2012 at 9:36 am
Backups to UNC locations can work, however small network hiccups can cause them to fail. What you may need to do is backup locally then move the backup file to the UNC location.
September 6, 2012 at 9:42 am
Lynn Pettis (9/6/2012)
Backups to UNC locations can work, however small network hiccups can cause them to fail. What you may need to do is backup locally then move the backup file to the UNC location.
Actually that is what I am currently doing.
Backing up locally and then moving the files to a external location with robocopy, any suggestions instead of robocopy? or it is appropriate for the task?
I thought that using UNC paths could fit better to my needs but maybe is as you say
Thank you for the reply!
September 6, 2012 at 10:09 am
tikoti (9/6/2012)
Lynn Pettis (9/6/2012)
Backups to UNC locations can work, however small network hiccups can cause them to fail. What you may need to do is backup locally then move the backup file to the UNC location.Actually that is what I am currently doing.
Backing up locally and then moving the files to a external location with robocopy, any suggestions instead of robocopy? or it is appropriate for the task?
I thought that using UNC paths could fit better to my needs but maybe is as you say
Thank you for the reply!
Robocopy is the tool I used to move backup files to a network location at a previous employer. I had it setup to move the files automatically upon creation so that it was not a part of the backup process.
If you have a stable network, it works. What I have seen where this works well the servers have multiple NICs and there is a dedicated network for the backups to run on that was separate from the general use network. This eliminated many of the small hiccups that could kill a backup.
September 6, 2012 at 10:49 am
Robocopy is the tool I used to move backup files to a network location at a previous employer. I had it setup to move the files automatically upon creation so that it was not a part of the backup process.
If you have a stable network, it works. What I have seen where this works well the servers have multiple NICs and there is a dedicated network for the backups to run on that was separate from the general use network. This eliminated many of the small hiccups that could kill a backup.
Ok thanks! So I think I will carry on with robocopy
Finally... I have it setup to move the files at a certain hour that I expect the backup to be finished
If I understand you correctly you backed them up and move immediately after their creation
Could you point me out on how to do that?
Thank you anyway your expertise has been of great help 😉
Thank you for your advices!
September 6, 2012 at 10:58 am
tikoti (9/6/2012)
Robocopy is the tool I used to move backup files to a network location at a previous employer. I had it setup to move the files automatically upon creation so that it was not a part of the backup process.
If you have a stable network, it works. What I have seen where this works well the servers have multiple NICs and there is a dedicated network for the backups to run on that was separate from the general use network. This eliminated many of the small hiccups that could kill a backup.
Ok thanks! So I think I will carry on with robocopy
Finally... I have it setup to move the files at a certain hour that I expect the backup to be finished
If I understand you correctly you backed them up and move immediately after their creation
Could you point me out on how to do that?
Thank you anyway your expertise has been of great help 😉
Thank you for your advices!
You will have to do the research but basically I setup Robocopy as a service to move new files created in the backup directory to the desginated network location. Off the top of my head I don't remember the details as it has been over two years since I set it up.
September 7, 2012 at 12:54 am
You will have to do the research but basically I setup Robocopy as a service to move new files created in the backup directory to the desginated network location. Off the top of my head I don't remember the details as it has been over two years since I set it up.
Nice!
That is more than enough to start looking for a solution
You've been of great help, thank you for your advices
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply