Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Copy a big table (250G) form one server to another Expand / Collapse
Author
Message
Posted Monday, March 4, 2013 8:42 PM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 1:31 AM
Points: 553, Visits: 1,573
Jeff Moden (3/4/2013)
In that case, you might try what SQL Server itself uses for replication if you have the disk space. BCP in native format. Export the table to a file, then import it in half million (or less depending on the size of your rows) row chunks using the batch size setting. It will keep your log file from exploding.

As an alternative, you could have the infrastructure folks do what I believe is called a "SAN Snapshot" to copy the mdf and ldf files and then simply attach the database.


As jeff sugested , taking a SAN snapshot and then attaching the mdf / ldf file could save time and will be a good approach.


"More Green More Oxygen !! Plant a tree today"
Post #1426577
Posted Monday, March 4, 2013 9:55 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 3:05 AM
Points: 155, Visits: 1,019
I have some recommends here:
1-Choose the point time when your server does not work hard
2-Separate your table into smaller pieces (may be multiple files) and using a package to transform data
Post #1426590
Posted Wednesday, March 6, 2013 4:10 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 6:51 AM
Points: 232, Visits: 828
Did you try Import/export option of SQL when server is not too busy?
Post #1427288
Posted Wednesday, March 6, 2013 8:23 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 6, 2013 8:23 AM
Points: 12, Visits: 34
Hi,

We decided to insert the info using

BEGIN TRANSACTION

INSERT INTO DB2.dbo.Table
SELECT *
FROM DB1.dbo.Table
WHERE CodigoCaso < @cont and CodigoCaso > @ultimo

SET @ultimo = @cont
SET @cont = @cont + 1000

COMMIT TRANSACTION

In order to avoid a huge increase in the log file.

I will post the result

Thanks
Post #1427452
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse