Online Database Transfer

Hi.
I need to periodically copy my 3 databases to another SQL Server (both
SQL Server 2005).
I want to use "Copy Database Wizard" (mode SMO) or DTS Package
"Transfer database task" (online)
(I think booth use the same engines mechanism).
I have to use SMO/online method because my servers don't have a
network share-point.
Each of my databases has more then 100 tables (no more than 200).
During executing CDW job or DTS package I get an error:
"OnError,PC_NAME,ACCOUNT_NAME,PACKAGE_NAME,{5568F782-2BCD-4446-
BFF0-0287199B830E},{B5740EDC-D526-4B6E-BE13-E3FCCFFAA52C},
2007-02-26 22:50:08,2007-02-26 22:50:08,0,0x,ERROR :
errorCode=-1073548784 description= Executing the query "IF EXISTS (SELECT name FROM sys.databases WHERE
name = N'DEST_TABLE_NAME')
DROP DATABASE [DEST_TABLE_NAME]
" failed with the following error: "Cannot drop database
"DEST_TABLE_NAME" because it is currently in use.".
Possible failure reasons: Problems with the query, "ResultSet"
property not set correctly, parameters not set correctly,
or connection not established correctly.
helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-
E9D8-4D23-9739-DA807BCDC2AC}"
Every time the same error.
I did some research and I noticed that this error appears only if a
database has more than 49 tables.
I made a TEST databases with 49 empty and identical tables and the
process succeed. After I added another one table
(50th) then process failed.
Additionally I noticed that this error appears only on MS SLQ Server
2005 with SP1 or SP2. I was testing it on SLQ Server without Service
Pack
and I didn't get any error. (On the version without SP there is small
bug - tables loose a identity column info)
I'm waiting for any advices or suggestion ...
Greetings
gioodoHello,
The destination database must be already availble in the destinatgion server
and it should be used by some one. Best methodology
to copy the database to one server to another is:-
1. Backup the database using BACKUP DATABASE command
2. Copy the Backup (.BAK) file to destination
3.Set the destination database to Sinle user using ALTER DATABASE<dbname>
set single_user with rollback immediate
4. Restore the database using RESTORE DATABASE WITH MOVE options
5. Set the database to Multi user using ALTER DATABASE <Dbname> set
Multi_user
Thanks
Hari
<gioodo@.gmail.com> wrote in message
news:1172533698.435434.55890@.k78g2000cwa.googlegroups.com...
> Hi.
> I need to periodically copy my 3 databases to another SQL Server (both
> SQL Server 2005).
> I want to use "Copy Database Wizard" (mode SMO) or DTS Package
> "Transfer database task" (online)
> (I think booth use the same engines mechanism).
> I have to use SMO/online method because my servers don't have a
> network share-point.
> Each of my databases has more then 100 tables (no more than 200).
> During executing CDW job or DTS package I get an error:
> "OnError,PC_NAME,ACCOUNT_NAME,PACKAGE_NAME,{5568F782-2BCD-4446-
> BFF0-0287199B830E},{B5740EDC-D526-4B6E-BE13-E3FCCFFAA52C},
> 2007-02-26 22:50:08,2007-02-26 22:50:08,0,0x,ERROR :
> errorCode=-1073548784 description=> Executing the query "IF EXISTS (SELECT name FROM sys.databases WHERE
> name = N'DEST_TABLE_NAME')
> DROP DATABASE [DEST_TABLE_NAME]
> " failed with the following error: "Cannot drop database
> "DEST_TABLE_NAME" because it is currently in use.".
> Possible failure reasons: Problems with the query, "ResultSet"
> property not set correctly, parameters not set correctly,
> or connection not established correctly.
> helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-
> E9D8-4D23-9739-DA807BCDC2AC}"
> Every time the same error.
> I did some research and I noticed that this error appears only if a
> database has more than 49 tables.
> I made a TEST databases with 49 empty and identical tables and the
> process succeed. After I added another one table
> (50th) then process failed.
> Additionally I noticed that this error appears only on MS SLQ Server
> 2005 with SP1 or SP2. I was testing it on SLQ Server without Service
> Pack
> and I didn't get any error. (On the version without SP there is small
> bug - tables loose a identity column info)
> I'm waiting for any advices or suggestion ...
> Greetings
> gioodo
>