Restore database on different domain - performance

Hi,

I'm trying to restore database on the server in the different domain.

The destination server is located in the physically remoted place.

First I tried UNC path (including IP address) with DBCC TRACEON | OFF (1807) optioin but it didn't work.

Some Internet document suggested that the FTP file transfer & restore locally on the remote domain but I didn't use this method because there is additional step.

Instead I used the following scripts:

DECLARE @.COMMANDLINE VARCHAR(500)

SET @.COMMANDLINE = 'NET USE B: \\192.168.1.xxx\sql_backup$ password /USER:SourceServer\read_sql_backup /YES'
EXEC master..xp_cmdshell @.COMMANDLINE

RESTORE DATABASE [DestinationDB]

FROM DISK = N'B:\SourceBackup.bak' WITH
FILE = 1, NOUNLOAD , STATS = 10, REPLACE ,
MOVE N'dMSLog' TO N'D:\MSSQL\Data\Database.ldf',
MOVE N'dMSData' TO N'D:\MSSQL\Data\Database.mdf'

SET @.COMMANDLINE = 'NET USE B: /DELETE /YES'
EXEC master..xp_cmdshell @.COMMANDLINE

The script is working but it's extremly slow.

The size of database is only 1152.88 MB but it took more than 2 hours.

Is there any way that I can improve the performance?

Any advice would be appreciated

Thanks,

- Hyung -

Hi,

I think this is the wrong approach to restore the database. You should first copy the files to the local disk and then afterwards do the restore job. I think this will be faster than using the network restore.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

You should do the following:

1. Compress the backup file on remote server

2. Copy the compressed file locally to database server or local network

3. Uncompress the backup file

4. Now, restore using local machine/network path

This approach also works best if you are trying to do the restore from a remote network over WAN connection or slower connection. Optionally, you can stripe the backup on the remote server to multiple files. This way you can compress each file quickly and copy those over to the local database server.

You may also want to figure out the cause for the network latency in your configuration. You may be able to avoid it depending on your configuration but it requires working with network administrators etc to solve those problems.