1

I have a table with around 400 GB data I want to copy some data to some other temporary database to make some more space. Can you please give me a efficient way to do that?

My DBMS is SQL server 2014 standard edition.

5
  • copying to temp table will not reclaim space,can you please further clarify on what you are trying to do – TheGameiswar Feb 22 '16 at 9:37
  • What should happen with the data you copy "away"? Is this "dead" archive data, or just "rarely needed" data, or do you want to access this "as ever"? Is this data connected to other data (foreign keys, other constraints)? Is this just "one huge table" or a fully structured tree? What kind of data (BLOBs?)... – Shnugo Feb 22 '16 at 10:09
  • Well purpose to copy data to make an archive of old data and keep recent data which major problem is everyday backup which is around 750 GB total database space if i copy data from source db to temp db like 300GB data and delete that from source db which is production db. – Tahir Feb 22 '16 at 10:14
  • its not blob data normal data – Tahir Feb 22 '16 at 10:57
  • The better way to copy a lot of data int SQL Server is through SSIS. – Krismorte Feb 22 '16 at 11:42
1

You want to use a minimally logged inserts. If your temp/scratch database is on the same instance you can simply do.

SELECT * INTO DATABASE.SCHEMA.TargetTable FROM SourceTable;

The above statement create the table in the target database and bulk insert the data.

If you want to copy the data to a different instance you are best off using SSIS.

2
0

Is the source table partitioned? If so, partition switching is a great way to move large blocks of rows from a "hot" table to an "archive" one.

At that size I would expect the DB to have many files. You could put old data in one file, make it read only and perform file backups of the hot data rather than full backups of the whole DB. Restore gets more complicated, however, and you would want to practice regularly to ensure you can get it right when needed.

BCP is a handy utility for copying chunks of data. Though much less capable than SSIS it requires less setup.

And investigate the various compression options available. We've found that queries can be faster due to the reduced IO. YMMV, of course.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy