Menu
I have a table with some 5 Million rows and above 400 columns in a test level database.
I want to temporarily hold a copy of this table in the production database.
I know I can use Tasks and generate script and can choose to script the schema item and the data. This seems to take a long long time.
I could use the Tasks and generate script option to just copy table definition and process the data copy via another method.
Can I use an insert ... select .. across multiple servers with SQL server..?
I could add the test DB and server as a linked server.
I could use SSIS.
Am just asking the general forum for a recommendation that is a best (or better) approach i.e. that is faster than the generate scripts option
I am trying the Generate Scripts option but this has been running all afternoon.
I am hoping for an approach which will run in under an hour or two.
654
Steps that need to be followed are:Launch SQL Server Management Studio. Select and right-click on the Source Database, go to Tasks > Export Data. Import/Export Wizard will be opened and click on Next to proceed. Enter the data source, server name and select the authentication method and the source database.
You can use a hand truck, 4-wheel dolly or appliance dolly. If you're moving heavy furniture upstairs or down, a hand truck will be your best bet. No matter which you use, you want to make sure the item is balanced on the dolly and strapped in using any lifting straps or tie down straps.
Create SQL Server incremental backups to local file share on premises. Install the AWS DataSync agent on premises. Create DataSync tasks for continuous synchronization of file data from source to target Amazon FSx file system. Run the DataSync task.
If you are on SQL server I would tend to use the Import Export Wizard as the quick and easy method. It will fall over gracefully if there are issues.
1) Create the table in the destination database
2) Right click on the destination database and then Tasks-> Import Data
3) Connect to the Source server when prompted and then keep following the prompts
Hope it helps
97
One option is to right-click the database, then select Tasks -> Export data.
However, if you know SSIS, I'd think it's the easiest way to go, as moving data is exactly what it's intended for. You can increase the batch size there to beter suit your data volume.
As for how long it takes, that really depends on your hardware. The biggest bottlenecks most likely are your network and disks.
40
In a similar case, i will use SSIS since it gives you more control over the transfer operation. There are many tips and tricks that can be done to achieve higher performance such as importing data in chunks, configure buffer size, use bulk insert ...
I have posted many SSIS related answers that you can refer to in order to obtain more information:
42
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
