Migrate data to Azure SQL

Index

• Intro
• Problem
• Solution
• References

Intro

Today I want to show you a handy way to move data between databases hosted on premise/cloud providers.

Problem

Normally, I host my databases locally on my development machine. This is the typical approach for every single developer in the world I guess, unless you need to tackle a replica for the  production environment.

At some point, I have to take that database and restore it on the cloud or other data storage service provider. For this particular example I'll show you how to use a tool to move data from a local MSSQL server instance to an Azure SQL database.

Solution

BCP tool is a commnad line application which enables you to export and import data from SQL instances. I found this tool very useful when uploading data to Azure SQL database is required. The performance provided by BCP is great allowing me to upload 60.000 rows from my laptop to Azure in less than a minute. This always might depend on the speed of your network connection.

Following a couple of examples for export/import data.

• Export

See in this previous example how I export all the information hosted in the table called "MyTable" into a .dat file within a temporary file called "data.dat".

With "-T" I'm defining "Integrated security" as this is a local host database.

• Import

In the example before I'm using basic authentication with the parameters "-U" for user and "-P" for password.

See here a list of option you can use with BCP command line tool:

bcp [database_name.] schema.{table_name | view_name | "query"
    {in data_file | out data_file | queryout data_file | format nul}

•    [-a packet_size]
•    [-b batch_size]
•    [-c]
•    [-C { ACP | OEM | RAW | code_page } ]
•    [-d database_name]
•    [-e err_file]
•    [-E]
•    [-f format_file]
•    [-F first_row]
•    [-h"hint [,...n]"]
•    [-i input_file]
•    [-k]
•    [-K application_intent]
•    [-L last_row]
•    [-m max_errors]
•    [-n]
•    [-N]
•    [-o output_file]
•    [-P password]
•    [-q]
•    [-r row_term]
•    [-R]
•    [-S [server_name[\instance_name]]
•    [-t field_term]
•    [-T]
•    [-U login_id]
•    [-v]
•    [-V (80 | 90 | 100 | 110 | 120 | 130 ) ]
•    [-w]
•    [-x]
•    /?

References

Download Microsoft Command Line Utilities 11 for SQL Server
Microsoft Blog tutorial
Tutorial for using BCP