May 28, 2002 at 6:55 am
Using SQL 2000 can you restore a database from a UNC path?
\\server2\c\myfile
-Kevin
May 28, 2002 at 7:08 am
Yes using the 'restore database' t-sql command. Not sure if EM allows you.
Steven
May 28, 2002 at 7:18 am
I figured it out:
Why can't I backup/restore my SQL Server database to the network?
The reason is that the MSSQLSERVER service is running under a separate set of NT credentials. It doesn't matter who YOU are logged on as (after all SQL runs quite happily when no-one is logged on to the console doesn't it). Therefore your logon account and any mapped drives are irrelevant. It is SQL Server doing the backup, not you.
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.
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 security on that share, so you need to be careful about what is in the share.
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. DUMP DATABASE pubs to DISK='\\server01\share\backupdir\backup.dmp'
Can I do a SQL backup to a tape drive on another server?
No, not with built-in SQL tools. SQL will only dump to local tape devices. If you could find an NT driver that would make a remote tape drive look local then this would work as SQL just uses standard i/o calls. I don't know of such a driver at the moment.
You can always dump the SQL database to disk locally (or across the network with some provisos) and then back that up to tape.
Finally, you CAN do what you want with a 3rd party backup tool that has a SQL agent. Examples are BEI Ultrabac, Cheyenne Arcserve, Seagate BackupExec, Legato Networker and IBM ADSM - I'm sure there are others.
These put SQL dumps (via a standard named-pipe interface) onto a standard dump tape, potentially as part of a complete server backup and not just SQL. Obviously if the named-pipe connection is made across the network then the dump will usually be a lot slower than doing it locally.
Why is a SQL Server restore (LOAD DATABASE) so much slower than a dump database?
The answer to this is that SQL initialises all pages during a load. So if you have 50Mb of data in a 5Gb database, the dump only processes the used 50Mb. But when the load occurs it loads the 50Mb of data - which takes roughly the same time as the dump - and then initialises the rest of the 4.95Gb of free space. This initialisation is done page by page and goes as fast as the disk subsystem will allow.
May 28, 2002 at 10:58 pm
Just look into BOL for sp_addumpdevice
Hope this is what you are looking for
Prakash
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply