SQL backup with network drive

carmelcw

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

petey

Does it make any difference if you use UNC names e.g. \\computername\netbackups, instead of mapped drives e.g. x:\netbackups ?

carmelcw

Hi,
Under my program, I have already use the UNC name.
Regards,
Carmel Lee

Brian Donahue

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.

carmelcw

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

Brian Donahue

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.

carmelcw

Hi Brian,

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

Best regards,
Carmel Lee

carmelcw

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