September 11, 2009 at 3:11 pm
For years I've backed up our SQL Server 2000 across a network share to Windows Server 2003, with no troubles. Now we have a new backup server and it's Server 2008 based (64 bit by the way). The backups can't write to the share I've created there, even though I've:
1. Double-checked the UNC pathname to the new server (and also even tried using a mapped drive letter, even though I know it doesn't work on Server 2003);
2. Verified the username and password are the same (I can see the SQL machine's successful logins and logouts for username "x" in the target machine's Security Event Log, and I can also login under "x", same as SQL is running under, and write to the share);
3. Inserted user "x" into the Administrators group on the target machine for testing purposes.
So what's different under Server 2008? I get the same result initiating a manual backup from the Enterprise Manager as I do from a backup job running under the SQL Serve Agent. I also get the same result whether or not the target directory already exists (I've seen comments in other threads that SQL won't create a directory, but it has always done it just fine for me.)
The SQL Server error log contains this:
Could not create a sub directory for database master. The parent directory will be used instead.[1] Database master: Database Backup...
Destination: [\\d333clk1\d\Backup\Web1 SQL\master_db_200909111553.BAK]
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 3201: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open backup device '\\d333clk1\d\Backup\Web1 SQL\master_db_200909111553.BAK'. Device error or device off-line. See the SQL Server error log for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.
Going directly to a network share works much better for us than making a local copy first (which I've seen suggested in other threads), especially because there are log backups thoughout the day. I'd really like to get it going with the 64-bit Server 2008 target, but what's different that's keeping this from working?
September 11, 2009 at 5:19 pm
add the network pafh as a backup device through UI or sp_addumpdevice
September 14, 2009 at 8:45 pm
Although I'm on vacation this week, I connected to my office PC and looked for the option you describe (I had never heard of it before). I did find how to add a backup path manually, but unless I'm doing something wrong it still didn't work.
And of course there has never been any such backup path needed when backing up to the Windows Server 2003 system. Why should Server 2008 be any different in this respect?
The dialog box seemed to indicate the path needed to include the file name also, but of course I can't do that since there are thousands of different backup file names generated each day.
September 15, 2009 at 1:48 am
September 28, 2009 at 1:46 pm
OK, now that I'm back from vacation and gotten past a few server emergencies, I can get back to this thread.
Yes, as far as I can tell, full permissions are there. In my original post I already said I had full ADMIN permissions, so I don't understand your question.
Of course I tried various permission setups before posting, and by the time I posted, was testing this with full admin privileges. By that I mean, more precisely, that SQL Server (and its Agent, which will actually be doing the production backup jobs) are both running under an account named SQLServer, and for testing at least, I have added that account to the Administrators group on the target server.
When the actual backup jobs (that always work on the Server 2003 target) didn't work, I had moved to using Enterprise Manager for testing. It didn't work either.
Since nobody had posted that this wasn't supposed to work (which was really the first question that needed to be answered), today I decided to try the actual T-SQL BACKUP DATABASE statement in Query Analyzer. After running through various combinations that didn't work, I decided to try specifying the IP address instead of the server name in the UNC. All of a sudden things started to work.
In other words, \\<server-name>\share-name\subdir\filename.bak fails, but \\<server-ip>\share-name\subdir\filename.bak succeeds. At least as long as "subdir" exists.
(I have never had to create the subdirectories before. I suppose maybe the xp_sqlmaint function in the normal backup job does it, before doing the actual backup.)
So from that it appears this may be a case of the server's NetBIOS name not propagating properly across the network. However, I can ping the target just fine using only its NetBIOS name, both from my workstation and also from the server where SQL Server is running.
I could try running the production backups using the target server's IP address, but it's a kludge, and unwanted effort to change all the backup scripts, plus (wouldn't you know it) that IP is just about to change when the new server gets put into production replacing the old backup target.
Any ideas why SQL Server won't recognize the NetBIOS server name?
September 30, 2009 at 3:03 am
There may be a lack of trust between the two machines. Dealing with trusts has changed a lot with W2008.
Try adding the target server to your Trusted Sites in Internet Options.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
September 30, 2009 at 7:01 am
Well, I can try that, but I can already create, edit, and delete files on the target machine while logged in to the source machine. Everything I try works, except SQL Server backups. So what would SQL Server be looking at, that Windows itself doesn't?
Do you have a link explaining some more about those changed trust relationships in WS 2008?
September 30, 2009 at 7:07 am
If you can edit files yourself, then it is not a trust issue. Have you tried logging on with the SQL service account and accessing the share. This would confirm that the accuont has the right permissions.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
September 30, 2009 at 7:33 am
I did try that once before, but long enough ago that I forgot the results. I figured putting that account in the target's Administrators group should be enough (unless Windows was seriously sick!).
Anyway, I just tried it again, and I was able to copy a file to the target, edit it, and then delete it.
Oh, and the fact that accessing the target via IP address (as opposed to NetBIOS name) works, also argues against it being a trust issue.
October 2, 2009 at 11:04 am
Well, I'm out of any more time to spend on this. We're going to have to use the IP address workaround for the time being, unless/until a true solution comes along. After researching it, I know a good bit more than I did about NetBIOS names, and NetBIOS-over-IP, but nothing else has worked.
I've tried shutting down the firewall, re-enabling "network discovery," even putting the target machine's name in an LMHOSTS file on the originating machine and preloading it into the name cache. I didn't try putting up a WINS server -- the effort just doesn't seem reasonable. I've tried to get away from NetBIOS names by adding a true DNS name for the target (and for my trouble, just got a different error message: duplicate system name on the network). After all this, SQL Server still refuses to recognize the name and write to the share.
Maybe this problem is unique to SQL Server 2000? Maybe SQL 2005 works? Looking at the posts, I still don't know for sure if anyone has actually gotten this to work for specifically SQL Server 2000 (backing up to a share name that's on Windows Server 2007). Has anyone?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply