Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

Database/Object Transfer stopped with no log

My needs are for transfering 50 tables from one server to another one. I use the transfer management interface of SQL Entreprise Manager (Database/Object Transfer). I scheduled the transfer by night under the scheduled task feature. My problem is the transfer for the first 20 tables are working but after that the transfer stopped and i have nothing on the log explaining that. We have SQL Enterprise manager 6.5 and the service pack 4.0 is installed.

Do you have any idea about this problem or another technique for transferring tables or do you know a third party product?

Note: The replication is not a solution for us
0
mulus
Asked:
mulus
1 Solution
 
traygreenCommented:
Was the target device and database sized as large or larger than the source?  I haven't run transer manager as a scheduled event, but it might be the issue
0
 
mulusAuthor Commented:
The size of the target database is the same as the source.
0
 
odessaCommented:
Does the transaction log on targed Base are cutted on checkpoint?

and second the SQL Executive is not a bug free app
0
 
snyderoCommented:
I have had many mysterious failures when using transfer manager.
I don't use it any more.  Usually, foreign key constraints are the cause of un-logged failure to transfer.
----------
Solution
----------
Run this script to create a BCP out statement for each table...
-----------------------------------------------------------
-- Create a BCP command-line for each master and tmp table.
-----------------------------------------------------------
SET nocount ON
GO

SELECT   'BCP source_db..' + name + ' out ' + name + '.dat -c -Usa -T -S source_server '
FROM     sysobjects
WHERE    type = 'U'
ORDER BY 1
GO

Then clean up this output and make a batch file of it called bcp_in.bat.

Run this script to create the bcp in batch file...
-----------------------------------------------------------
-- Create a BCP command-line for each master and tmp table.
-----------------------------------------------------------
SET nocount ON
GO

SELECT   'BCP target_db..' + name + ' in ' + name + '.dat -c -Usa -T -S target_server '
FROM     sysobjects
WHERE    type = 'U'
ORDER BY 1
GO

Run this script on the source server to create the bcp in batch file and clean it up.

Delete the rows in the target database.

Create a scheduled task in the source server:
----------------------------------------
Type:     TSQL
Database: master
Command:
DECLARE @result int
EXECUTE @result = xp_cmdshell
'd:\full_path\bcp_out.bat'
IF (@result = 0)
      PRINT 'Success'
ELSE
      PRINT 'Failure'
----------------------------------------
And another in the target server...
Type:     TSQL
Database: master
Command:
DECLARE @result int
EXECUTE @result = xp_cmdshell
'd:\full_path\bcp_in.bat'
IF (@result = 0)
      PRINT 'Success'
ELSE
      PRINT 'Failure'
----------------------------------------
I the source and target servers are different, you may have to ftp the xxx.dat files from to the target.  A task can do this too.

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now