April 28, 2004 at 7:27 am
Hi,
I am migrating data between two database servers. These servers can't be linked.
I installed the databases on the new server several weeks ago, and the live database server has been frozen. I need to transfer all the data written to each database table in the last 3 weeks.
I'm not sure how to go about this. Should I use bcp, or Bulk insert or DTS?
Can anyone post some code examples of how to accomplish this.
Thanks,
John
April 28, 2004 at 8:12 am
I have always used BCP to export and bulk insert to import. It may not be the easiest way, but for me it is the fastest to transfer big amount of data.
to export:
BCP "dbname.dbo.TableName" OUT TableName.txt -Usa -Ppassword -N
you can create a query to export all the tables you want.
Something like this:
DECLARE @TableName AS SYSNAME, @cmd AS VARCHAR(500)
DECLARE curTables CURSOR FOR
SELECT name FROM sysobjects WHERE xtype = 'U'
OPEN curTables
FETCH NEXT FROM curTables INTO @TableName
WHILE (@@FETCH_STATUS = 0)
BEGIN
SELECT @cmd = 'bcp "dbname.dbo.' + @TableName + '" OUT ' + @TableName + '.txt -Usa -Ppassword -N"'
exec master.dbo.xp_cmdshell @cmd, NO_OUTPUT
END
CLOSE curTables
DEALLOCATE curTables
And to import
BULK INSERT NewTableName FROM 'PathToFile\TableName.txt' WITH DATAFILETYPE = 'WIDENATIVE'.
Here you can use something similar to the code I wrote before.
April 29, 2004 at 5:20 am
Hi,
what about dumping (backing up) the database(s) and restoring it on the target server. Certainly not as fast as the bcp approach but much easier. You can simply set new directories for your files (mdf, ndf, ldf) if it's necessary in the restore dialog. The restore process is quite slow, but if you are not familiar with the bcp tool, you might waste a lot of time.
Also consider detaching on the source and attaching on the target server.
Good luck !
Michael
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply