| Author |
Topic |
|
InfoTech
Starting Member
4 Posts |
 Posted - 2007-11-28 : 01:10:41
|
| When using Query Analyser we are using the following SQL to backup a database to diskBACKUP DATABASE (DATABASE_NAME) TO DISK = '\\192.168.0.5\sharename\test.bak'Its returning the following error message2007-11-28 14:34:44.49 spid55 BackupDiskFile::CreateMedia: Backup device '\\192.168.0.5\sharename\test.bak' failed to create. Operating system error = 5(Access is denied.).If I replace the ip address with servername and run BACKUP DATABASE (DATABASE_NAME) TO DISK = '\\SERVERNAME\sharename\test.bak'it works successfully. So backing up via ip address fails but by name it will work. The folder share is accessable by running \\192.168.0.5\sharename\ and full permission is enabled.Both servers run Windows Server 2003, are on the same domain with the same SQL and Windows administrator passwords. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-28 : 03:03:35
|
| Did you ping the ip address from sql server? Did it return correct server name? |
 |
|
|
InfoTech
Starting Member
4 Posts |
Posted - 2007-11-28 : 08:01:41
|
| Yes both servers can ping each other by name and ip. the server we are running the database on can access the shared folder on the other server by both name and ip. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2007-11-28 : 08:53:14
|
| Do you have a group policy denying server connection via IP? |
|
|
|
InfoTech
Starting Member
4 Posts |
Posted - 2007-11-28 : 17:35:54
|
| no, we have both servers in same OU, without any deny rights.Its strange because the ip resolves to name correctly, and if we backup via \\SERVERNAME it works but by \\SERVERIP it fails. TCP/IP is also top priority in cliconfg on both servers |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2007-11-29 : 03:49:26
|
| That is really very strange, I take it you do not have an Aliases set up or you would have mentioned it, but even if you did, I couldn't see how that would affect the IP address?!? |
|
|
|
suresha_b
Yak Posting Veteran
82 Posts |
Posted - 2007-11-29 : 04:05:04
|
| This could be DNS issue.run ipconfig /flushdnsThen ping ServerNameSee whether it returns same ip (192.168.0.5) |
|
|
|
InfoTech
Starting Member
4 Posts |
Posted - 2007-11-29 : 06:08:52
|
| all the dns records are fine, they're all reserved dhcp addresses which are resolving properly. Its the account that SQL is using to access the file share under ip thats failing. Using the machine with the folder share and running SQL on that to backup works successfully. So the server itself backs up to the share fine, but over network and only by ip its failing...Spent a lot of time trying to fix this,We set up the folder share on another machine, Windows XP laptop. That works fine to backup. We have also had a 2000 Server in the past which had the same problem so its permissions existing on 2000Server and 2003Server somewhere on that shared folder which is blocking ip access(only for the account SQL is using) but letting access via name |
|
|
|
dewacorp.alliances
452 Posts |
Posted - 2007-11-30 : 02:28:45
|
quote:
Originally posted by valdyv
What sort of SQL service account that is running? Cause you can't have a system account running if you want to access to outside your local server. Also, if it's not a system account, is it a domain account or workgroup?For domain account, you just give a normal permission on the box and for workgroup give the same name account and passord accessing that box. Generally, I give the full local admin on the box.Thanksquote:
Originally posted by InfoTech
all the dns records are fine, they're all reserved dhcp addresses which are resolving properly. Its the account that SQL is using to access the file share under ip thats failing. Using the machine with the folder share and running SQL on that to backup works successfully. So the server itself backs up to the share fine, but over network and only by ip its failing...Spent a lot of time trying to fix this,We set up the folder share on another machine, Windows XP laptop. That works fine to backup. We have also had a 2000 Server in the past which had the same problem so its permissions existing on 2000Server and 2003Server somewhere on that shared folder which is blocking ip access(only for the account SQL is using) but letting access via name
|
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-11-30 : 08:29:12
|
in general it's not a good idea to backup to a remote share. better is to backup to the local disk, and then copy the local backup file to the remote share when it's done. if the copy is successful, then you can delete the local copy.the reason is, if the network goes down or there's some other glitch while the backup is happening, you lose your backup, resulting in possible data loss. then you get fired. and nobody wants that...
elsasoft.org |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-12-02 : 01:44:31
|
| Not sure if it's possible if the server has db size in couple of tb. |
|
|
|
|
SQL 2000 Backup denied access to network share