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.

restore database form MSSQL server with no servicepack

Hi,
I have the following situation: My source server is MSSQL 2K without
servicepacks. My destination server is MSSQL 2K with SP3. Both running Win2K
with sp4.
Assume I don't want to upgrade the source server. What are my options:
1) Is it possible to backup the database on the source server and restore it
on the destination? Do I have to run some scripts to upgrade the database to
a sp3 db?
2) Is the same also possible for a attach/detach action?
3) Does somebody have an article about what a MSSQL servicepack is actually
doing (only the master database, or also user databases)?
thanks!
Hi
Attach/Detach and Backup/Restore will both work without any issue.
If you want to see what a SP does to Master and User DB's, look at the
scripts that ship with the SP. Generally, user DB's are not touched, only the
system ones.
Running a non-SP 3 box is rather dangerous, especially with Slammer virus
around.
Cheers
Mike
"Wilfred van Dijk" wrote:

> Hi,
> I have the following situation: My source server is MSSQL 2K without
> servicepacks. My destination server is MSSQL 2K with SP3. Both running Win2K
> with sp4.
> Assume I don't want to upgrade the source server. What are my options:
> 1) Is it possible to backup the database on the source server and restore it
> on the destination? Do I have to run some scripts to upgrade the database to
> a sp3 db?
> 2) Is the same also possible for a attach/detach action?
> 3) Does somebody have an article about what a MSSQL servicepack is actually
> doing (only the master database, or also user databases)?
> thanks!

restore database form MSSQL server with no servicepack

Hi,
I have the following situation: My source server is MSSQL 2K without
servicepacks. My destination server is MSSQL 2K with SP3. Both running Win2K
with sp4.
Assume I don't want to upgrade the source server. What are my options:
1) Is it possible to backup the database on the source server and restore it
on the destination? Do I have to run some scripts to upgrade the database to
a sp3 db?
2) Is the same also possible for a attach/detach action?
3) Does somebody have an article about what a MSSQL servicepack is actually
doing (only the master database, or also user databases)?
thanks!Hi
Attach/Detach and Backup/Restore will both work without any issue.
If you want to see what a SP does to Master and User DB's, look at the
scripts that ship with the SP. Generally, user DB's are not touched, only the
system ones.
Running a non-SP 3 box is rather dangerous, especially with Slammer virus
around.
Cheers
Mike
"Wilfred van Dijk" wrote:
> Hi,
> I have the following situation: My source server is MSSQL 2K without
> servicepacks. My destination server is MSSQL 2K with SP3. Both running Win2K
> with sp4.
> Assume I don't want to upgrade the source server. What are my options:
> 1) Is it possible to backup the database on the source server and restore it
> on the destination? Do I have to run some scripts to upgrade the database to
> a sp3 db?
> 2) Is the same also possible for a attach/detach action?
> 3) Does somebody have an article about what a MSSQL servicepack is actually
> doing (only the master database, or also user databases)?
> thanks!

Restore a set of databases from a SQL2000 server to a SQL2005 server

I can restore databases one by one, setting .mdf and .ldf destination paths.

How can I restore all my Databases at the same time?

Thanks

G. Zanghi

You will either have restore or attach them one by one.

HTH, Jens K. Suessmeyer.

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

In SQL2000 i have a script that permits me to restore, at the same time, a group of databases backed up with a maintenance plan.

In SQL2005 this script doesn't run. Do you know a similar one?

Restoring one by one it's an hard work....

|||COuld you send me over the script, perhaps I can try a transistion. My EMail is accessible through my profile.

HTH, Jens K. Suessmeyer.

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

Your Email doesn't run!

check this link: fengyu.china.com/source/sp_CSS_RestoreDir.sql

It's very similar to mine.

|||My Email works, you actually will have to remove the SPAMfuscator to make it work. Which error do you get ? If you are not sure if the statements are right composed you can use the PRINT Command to print our the composed commands for debugging.

HTH, Jens K. Suessmeyer.

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

This is the output of the query:

Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
RESTORING DATABASE dataprova1
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "E:\dataprova1Data\dataprova1.mdf" failed with the operating system error 1008(An attempt was made to reference a token that does not exist.).
Msg 3156, Level 16, State 3, Line 1
File 'dataprova1ForRecovery_Data' cannot be restored to 'E:\dataprova1Data\dataprova1.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5184, Level 16, State 2, Line 1
Cannot use file 'D:\dataprova1Log\dataprova1_log.ldf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used.
Msg 3156, Level 16, State 3, Line 1
File 'dataprova1ForRecovery_Log' cannot be restored to 'D:\dataprova1Log\dataprova1_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.

Thanks!!!

Kind Regards Gianpaolo

Restore a set of databases from a SQL2000 server to a SQL2005 server

I can restore databases one by one, setting .mdf and .ldf destination paths.

How can I restore all my Databases at the same time?

Thanks

G. Zanghi

You will either have restore or attach them one by one.

HTH, Jens K. Suessmeyer.

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

In SQL2000 i have a script that permits me to restore, at the same time, a group of databases backed up with a maintenance plan.

In SQL2005 this script doesn't run. Do you know a similar one?

Restoring one by one it's an hard work....

|||COuld you send me over the script, perhaps I can try a transistion. My EMail is accessible through my profile.

HTH, Jens K. Suessmeyer.

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

Your Email doesn't run!

check this link: fengyu.china.com/source/sp_CSS_RestoreDir.sql

It's very similar to mine.

|||My Email works, you actually will have to remove the SPAMfuscator to make it work. Which error do you get ? If you are not sure if the statements are right composed you can use the PRINT Command to print our the composed commands for debugging.

HTH, Jens K. Suessmeyer.

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

This is the output of the query:

Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
RESTORING DATABASE dataprova1
Msg 5133, Level 16, State 1, Line 1
Directory lookup for the file "E:\dataprova1Data\dataprova1.mdf" failed with the operating system error 1008(An attempt was made to reference a token that does not exist.).
Msg 3156, Level 16, State 3, Line 1
File 'dataprova1ForRecovery_Data' cannot be restored to 'E:\dataprova1Data\dataprova1.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5184, Level 16, State 2, Line 1
Cannot use file 'D:\dataprova1Log\dataprova1_log.ldf' for clustered server. Only formatted files on which the cluster resource of the server has a dependency can be used.
Msg 3156, Level 16, State 3, Line 1
File 'dataprova1ForRecovery_Log' cannot be restored to 'D:\dataprova1Log\dataprova1_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.

Thanks!!!

Kind Regards Gianpaolo