akbcomputers
asked on
SQL DB backups to network drives
We are low on space on our SQL server and I would like to backup my databases to a network drive.
I did do some searches on this site, but what has been suggested I get errors. What I would like to do is to be able to create a maintenance plan that will allow me to back up to the network or is the only way to backup to the network is to use query analyzer?
I am running SQL 2000 with SP4
Thanks,
I did do some searches on this site, but what has been suggested I get errors. What I would like to do is to be able to create a maintenance plan that will allow me to back up to the network or is the only way to backup to the network is to use query analyzer?
I am running SQL 2000 with SP4
Thanks,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi,
For a previous company I had this working, but had to do the following:
In the script check for the drive mapping, and if necessary remap the drive.
Create a SQL start-up procedure that mapped the drive.
I got fairly good throughput over-night - 100GB in around an hour. The servers where on a Gigabit switch.
Regards
David
For a previous company I had this working, but had to do the following:
In the script check for the drive mapping, and if necessary remap the drive.
Create a SQL start-up procedure that mapped the drive.
I got fairly good throughput over-night - 100GB in around an hour. The servers where on a Gigabit switch.
Regards
David
ASKER
There is not enough space on the local machine to handle the DB.
The error I get is:
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '\\servername\full\path\to \backup\fi le\model.b ak'. Device error or device off-line. See the SQL Server error log for more details.
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
The error I get is:
Server: Msg 3201, Level 16, State 1, Line 1
Cannot open backup device '\\servername\full\path\to
Server: Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Hi,
Do try what I suggested about with mapping a drive and using that. Note that it does need to be mapped within SQL's process - hence the startup proc.
The command to map a drive is
net use f: \\someotherserver\someshar e
Regards
David
Do try what I suggested about with mapping a drive and using that. Note that it does need to be mapped within SQL's process - hence the startup proc.
The command to map a drive is
net use f: \\someotherserver\someshar
Regards
David
ASKER
I tried that, I get a syntax error 'f:'
I also tried master..xp_cmdshell 'net use f: \\someotherserver\someshar e' which was working on another server, but not this server
I also tried master..xp_cmdshell 'net use f: \\someotherserver\someshar
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You did replace this name:
'\\servername\full\path\to \backup\fi le\model.b ak'
with your actual remote computer name, path name, and backup name, right? :-)
Also, Yveau is of course correct: make sure the account doing the backup has full permissions on the remote server directory.
'\\servername\full\path\to
with your actual remote computer name, path name, and backup name, right? :-)
Also, Yveau is of course correct: make sure the account doing the backup has full permissions on the remote server directory.
ASKER
Oh, I have to use my server name...
Sheesh :P
It was the account. One server is running under the local system account when the other is using an actual account. Didn't realize that.
Thanks Yveau and Scott as i still needed the query.
Sheesh :P
It was the account. One server is running under the local system account when the other is using an actual account. Didn't realize that.
Thanks Yveau and Scott as i still needed the query.
I use MP's to create backups to SAN disks all the time ... I don't even have disks in my machines !!!
So it should work ... what errors are you encountering ?