May 11, 2006 at 5:55 am
I can't see any n/work drives offered in the Maintenance Plan.
Thanks,
Jaybee.
May 11, 2006 at 6:54 am
from http://www.windowsitpro.com/Article/ArticleID/14025/14025.html
| ||||
A. A. The reason is that the MSSQLSERVER service is running under a separate set of NT credentials - all services are related to an NT account. It doesn't matter who YOU are logged on as (after all SQL runs quite happily when no-one is logged on locally to the server doesn't it). Therefore your logon account and any mapped drives are irrelevant. It is SQL Server doing the backup, not you. This is the same for backups done via SQL Executive/SQL Agent - they just pass the TSQL to SQL Server to run, so it's still MSSQLSERVER doing the backup/restore. For this reason the backup gui does not show you mapped drives or allow a UNC path to be typed in. You have to use raw TSQL commands to do the backup. The default set of NT credentials used by MSSQLSERVER is the Localsystem account. You can check what userid that MSSQLSERVER is running under by looking at control panel/services highlighting MSSQLSERVER and choosing the start-up option. The Localsystem account has no access to shares on the network as it isn't an authenticated network account. Therefore SQL Server running under this account cannot backup to a normal network share. So, if you want to backup to a network share you have two choices :- 1. Change the account the MSSQLSERVER service runs under to a user account with the relevant network rights. or 2. Amend the following registry value on the TARGET server and add the sharename you want to dump to - the share does not then authenticate who is coming in and so a Localsystem account will work. The server service on the target server must be re-started before the change takes effect. Note that this effectively removes ALL security on that share, so you're letting anyone/anything have access. Which is probably not something you want to do with production business data. HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Parameters\NullSessionShares Whichever method you use, you MUST also use a UNC name to reference the file required and not a drive letter. e.g. (6.5) DUMP DATABASE pubs to DISK='\\server01\share\backupdir\backup.dmp' (7.0) BACKUP DATABASE pubs to DISK='\\server01\share\backupdir\backup.dmp' | ||||
Lowell
May 11, 2006 at 7:26 am
Hi Neil,
The SQL services both login with the main domain admin account.
Is it sufficient that I alter the job step to write to the share?
At the moment, it is:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID 9EF21162-EF14-4F2A-8DD2-409BC1574749 -Rpt "C:\Program Files\Microsoft SQL Server\MSSQL\LOG\Full backup of Database and Transaction logs4.txt" -WriteHistory -VrfyBackup -BkUpMedia DISK -BkUpDB "E:\Backup" -DelBkUps 2DAYS -BkExt "BAK"'
You said that the job would need to use raw SQL. Could I simply alter the last line from "E:\Backup" to "\\Server_name\SQL"??
Thanks,
Jaybee.
May 11, 2006 at 7:32 am
the GUI doesn't let you browse to, or paste a UNC path; so to use the GUI, you MUST map a drive; if the problem was a scheduled job, then you can use a UNC path for the file location.
Lowell
May 11, 2006 at 7:48 am
Thanks, but I'm asking if that path is sufficient for a UNC - it's been some time since I did NT, and whether putting in a UNC path into the job step will suffice.
Jaybee.
May 12, 2006 at 8:11 am
I use UNC paths. What version of SQL are you running because this was not available in 7.0? Also, it's my understanding that MS doesn't recommend doing this (but we do it anyway and it works fine!).
Terry
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply