Can you backup sql DB to different location? 1

[fajitapete1]

I am afraid I know the answer but just in case here is my dilemma:

I have a lot of network storage but I also have a sql server that has a 60bg drive holding a 40gb database. So far I can find no way to back up this db except by doing a file copy. I really need to backup this data along with trn logs so I can recover if necessary, I did mention it is a production DB.

I am avoiding going to the boss with a request for an additional drive for this machine, I just brow beat them in to buying me a terabyte of network storage that I can't seem to use for back ups.

Anybody have any suggestions?

Thanks,

pete

 

[ecobb]

You should be able to backup the database to anywhere on your network. I've got nightly SQL Server backups running to several differnet machines with no problems. What exactly are you running in to?

Hope This Helps!

Ecobb
- I hate computers!

 

[fajitapete1]

Thanks for the quick reply, I too assumed you could backup to anywhere on the network. I am using the enterprise manager interface and it only sees the local drives.

Do you run your back ups from the command line?
using something like :

-- Create the backup device for the full MyNwind backup.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwind_2',
'\\SQL2\Program Files\Microsoft SQL Server\MSSQL \BACKUP \MyNwind_2.dat'

BACKUP DATABASE MyNwind TO MyNwind_2

?

 

[SQLBill]

Don't forget, if you are running your backups as a scheduled job, the SQL Server Agent service account must have access to the backup file location. Default is for the SQL Server Agent to run as a LOCAL account. That means it can't access network shares.

Start with checking the SQL Server Agent account, if it's a local account, change it to a domain account.

-SQLBill

 

[ecobb]

I'm running my backups from Enterprise Manager as well. I just specify the network path I want it to use, like this: \\ServerName\E$\SQLbackups (where "E$" is the drive letter on the remote maching) So, you would specify the path as: \\ServerName\DriveLetter$\Folder\Folder\etc...

As SQLBill said, you also have to make sure the account that the SQL Server Agent is using has permission to access machines across the network.

Hope This Helps!

Ecobb
- I hate computers!

 

[trendzone]

I'm in a similar sitution. After I made sure the SQL Server agent service is logged on as a valid domain user I created a backup device in the EM and pointed it to the specifed network path. So when I create a backup job I select backup device and submit.

Everything seems to work fine as a BAK file is created and the size of the file looks like a complete back up, BUT at the very end of the backup process EM tells me the procedure has terminated prematurely and I get an error in the ERRORLOG that reads, "BackupDiskFile::RequestDurableMedia: failure on backup device '\\fchwd03\Aperture\Backup\apertureproddb_backup.BAK'. Operating system error 64(The specified network name is no longer available.)."

I have searched high and low for anything related to this type of error message and how to solve it and have come up empty. Any ideas?

-SL

 

[SQLBill]

It sounds like your network connection is being 'cut' before the backup is finished. Check with your network sysadmin. Sometimes if you are 'moving' a large amount of data the network 'times out'.

-SQLBill

 

[Grant007]

Did anybody solve this problem of the

operating system error 64
the specified network name is no longer avaliable

any help on this error would be very much appreciated.. as it happening to most of my sql backups that backup acroos the network!!

thanks.

 

[kishy]

What kind of operating system is running on the file server?
If Windows 2000 or 2003, are you using named pipes? If yes, then check out this article :

http://support.microsoft.com/default.aspx?scid=kb;en-us;827452

--Kishore