Copy files across network

[AnnCao]

We are configuring a testing server for our academic software application.

Part of it is sql server for database.

So I backuped all the database we need on production server, and plan to restore them on our testing sql server.

But as far as I know I cannot restore databases from back up files that across the network.

So I plan to move the huge back up files to the testing server.

I know we can write some scripts and schedule it to run to move the files across network, but I wonder if there is another way to do this.

Or I remember I read somewhere there is a windows tool that move large files across network that can be download, but I cannot find the information any more.

Any ideas or suggestions how I can accomplish this?

Thank you

[Scarecrow Man]

There should be no problems to backup or restore to a network share. Just make sure the SQL Service Account has permssions on the shared drive you are restoring from.

EDIT: Found this

Q. Why can't I backup/restore my SQL Server databases to a share on another

server?

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 nobody

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 SYSTEM 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 SYSTEM 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 share

name you want to dump to - the share does not then authenticate who is coming

in and so a SYSTEM 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 or anything have

access. This is probably not something you want to do with production business

data.

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\LanmanServer\Parameter­s\Nu

llSessionShares

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'

e.g. (7.0, 2000) BACKUP DATABASE pubs to

DISK='\\server01\share\backupdir\backup.dmp'