August 25, 2009 at 10:44 am
I've been reading some of the posting about not able to backup databases to a shared drive, network drive, etc. as well as looked at some of the resolutions. Unfortunaly none of them worked for me.
Here is my scenario. I have 2 brand new SQL 2005 servers that should be backed up nightly to a NAS share. The job is running under a domain account (which is used by all of my other db servers for the same process to the same location), the NAS has 500 gig of free space on it, so its not a space issues. When I run the backup job, I keep getting this error message (on both DB servers)
Executing the query "BACKUP DATABASE [master] TO DISK = N'\\\\NASServerSQL\\data\\production\\ServerName\\master_backup_200908251120.bak' WITH NOFORMAT, NOINIT, NAME = N'master_backup_20090825112052', SKIP, REWIND, NOUNLOAD, STATS = 10
" failed with the following error: "Cannot open backup device '\\\\NASServerSQL\\data\\production\\ServerName\\master_backup_200908251120.bak'. Operating system error 3(The system cannot find the path specified.).
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Since its not space, the file doesn't currently exist on the share, I have tried to run the job under the same account that my other 2 servers are using and with no success, I even tried using my ID and no success.
Any other suggestions and/or ideas on the cause? thanks
August 25, 2009 at 11:14 am
I would check security log on the destination. your job might be trying to connect under the computer account, not the account that sql server (or the agent) is running under. try adding [domain]\[computername$] to the share permissions and give it write access. see if this works. also, make sure that folder permissions are set correctly as well. you'll need [domain]\[computername$] or [domain]\[sqlagentaccount] to have permissions to access the folder itself.
August 25, 2009 at 11:16 am
Been there done that already. That was my first thought, and no success.
August 25, 2009 at 12:26 pm
do all of the following folders exist?
\data
\production
\ServerName
do all of them have the right permissions?
do the folder permissions on \data match the share permissions?
August 25, 2009 at 12:29 pm
binko (8/25/2009)
do all of the following folders exist?\data
\production
\ServerName
do all of them have the right permissions?
do the folder permissions on \data match the share permissions?
Yes
Yes
Yes
and Yes
I even mapped to the drive from the actual server and copied and paisted it into the location to backup to and still no luck
August 25, 2009 at 12:50 pm
First, it doesn't matter what the job is run under, or what Agent runs under. Just like when you connect to SQL Server, it's not your credentials used for the backup, it's the service account.
So, what account is the SQL Server DB engine running under?
Second, I'd recommend you back up locally. Then use your job to copy the files over, and then delete them from the local machine if you need to.
August 25, 2009 at 1:03 pm
It was running under a domain service account that has access to the share, then I switched it to a local service account, and that ID has read/write access to the share, but no luck.
August 25, 2009 at 1:08 pm
Log in with the current service account, and then try to access the share. If that works, you're one thing down.
Can you run the backup command interactively in SSMS/SQLCMD?
If that works, put that command in a job and run the job.
If not, I worry about the pathing in the job. Make sure that the correct command with proper paths is executing. I might use variables to be sure it works.
Lastly, why NOINIT in the command?
August 25, 2009 at 1:11 pm
Steve Jones - Editor (8/25/2009)
Log in with the current service account, and then try to access the share. If that works, you're one thing down.Can you run the backup command interactively in SSMS/SQLCMD?
If that works, put that command in a job and run the job.
If not, I worry about the pathing in the job. Make sure that the correct command with proper paths is executing. I might use variables to be sure it works.
Lastly, why NOINIT in the command?
Did that, that's how I actually mapped to the shared drive for the valid mapping. I also took the mapping from another db server we have that is backing up to the same location (w/out issues), and copied it from there and still no luck.
I can only run the backup successfully when I create the .bak files locally.
August 25, 2009 at 2:41 pm
Mike (8/25/2009)
Steve Jones - Editor (8/25/2009)
Log in with the current service account, and then try to access the share. If that works, you're one thing down.Can you run the backup command interactively in SSMS/SQLCMD?
If that works, put that command in a job and run the job.
If not, I worry about the pathing in the job. Make sure that the correct command with proper paths is executing. I might use variables to be sure it works.
Lastly, why NOINIT in the command?
Did that, that's how I actually mapped to the shared drive for the valid mapping. I also took the mapping from another db server we have that is backing up to the same location (w/out issues), and copied it from there and still no luck.
I can only run the backup successfully when I create the .bak files locally.
this is the process i followed when I had to troubleshoot a similar issue (failing backups to network share):
1. create a new share on the destination server (since this includes making changes to permissions, didn't want to risk breaking backups that were already running)
2. try to backup to a new share (probably fails).
3. add "everyone" to both folder and share permissions with rw access.
4. try to backup to a new share. if it succeeds, you know it's a permissions issue. remove "everyone" and start by adding a)for servers running under domain account, sql server account or b) for servers running under local account, computer domain account.
5. add read/write permissions to both folder and share.
try again.
if you can't backup to a network share with "everyone" having rw access, then it's likely some other issue.
August 25, 2009 at 2:53 pm
Executing the query "BACKUP DATABASE [master] TO DISK = N'\\\\NASServerSQL\\data\\production\\ServerName\\master_backup_200908251120.bak'
notsure ... might be very basic .... but do these double \\ sound right ... shouldn't it be a single
also have we checked the systems tables in msdb for the backup device name associated with this db ??
-------------------------------------------------
-Amit
Give a man a fish and he'll ask for a lemon. Teach a man to fish and he wont get paged on weekends !! :w00t: - desparately trying to fish [/size]
August 26, 2009 at 4:53 am
Amit Singh (8/25/2009)
Executing the query "BACKUP DATABASE [master] TO DISK = N'\\\\NASServerSQL\\data\\production\\ServerName\\master_backup_200908251120.bak'
notsure ... might be very basic .... but do these double \\ sound right ... shouldn't it be a single
also have we checked the systems tables in msdb for the backup device name associated with this db ??
the double quotes are in the error message, in the actual backup job they're as ( \\NASServerSQL\) and so on
August 27, 2009 at 12:11 pm
Amit Singh (8/25/2009)
Executing the query "BACKUP DATABASE [master] TO DISK = N'\\\\NASServerSQL\\data\\production\\ServerName\\master_backup_200908251120.bak'
notsure ... might be very basic .... but do these double \\ sound right ... shouldn't it be a single
those double \\ sound very strange ( \\\\NASServerSQL)
Calico
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply