snippetsqlMinor
How to export a single huge table from SQL Server?
Viewed 0 times
sqlhugesingleexporthowserverfromtable
Problem
I'm given the task to export the content of a huge table from a production database and to import the data into a database at a remote location.
The Table has about 45,000,000 rows. using about 4 GB space in the database.
Its 10 Columns are of type int, datetime and varchar(n)
Select * into copy_of_table from prod_db..big_table
location
I assume that inserting into a database with simple recovery model generates only minimal logging on the new database and this way is one of the fastest possible to export the data and the impact to the production database is the least possible.
The Table has about 45,000,000 rows. using about 4 GB space in the database.
Its 10 Columns are of type int, datetime and varchar(n)
- Set its recovery mode to simple
- copy the data with
Select * into copy_of_table from prod_db..big_table
- Detach mdf file from this new database and ship it to the other
location
- Drop adhoc database
I assume that inserting into a database with simple recovery model generates only minimal logging on the new database and this way is one of the fastest possible to export the data and the impact to the production database is the least possible.
Solution
I'd of thought BCP, with a format file rather than defaults, would be your best bet.
Creating a format file
Creating a format file
Context
StackExchange Database Administrators Q#4056, answer score: 5
Revisions (0)
No revisions yet.