SQL Server Copy Fails between 2 Win2K3 Small Business Servers
Paul Goldman
server is old, slow, and failing). My strategy is to build a new server,
copy all of the data from the old server to the new server, then shutdown
the old server, turn on the new server, and then demote and then promote all
Windows XP clients and Windows 2003 servers out of the old domain and into
the new domain (even though they both have the same name). I have installed
the new server behind a firewall. It has the same servername and domain name
as the existing server. The only difference is a new IP address
(192.168.168.x) that I will change before going live (10.0.0.x). I am trying
to copy a SQL Server database using the Copy Database Wizard (CDW) but it is
failing. From the target server I have added the old SQL server using it's
IP address (10.0.0.253) and am attempting to copy the database. I am using
SQL Server authentication on the source server (using the sa password) since
the source server does not recognize the new server as being on its network.
The wizard is failing at the step where it's trying to copy the database to
it's C drive. I suspect there may be a rights issue. Below is the log that
was generated. Any help would be greatly appreciated.
The execution of the following DTS Package succeeded:
Package Name: CDW_10.0.0.253_EXCH05-CA_4
Package Description: Copy Database Wizard Package
Package ID: {75205AC7-4335-4487-AB38-C58F5449AC71}
Package Version: {3253DAC4-BF45-4897-8574-2BC20BE0DBE3}
Package Execution Lineage: {B990C5D3-1E01-456C-87B2-A3944C5C9385}
Executed On: EXCH05-CA
Executed By: pgoldman
Execution Started: 7/18/2005 12:18:17 PM
Execution Completed: 7/18/2005 12:18:28 PM
Total Execution Time: 11.235 seconds
Package Steps execution information:
Step 'CDW Master Task Step' succeeded
Step Execution Started: 7/18/2005 12:18:17 PM
Step Execution Completed: 7/18/2005 12:18:17 PM
Total Step Execution Time: 0.344 seconds
Progress count in Step: 0
Step 'CDW MSDB Task Step' succeeded
Step Execution Started: 7/18/2005 12:18:17 PM
Step Execution Completed: 7/18/2005 12:18:18 PM
Total Step Execution Time: 0.484 seconds
Progress count in Step: 0
Step 'CDW Errors Task Step' succeeded
Step Execution Started: 7/18/2005 12:18:18 PM
Step Execution Completed: 7/18/2005 12:18:18 PM
Total Step Execution Time: 0.312 seconds
Progress count in Step: 0
Step 'CDW Databases Task Step' failed
Step Error Source: Microsoft Data Transformation Services (DTS) Package
Step Error Description:Unspecified error
Step Error code: 80004005
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100
Step Execution Started: 7/18/2005 12:18:18 PM
Step Execution Completed: 7/18/2005 12:18:28 PM
Total Step Execution Time: 10.016 seconds
Progress count in Step: 0
Task Log for this step:
-----Start Task Log-----
Error = 1 (00000001), Description = Begin to copy the database Tigerpaw Test
Error = 1 (00000001), Description = Database Tigerpaw Test has no active
connections.
Error = 1 (00000001), Description = Put the database Tigerpaw Test in single
user mode
Error = 1 (00000001), Description = Try to detach the database Tigerpaw Test
Error = 1 (00000001), Description = Successfully detached the database
Tigerpaw Test
Error = 0 (00000000), Description = Failed to copy file
c:\TigerpawBackup\Tigerpaw Test.mdf
Error = 1 (00000001), Description = Successfully attached the database
Tigerpaw Test on source
-----End Task Log-----
****************************************************************************************************
Steve Foster [SBS MVP]
>I am trying to move a Win2K3 SBS to a new hardware platform (the existing
>server is old, slow, and failing). My strategy is to build a new server,
>copy all of the data from the old server to the new server, then shutdown
>the old server, turn on the new server, and then demote and then promote
>all Windows XP clients and Windows 2003 servers out of the old domain and
>into the new domain (even though they both have the same name). I have
>installed the new server behind a firewall. It has the same servername and
>domain name as the existing server. The only difference is a new IP
>address (192.168.168.x) that I will change before going live (10.0.0.x). I
>am trying to copy a SQL Server database using the Copy Database Wizard
>(CDW) but it is failing. From the target server I have added the old SQL
>server using it's IP address (10.0.0.253) and am attempting to copy the
>database. I am using SQL Server authentication on the source server (using
>the sa password) since the source server does not recognize the new server
>as being on its network. The wizard is failing at the step where it's
>trying to copy the database to it's C drive. I suspect there may be a
>rights issue. Below is the log that was generated. Any help would be
>greatly appreciated.
>
Assuming that SQL Server is running under LocalSystem as is usually the
case, it has no rights to use network shares at all, ever. So the attempt
to copy the database to the destination server will *always* fail.
I wouldn't bother trying to use the Copy Db wizard - just detach the
database(s) manually, copy the MDF and LDF files yourself, then re-attach
on the destination server. Make sure you deal with SQL logins and users
though, since they'll be a [minor] hiccup on the path.
--
Steve Foster [SBS MVP]
---------------------------------------
MVPs do not work for Microsoft. Please reply only to the newsgroups.