Dfyjkt

I have a 170 GB table that is no longer referenced.

I want to move that table to another server. I tried these options in staging:

• select into 8 GB table to another db - 1.47 min


• SSIS package - 6 min

Can anyone suggest best way to do it? How can I break table into parts so I can make small packages and upload it?

I would use:

1) backup (copy-only) & restore the database on the same server to make this db available locally -> copy records into the other database. No option if the database itself is too large.

2) use bcp out/in. Use QueryOut if you want to specify the records to be exported yourself (partition on PK value)

As recommended on the other answer, I would suggest using a backup, maybe you already have a FULL backup from last night ready to be used (if not, I will suggest to immediately configure a job to run FULL backups at least on a weekly basis). You could run a backup with copy-only option that allows you to run a backup and don't break your backup chain.

BACKUP DATABASE your_database
TO DISK = '\your_driveBAKyour_database_copy_only_backup.bak'
WITH COPY_ONLY;

Once you have that BAK file, you can restore on your staging environment and do everything you need.

You commented that you database is 450GB, why that could be a problem? Don't you have enough space on the staging environment? If that is the case, then you will have a somewhat different approach. Maybe you could create a job that moves data from your prod server to the staging server and let it run in hours where your server has low traffic. If the server is not under heavy load, you could have 2 schedules, one that runs during the day, moving less amount of data and one at night moving more data.

And I guess once you have that table available on staging you will want to delete it from your PROD environment. Check this answer for some advice on how to delete it and recover the space, if needed. The code there refers to UPDATES, but for DELETE's is the same, here a sample to run a delete in chunks.

WHILE (1=1)
BEGIN
 WAITFOR DELAY '00:00:01'

 DELETE TOP(10000)
 FROM [dbo].[myTable]
 WHERE --condition here

 IF @@ROWCOUNT < 1 BREAK
END

If you need table from Production for test purpose?

• Back up your database WITH COPY_ONLY, COMPRESSION and .bak file copy to another server (must need enough space and better to copy .bak over share drives)


• Restore DB to another server (restore with new name if needed)


• Drop all of the tables you don't want.

BACKUP DATABASE db_name TO DISK = db_filename WITH COPY_ONLY, COMPRESSION 

Backup the database and send that .bak over test environments.