| Author |
Topic |
|
hansonian
Starting Member
7 Posts |
 Posted - 2010-02-25 : 13:16:57
|
| I'm new to SQL and have run into a problem. I would like to set a backup device to a network attached storage (NAS) I have on my network. I'm running SQL 2005 on a new install of Windows Server 2008. I can see the storage device I want on the network but when I goto make a backup device to that corresponding drive it only sees the local drives on the machine. I've plugged in a USB hard drive and it recognizes it but no matter what I've tried I can't find the NAS. Is this something that's just a impossibility or am I overlooking something?Any help on this would be greatly appreciated.-hansonian |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-02-25 : 14:17:50
|
| use unc path.\\servername\sharename\ |
 |
|
|
Peter99
Constraint Violating Yak Guru
498 Posts |
Posted - 2010-02-25 : 14:38:26
|
| you can map network drive |
|
|
|
hansonian
Starting Member
7 Posts |
Posted - 2010-02-25 : 14:39:38
|
| I've tried the unc path to set up the device and when I do a quick backup to test it I got this error;Backup failed for Server 'CRC-SERVER'. (Microsoft.SqlServer.Smo)ADDITIONAL INFORMATION:System.Data.SqlClient.SqlError: Cannot open backup device 'Server Backup(\\LS-WTGL34E\)'. Operating system error 161(The specified path is invalid.). (Microsoft.SqlServer.Smo)This is the name and path to the NAS device 'Server Backup(\\LS-WTGL34E\)'. I've been over everything I could think of and some things that I randomly found and I've made zero progress on this. Is it normally this hard to backup to a separate device?-hansonian |
|
|
|
hansonian
Starting Member
7 Posts |
Posted - 2010-02-25 : 14:43:40
|
quote:
Originally posted by Peter99
you can map network drive
I originally mapped it as a network drive and it wouldn't see it that way either. It seems to only show physical drives that are on the server itself. Is it possible I'm missing some little check box or line item somewhere?-hansonian |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-02-25 : 14:50:06
|
| DON'T map drive. Use unc path. Seems the sql service account doesn't have permission on the share. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-02-25 : 14:50:59
|
| try using t-sql backup command and post any errors here |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2010-02-25 : 15:42:38
|
quote:
Originally posted by russell
Seems the sql service account doesn't have permission on the share.
This seems the most likely cause to me. BACKUP works within the security context of the SQL Service Account. YOU may have rights to the NAS device but once you invoke BACKUP, your rights are no longer germane. See if you (or your DBA/IT guy) can log in using the SQL Server Service account and still see the device.=======================================Few things are harder to put up with than the annoyance of a good example. (Mark Twain) |
|
|
|
hansonian
Starting Member
7 Posts |
Posted - 2010-02-25 : 15:45:06
|
| This is the t-sql command I used,--Backing Up a Whole Database BACKUP DATABASE CRCDATA TO DISK = '\\LS-WTGL43E\Server\TestDB.bak' WITH DESCRIPTION = 'Test DB Backup';and this is the error it's giving now.Msg 3201, Level 16, State 1, Line 2Cannot open backup device '\\LS-WTGL43E\Server\TestDB.bak'. Operating system error 53(The network path was not found.).Msg 3013, Level 16, State 1, Line 2BACKUP DATABASE is terminating abnormally.It looks to me like it still can't figure out how to get to the NAS or maybe I typed something in wrong. I appreciate everyone's suggestions. If nothing else it's getting me more familiar with SQL and I'm learning things. -hansonian |
|
|
|
hansonian
Starting Member
7 Posts |
Posted - 2010-02-25 : 16:10:48
|
quote:
Seems the sql service account doesn't have permission on the share.
quote:
This seems the most likely cause to me. BACKUP works within the security context of the SQL Service Account. YOU may have rights to the NAS device but once you invoke BACKUP, your rights are no longer germane. See if you (or your DBA/IT guy) can log in using the SQL Server Service account and still see the device.
I've gone into the Server Service Account and set them all as "Administrator" just to test this, reran the same command and this is my new error code,Msg 233, Level 20, State 0, Line 0A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)I'm not too sure what the provider: Shared Memory Provider is or why it's giving a error, anyone have a thought on this?-hansonian |
|
|
|
David Singleton
Starting Member
35 Posts |
Posted - 2010-02-25 : 16:38:40
|
| I am no expert, but when I first encountered this issue, I was told that it was because of performance, i.e. that it will take a lot longer to create a backup over a network, thus slowing down the whole server whilst this is happening. Thus SQL makes it difficult though not impossible to do. Now I always backup to a fast local drive and then move the backup to the network once that is finished.In that first case, it was taking about 3 hours to do a backup, over the network to a mapped drive, and moving it to a fast local drive, it went down to under 30 minutes.David SingletonMicrosoft MVP Dynamics NAV |
|
|
|
David Singleton
Starting Member
35 Posts |
Posted - 2010-02-25 : 16:43:22
|
quote:
Originally posted by russell
DON'T map drive. Use unc path. Seems the sql service account doesn't have permission on the share.
In the case I described above, we could not backup to a UNC, ONLY to a mapped drive. UNC just gave the "drive not found" error, but that was SQL 2000 not 2005, so maybe irrelevant here.David SingletonMicrosoft MVP Dynamics NAV |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-02-25 : 16:44:30
|
quote:
Originally posted by hansonian
I've gone into the Server Service Account and set them all as "Administrator" just to test this, reran the same command
what does that mean?is the SQL Server service account a domain account ot a local one?it needs explicit permission on the share you're backing up to. |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2010-02-25 : 19:42:45
|
| The diagnostic path that I was suggesting was for you to log on to Windows on the SQL Server hardware using the same credentials as are used by the SQL Server Service account and then see if you can reach the NAS device directly. Sorry if I was a bit unclear.=======================================Few things are harder to put up with than the annoyance of a good example. (Mark Twain) |
|
|
|
hansonian
Starting Member
7 Posts |
Posted - 2010-02-26 : 09:50:27
|
quote:
Originally posted by Bustaz Kool
The diagnostic path that I was suggesting was for you to log on to Windows on the SQL Server hardware using the same credentials as are used by the SQL Server Service account and then see if you can reach the NAS device directly. Sorry if I was a bit unclear.
The SQL Service accounts are set to 'Local System' it's my understanding that I can't login with this and that it's for the operating system itself, am I wrong or maybe I'm not understanding what you're saying. I've looked through everything for the NAS itself and can't seem to find anything that would disallow this procedure. Does it matter that the NAS is setup as RAID1? I really appreciate everyone's help and input on this, I don't have a strong database background but since I've been put into this role I'm trying to get up to speed as fast as I can.-hansonian |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
|
|
|
NAS as a backup device?