SQL backup with network drive

carmelcwcarmelcw Posts: 14
edited June 12, 2006 10:22PM in SQL Backup Previous Versions
Hi,

I have writed a VB program which will run the backup and the file will saved in the network drive.
My problem is, everyday I need to refresh the network drive connection in the SQL server. If I'm not refresh, the program cannot be run, and saying the file cannot be created.
My server is runing in W2K3 Std. Sometime the Windows Explorer show the network drive is disconnected, but when I double click on it, it will connect it again. I know there may be the problem come from the server.
I have tried the run a BAT file to reconnect the network drive, also added to the "Schedule" run every 4 hours. But it doesn't seem to work.
Is there a way to avoid this problem or can I add a refresh command in my program?
My backup program will be run a client side, and all the connectivity and SQL command are stored in the program, so the user only need to select the date than start the backup.

Thanks for everyone in advance.
Carmel Lee
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    Does it make any difference if you use UNC names e.g. \\computername\netbackups, instead of mapped drives e.g. x:\netbackups ?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • carmelcwcarmelcw Posts: 14
    Hi,
    Under my program, I have already use the UNC name.
    Regards,
    Carmel Lee
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hello Carmel,

    I think that the issue is that your computer is not holding on to the security token for the share that you're backing up to. In that case, you'd be asked for a username and password.

    I think the workaround here would be to modify the script so that it maps a network drive especially to do the backup. 
    /* JSCRIPT */
var obNetwork = new ActiveXObject("WScript.Network");
obNetwork.MapNetworkDrive("Y:", "\\server\share", false, DomainUser, DomainPassword);
/* do the backup */
obNetwork.RemoveNetworkDrive("Y:", true);
    Note that you don't specifically have to back up to Y:\, but you should be authenticated to the share now so you won't have an 'access denied' problem.
    · Share on Twitter
  • carmelcwcarmelcw Posts: 14
    Hi Brian,

    Thanks for the share. Does it mean I just copy your code in my VB program. As I see this is a JSCRIPT, do i need to use VB script instead?

    My program is under VB syntax.

    Thanks and regards,
    Carmel Lee
    · Share on Twitter
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Hi Carmel,

    I was afraid you'd say that. :-)

    VBScript would be similar: 
    /* VBS */ 
Dim obNetwork
Set obNetwork = CreateObject("WScript.Network")
obNetwork.MapNetworkDrive("Y:", "\\server\share", false, DomainUser, DomainPassword); 
/* do the backup */ 
obNetwork.RemoveNetworkDrive("Y:", true)
    I think that will work.
    · Share on Twitter
  • carmelcwcarmelcw Posts: 14
    Hi Brian,

    Thanks for your help. I will try and let you know the result later.

    Best regards,
    Carmel Lee
    · Share on Twitter
  • carmelcwcarmelcw Posts: 14
    HI Brian,

    After a few try. The script of the mapdrive is only work in local machine.

    I may want the script run at the server side. (force to run at server) As I find that, when I start the backup in the client machine, the SQL Backup script will run at server side. So, at that time, if the server network drive is not active, than the backup will fail. (something like cannot create file or folder.)
    Now, what I can do is go to the server and refresh the network drive, than the client can start the backup again without any error.

    Thanks for your help.
    Best regards,
    Carmel Lee
    · Share on Twitter
Sign In or Register to comment.