Copy Database Wizard Timeout Error

Hi,

I am copying a database from one server to the other. Both have SQL 2005. When using the CDW, I get the following error after 10 minutes:

Event Name: OnError

Message: An exception occurred while executing a Transact-SQL statement or batch.

StackTrace: at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand)

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(StringCollection sqlCommands)

at Microsoft.SqlServer.Dts.Tasks.TransferObjectsTask.TransferObjectsTask.TransferDatabasesUsingSMOTransfer()

InnerException-->Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

CREATE DATABASE failed. Some file names listed could not be created. Check related errors.

It always errors at 10 minutes. But, when I run the Create Database statement from my query analyzer, it works and it takes about 18 minutes.

How do I get the CDW to wait longer than 10 minutes before returning an error?

I have set my remote query timeout to 20 minutes and that didn't seem to have any effect.

Thanks,

--ExoStatic

Have you installed Service PAck 2? CDW is much improved.

Download SQL Server 2005 Service Pack 2 at:

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/servicepacks/sp2.mspx

...probably doesn't matter but the job runs on the destination server so it timeouts are relevent they may need to be set on that server.

|||

We have greatly improved SMO transfer in CDW with Service Pack 2. I strongly suggest you try that. Also, you could try using the detach/attach method instead of SMO Transfer. I believe it is faster and more reliable.

After installing Service Pack 2, please let us know if your problem persists. You may still have to change some settings on your server.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

if you use Detach/attach method you still need to migrate the Login from Source Server. You can also try Backup/Restore method which is best in such scenario. in this method also the Logins has to be taken care.

To transfer login Refer :http://support.microsoft.com/default.aspx/kb/246133

Madhu

sqlsql

Copy Database Wizard Throughput?

Ok, so I'm trying to use the CDW to copy a SQL 2000 db to SQL 2005. The
original is composed of 2 or 3 data files and 1 translog file, total of about
12GB. I connected each server -- separate boxes -- to my internal network,
the SQL 2005 has a Gbit NIC, the 2000 server a 100MBit NIC. After 10+ hours,
the CDW is still processing and I can see that the largest of the ldf files
has copied over to the 2005 server, but basically I'm wondering, what's the
metric per GB of data to move, 1Gb/per hour? Has anyone else using the CDW
seen it work faster? Just seems a bit slow to me . . .
Thanks,
Chad
Hello,
I recommend you to do:-
1. Backup the SQL 2000 database
2. Copy the Backup file to SQL 2005 server
3. Restore the Backup file. This will automatically restore and upgrade the
database to SQL 2005
Thanks
Hari
"cc" <cc@.discussions.microsoft.com> wrote in message
news:21E0DBD4-D1C1-4539-85E9-E048EF0A4EDA@.microsoft.com...
> Ok, so I'm trying to use the CDW to copy a SQL 2000 db to SQL 2005. The
> original is composed of 2 or 3 data files and 1 translog file, total of
> about
> 12GB. I connected each server -- separate boxes -- to my internal
> network,
> the SQL 2005 has a Gbit NIC, the 2000 server a 100MBit NIC. After 10+
> hours,
> the CDW is still processing and I can see that the largest of the ldf
> files
> has copied over to the 2005 server, but basically I'm wondering, what's
> the
> metric per GB of data to move, 1Gb/per hour? Has anyone else using the
> CDW
> seen it work faster? Just seems a bit slow to me . . .
> Thanks,
>
> Chad
|||On Feb 19, 8:37 pm, cc <c...@.discussions.microsoft.com> wrote:
> Ok, so I'm trying to use the CDW to copy a SQL 2000 db to SQL 2005. The
> original is composed of 2 or 3 data files and 1 translog file, total of about
> 12GB. I connected each server -- separate boxes -- to my internal network,
> the SQL 2005 has a Gbit NIC, the 2000 server a 100MBit NIC. After 10+ hours,
> the CDW is still processing and I can see that the largest of the ldf files
> has copied over to the 2005 server, but basically I'm wondering, what's the
> metric per GB of data to move, 1Gb/per hour? Has anyone else using the CDW
> seen it work faster? Just seems a bit slow to me . . .
> Thanks,
> Chad
That wizard does the equivalent of you scripting and creating each
individual database object, followed by a INSERT INTO/SELECT * for
each table. It's pretty much a toy with no real value. There are
better, accepted methods for transferring a database between servers:
1. BACKUP/RESTORE - preferred if downtime is a concern, can be done
without taking the host DB offline
2. Detach/reattach - preferred if a clean cutoff is required, host DB
is taken offline, copied to the new location, then brought back
online.
Both are much faster and simpler than the copy wizard.
|||It looks like I'll go the sp_detach route. I had tried CDW with some <100mb
dbs and it went smoothly and quickly, but this one actually croaked. Well,
to be specific, I thought maybe I had too many GUI resources open, and in
Win2003, the SQL Mgr and CDW both appear as separate graphical entities, so I
killed the SQL MGR, which took the CDW down, just like that, no warnings, no
rollback at all. Fortunately, I was able to reattach the mdf files on the
origin server. Sigh.
"Tracy McKibben" wrote:

> On Feb 19, 8:37 pm, cc <c...@.discussions.microsoft.com> wrote:
>
> That wizard does the equivalent of you scripting and creating each
> individual database object, followed by a INSERT INTO/SELECT * for
> each table. It's pretty much a toy with no real value. There are
> better, accepted methods for transferring a database between servers:
> 1. BACKUP/RESTORE - preferred if downtime is a concern, can be done
> without taking the host DB offline
> 2. Detach/reattach - preferred if a clean cutoff is required, host DB
> is taken offline, copied to the new location, then brought back
> online.
> Both are much faster and simpler than the copy wizard.
>
>

Copy Database Wizard Throughput?

Ok, so I'm trying to use the CDW to copy a SQL 2000 db to SQL 2005. The
original is composed of 2 or 3 data files and 1 translog file, total of about
12GB. I connected each server -- separate boxes -- to my internal network,
the SQL 2005 has a Gbit NIC, the 2000 server a 100MBit NIC. After 10+ hours,
the CDW is still processing and I can see that the largest of the ldf files
has copied over to the 2005 server, but basically I'm wondering, what's the
metric per GB of data to move, 1Gb/per hour? Has anyone else using the CDW
seen it work faster? Just seems a bit slow to me . . .
Thanks,
ChadHello,
I recommend you to do:-
1. Backup the SQL 2000 database
2. Copy the Backup file to SQL 2005 server
3. Restore the Backup file. This will automatically restore and upgrade the
database to SQL 2005
Thanks
Hari
"cc" <cc@.discussions.microsoft.com> wrote in message
news:21E0DBD4-D1C1-4539-85E9-E048EF0A4EDA@.microsoft.com...
> Ok, so I'm trying to use the CDW to copy a SQL 2000 db to SQL 2005. The
> original is composed of 2 or 3 data files and 1 translog file, total of
> about
> 12GB. I connected each server -- separate boxes -- to my internal
> network,
> the SQL 2005 has a Gbit NIC, the 2000 server a 100MBit NIC. After 10+
> hours,
> the CDW is still processing and I can see that the largest of the ldf
> files
> has copied over to the 2005 server, but basically I'm wondering, what's
> the
> metric per GB of data to move, 1Gb/per hour? Has anyone else using the
> CDW
> seen it work faster? Just seems a bit slow to me . . .
> Thanks,
>
> Chad|||On Feb 19, 8:37 pm, cc <c...@.discussions.microsoft.com> wrote:
> Ok, so I'm trying to use the CDW to copy a SQL 2000 db to SQL 2005. The
> original is composed of 2 or 3 data files and 1 translog file, total of about
> 12GB. I connected each server -- separate boxes -- to my internal network,
> the SQL 2005 has a Gbit NIC, the 2000 server a 100MBit NIC. After 10+ hours,
> the CDW is still processing and I can see that the largest of the ldf files
> has copied over to the 2005 server, but basically I'm wondering, what's the
> metric per GB of data to move, 1Gb/per hour? Has anyone else using the CDW
> seen it work faster? Just seems a bit slow to me . . .
> Thanks,
> Chad
That wizard does the equivalent of you scripting and creating each
individual database object, followed by a INSERT INTO/SELECT * for
each table. It's pretty much a toy with no real value. There are
better, accepted methods for transferring a database between servers:
1. BACKUP/RESTORE - preferred if downtime is a concern, can be done
without taking the host DB offline
2. Detach/reattach - preferred if a clean cutoff is required, host DB
is taken offline, copied to the new location, then brought back
online.
Both are much faster and simpler than the copy wizard.|||It looks like I'll go the sp_detach route. I had tried CDW with some <100mb
dbs and it went smoothly and quickly, but this one actually croaked. Well,
to be specific, I thought maybe I had too many GUI resources open, and in
Win2003, the SQL Mgr and CDW both appear as separate graphical entities, so I
killed the SQL MGR, which took the CDW down, just like that, no warnings, no
rollback at all. Fortunately, I was able to reattach the mdf files on the
origin server. Sigh.
"Tracy McKibben" wrote:
> On Feb 19, 8:37 pm, cc <c...@.discussions.microsoft.com> wrote:
> > Ok, so I'm trying to use the CDW to copy a SQL 2000 db to SQL 2005. The
> > original is composed of 2 or 3 data files and 1 translog file, total of about
> > 12GB. I connected each server -- separate boxes -- to my internal network,
> > the SQL 2005 has a Gbit NIC, the 2000 server a 100MBit NIC. After 10+ hours,
> > the CDW is still processing and I can see that the largest of the ldf files
> > has copied over to the 2005 server, but basically I'm wondering, what's the
> > metric per GB of data to move, 1Gb/per hour? Has anyone else using the CDW
> > seen it work faster? Just seems a bit slow to me . . .
> >
> > Thanks,
> >
> > Chad
>
> That wizard does the equivalent of you scripting and creating each
> individual database object, followed by a INSERT INTO/SELECT * for
> each table. It's pretty much a toy with no real value. There are
> better, accepted methods for transferring a database between servers:
> 1. BACKUP/RESTORE - preferred if downtime is a concern, can be done
> without taking the host DB offline
> 2. Detach/reattach - preferred if a clean cutoff is required, host DB
> is taken offline, copied to the new location, then brought back
> online.
> Both are much faster and simpler than the copy wizard.
>
>

Copy Database Wizard Throughput?

Ok, so I'm trying to use the CDW to copy a SQL 2000 db to SQL 2005. The
original is composed of 2 or 3 data files and 1 translog file, total of abou
t
12GB. I connected each server -- separate boxes -- to my internal network,
the SQL 2005 has a Gbit NIC, the 2000 server a 100MBit NIC. After 10+ hours
,
the CDW is still processing and I can see that the largest of the ldf files
has copied over to the 2005 server, but basically I'm wondering, what's the
metric per GB of data to move, 1Gb/per hour? Has anyone else using the CDW
seen it work faster? Just seems a bit slow to me . . .
Thanks,
ChadHello,
I recommend you to do:-
1. Backup the SQL 2000 database
2. Copy the Backup file to SQL 2005 server
3. Restore the Backup file. This will automatically restore and upgrade the
database to SQL 2005
Thanks
Hari
"cc" <cc@.discussions.microsoft.com> wrote in message
news:21E0DBD4-D1C1-4539-85E9-E048EF0A4EDA@.microsoft.com...
> Ok, so I'm trying to use the CDW to copy a SQL 2000 db to SQL 2005. The
> original is composed of 2 or 3 data files and 1 translog file, total of
> about
> 12GB. I connected each server -- separate boxes -- to my internal
> network,
> the SQL 2005 has a Gbit NIC, the 2000 server a 100MBit NIC. After 10+
> hours,
> the CDW is still processing and I can see that the largest of the ldf
> files
> has copied over to the 2005 server, but basically I'm wondering, what's
> the
> metric per GB of data to move, 1Gb/per hour? Has anyone else using the
> CDW
> seen it work faster? Just seems a bit slow to me . . .
> Thanks,
>
> Chad|||On Feb 19, 8:37 pm, cc <c...@.discussions.microsoft.com> wrote:
> Ok, so I'm trying to use the CDW to copy a SQL 2000 db to SQL 2005. The
> original is composed of 2 or 3 data files and 1 translog file, total of ab
out
> 12GB. I connected each server -- separate boxes -- to my internal network
,
> the SQL 2005 has a Gbit NIC, the 2000 server a 100MBit NIC. After 10+ hou
rs,
> the CDW is still processing and I can see that the largest of the ldf file
s
> has copied over to the 2005 server, but basically I'm wondering, what's th
e
> metric per GB of data to move, 1Gb/per hour? Has anyone else using the CD
W
> seen it work faster? Just seems a bit slow to me . . .
> Thanks,
> Chad
That wizard does the equivalent of you scripting and creating each
individual database object, followed by a INSERT INTO/SELECT * for
each table. It's pretty much a toy with no real value. There are
better, accepted methods for transferring a database between servers:
1. BACKUP/RESTORE - preferred if downtime is a concern, can be done
without taking the host DB offline
2. Detach/reattach - preferred if a clean cutoff is required, host DB
is taken offline, copied to the new location, then brought back
online.
Both are much faster and simpler than the copy wizard.|||It looks like I'll go the sp_detach route. I had tried CDW with some <100mb
dbs and it went smoothly and quickly, but this one actually croaked. Well,
to be specific, I thought maybe I had too many GUI resources open, and in
Win2003, the SQL Mgr and CDW both appear as separate graphical entities, so
I
killed the SQL MGR, which took the CDW down, just like that, no warnings, no
rollback at all. Fortunately, I was able to reattach the mdf files on the
origin server. Sigh.
"Tracy McKibben" wrote:

> On Feb 19, 8:37 pm, cc <c...@.discussions.microsoft.com> wrote:
>
> That wizard does the equivalent of you scripting and creating each
> individual database object, followed by a INSERT INTO/SELECT * for
> each table. It's pretty much a toy with no real value. There are
> better, accepted methods for transferring a database between servers:
> 1. BACKUP/RESTORE - preferred if downtime is a concern, can be done
> without taking the host DB offline
> 2. Detach/reattach - preferred if a clean cutoff is required, host DB
> is taken offline, copied to the new location, then brought back
> online.
> Both are much faster and simpler than the copy wizard.
>
>

Copy Database Wizard problem

Hi
Just a quick question: I often perform CDW from one server to the next as
part of clients server upgrades. All works fine:
The only major headache i am getting is that when i select the logins to be
copied as part of the CDW (including databases), the logins get copied
over, but their default database in the logins console is pointing to master
database and not the correct database compared to the old server? Sometimes
it works and sometimes it don't. I am not sure if i am doing anything
wrong.
Both servers are SQL 2000 inc SP3a using the same domain account. The
databases do not exist on the new server before the CDW.
Within the database itself, the alias Name has the correct assigned Login
Name
Can anyone give me some pointers?
Regards
J
Most likely you need to check to see if the dbid of the target databases are the same on both servers.
|||Ken
All dbid are the same, including same DBO and collation sort.
"Ken Dutton" <fj60landcruiser@.yahoo.com> wrote in message
news:22A8C534-A6D6-400C-BABB-22404154062A@.microsoft.com...
> Most likely you need to check to see if the dbid of the target databases
are the same on both servers.
|||This is documented in fhe following article:
PRB: The Copy Database Wizard Does Not Copy the Default Database
Information for Logins
http://support.microsoft.com/?id=331450
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Many thanks for link, this is just what i am looking for.
Thanks
J
"James" <hush@.dontspamme.com> wrote in message
news:uOW72LjQEHA.3732@.TK2MSFTNGP11.phx.gbl...
> Hi
> Just a quick question: I often perform CDW from one server to the next as
> part of clients server upgrades. All works fine:
> The only major headache i am getting is that when i select the logins to
be
> copied as part of the CDW (including databases), the logins get copied
> over, but their default database in the logins console is pointing to
master
> database and not the correct database compared to the old server?
Sometimes
> it works and sometimes it don't. I am not sure if i am doing anything
> wrong.
> Both servers are SQL 2000 inc SP3a using the same domain account. The
> databases do not exist on the new server before the CDW.
> Within the database itself, the alias Name has the correct assigned Login
> Name
> Can anyone give me some pointers?
> Regards
> J
>
>
sqlsql

Copy Database Wizard problem

Hi
Just a quick question: I often perform CDW from one server to the next as
part of clients server upgrades. All works fine:
The only major headache i am getting is that when i select the logins to be
copied as part of the CDW (including databases), the logins get copied
over, but their default database in the logins console is pointing to master
database and not the correct database compared to the old server? Sometimes
it works and sometimes it don't. I am not sure if i am doing anything
wrong.
Both servers are SQL 2000 inc SP3a using the same domain account. The
databases do not exist on the new server before the CDW.
Within the database itself, the alias Name has the correct assigned Login
Name
Can anyone give me some pointers?
Regards
JMost likely you need to check to see if the dbid of the target databases are the same on both servers.|||Ken
All dbid are the same, including same DBO and collation sort.
"Ken Dutton" <fj60landcruiser@.yahoo.com> wrote in message
news:22A8C534-A6D6-400C-BABB-22404154062A@.microsoft.com...
> Most likely you need to check to see if the dbid of the target databases
are the same on both servers.|||This is documented in fhe following article:
PRB: The Copy Database Wizard Does Not Copy the Default Database
Information for Logins
http://support.microsoft.com/?id=331450
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Many thanks for link, this is just what i am looking for.
Thanks
J
"James" <hush@.dontspamme.com> wrote in message
news:uOW72LjQEHA.3732@.TK2MSFTNGP11.phx.gbl...
> Hi
> Just a quick question: I often perform CDW from one server to the next as
> part of clients server upgrades. All works fine:
> The only major headache i am getting is that when i select the logins to
be
> copied as part of the CDW (including databases), the logins get copied
> over, but their default database in the logins console is pointing to
master
> database and not the correct database compared to the old server?
Sometimes
> it works and sometimes it don't. I am not sure if i am doing anything
> wrong.
> Both servers are SQL 2000 inc SP3a using the same domain account. The
> databases do not exist on the new server before the CDW.
> Within the database itself, the alias Name has the correct assigned Login
> Name
> Can anyone give me some pointers?
> Regards
> J
>
>

Copy Database Wizard problem

Hi
Just a quick question: I often perform CDW from one server to the next as
part of clients server upgrades. All works fine:
The only major headache i am getting is that when i select the logins to be
copied as part of the CDW (including databases), the logins get copied
over, but their default database in the logins console is pointing to master
database and not the correct database compared to the old server? Sometimes
it works and sometimes it don't. I am not sure if i am doing anything
wrong.
Both servers are SQL 2000 inc SP3a using the same domain account. The
databases do not exist on the new server before the CDW.
Within the database itself, the alias Name has the correct assigned Login
Name
Can anyone give me some pointers?
Regards
JMost likely you need to check to see if the dbid of the target databases are
the same on both servers.|||Ken
All dbid are the same, including same DBO and collation sort.
"Ken Dutton" <fj60landcruiser@.yahoo.com> wrote in message
news:22A8C534-A6D6-400C-BABB-22404154062A@.microsoft.com...
> Most likely you need to check to see if the dbid of the target databases
are the same on both servers.|||This is documented in fhe following article:
PRB: The Copy Database Wizard Does Not Copy the Default Database
Information for Logins
http://support.microsoft.com/?id=331450
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Many thanks for link, this is just what i am looking for.
Thanks
J
"James" <hush@.dontspamme.com> wrote in message
news:uOW72LjQEHA.3732@.TK2MSFTNGP11.phx.gbl...
> Hi
> Just a quick question: I often perform CDW from one server to the next as
> part of clients server upgrades. All works fine:
> The only major headache i am getting is that when i select the logins to
be
> copied as part of the CDW (including databases), the logins get copied
> over, but their default database in the logins console is pointing to
master
> database and not the correct database compared to the old server?
Sometimes
> it works and sometimes it don't. I am not sure if i am doing anything
> wrong.
> Both servers are SQL 2000 inc SP3a using the same domain account. The
> databases do not exist on the new server before the CDW.
> Within the database itself, the alias Name has the correct assigned Login
> Name
> Can anyone give me some pointers?
> Regards
> J
>
>

Copy Database Wizard

I am attempting to use the CDW, to copy a database on my
laptop running SQL server 2000 PE to a server running SQL
Server 2000 standard edition. I think that I have set
all of the appropriate permissions/authentications etc.
I have run it both from EM and the command line but it
fails to copy the source .mdf file. All other function
execute properly ie the share is created, single user
mode invoked, etc etc. I have tried changing the name of
the destination file etc and it still results in a
failure to copy the source file error. Is this yet
another limitation of the PE or am I not doing something
correctly?
Thanks
The copy database wizard uses detach, copy and attach, and there aren't any
issues going up a version that I know of (going down is different if EG you
used partitioned views then downgraded to Personal Edition):
http://www.winnetmag.com/Articles/Ar...91/pg/2/2.html
http://support.microsoft.com/default...;en-us;q268361
Can you try detaching manually, making a copy of the file, copying the file
over to the share on the EE then attaching manually.
Hopefully this should establish the point of error.
HTH,
Paul Ibison
|||Will give it a try, thanks
>--Original Message--
>The copy database wizard uses detach, copy and attach,
and there aren't any
>issues going up a version that I know of (going down is
different if EG you
>used partitioned views then downgraded to Personal
Edition):
>http://www.winnetmag.com/Articles/Ar.../21391/pg/2/2.
html
>http://support.microsoft.com/default.aspx?scid=kb;en-
us;q268361
>Can you try detaching manually, making a copy of the
file, copying the file
>over to the share on the EE then attaching manually.
>Hopefully this should establish the point of error.
>HTH,
>Paul Ibison
>
>.
>
|||That worked, thanks[vbcol=seagreen]
>--Original Message--
>Will give it a try, thanks
>and there aren't any
>different if EG you
>Edition):
..
>html
>us;q268361
>file, copying the file
>.
>