AWS : RDS Importing and Exporting SQL Server Data

To demonstrate import/export, we'll use two RDS MS SQL instances on RDS as the first example. Next, we'll export a table from localhost (PC) to a RDS instance.

Before we learn how to import/export data, we want to create a database and table.

Select "New Database..." from "Databases":

Now we've just created "TestingSQL" database:

Let's create a table:

"Save":

Put some data into the table:

Now we want to export the table to another MS SQL server on RDS. We'll use SQL Server Management Studio (SSMS).

Right click on DB name => Tasks => Export Data...:

Click Next. Then, in the following dialog, we need to specify the data source:

Then, target (destination):

To create a new database in the destination SQL server, click New...:

Notice the wizard inserted the new database name:

Now, let's open our destination SQL server on RDS:

We can see the target DB, "CopiedDB". It has been successfully exported from the source:

The process is almost the same as exporting from RDS to RDS:

The Import and Export Wizard creates a special Integration Services package, which we can use to copy data from our local SQL Server database to the destination DB Instance. The wizard can filter which tables and even which tuples within a table are copied to the destination DB Instance.

Note:
The Import and Export Wizard works well for large datasets, but it may not be the fastest way to remotely export data from our local deployment. For an even faster way, we may want to consider the SQL Server bulk copy feature.

The SQL Server bulk copy feature is an efficient means of copying data from a source database to our DB Instance. Bulk copy writes the data that we specify to a data file, such as an ASCII file. We can then run bulk copy again to write the contents of the file to the destination DB Instance.

This section uses the bcp utility, which is included with all editions of SQL Server.

From the command line on a machine with SQL Server installed, type bcp and press Enter.:

C:\>bcp ?
usage: bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-x generate xml format file]
  [-d database name]        [-K application intent]

C:\>

Data file download from codeplex.

On Query pane of SQL Server Management Studio, here are the scripts to create and populate a table:

Create TABLE ExportTab(ID INT, Name varchar(20))

INSERT INTO ExportTab VALUES(1,'McDowell'), (2, 'McCain')

select * from ExportTab

Execute one by one, and then, on command window, we use bcp to push data out:

PS C:\> bcp LocalSQLDB.dbo.ExportTab out ExportTab.txt -S localhost\SQLEXPRESS -c -T
Starting copy...

2 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 16     Average : (125.00 rows per sec.)

The "ExportTab.txt" looks like this:

1	McDowell  
2	McCain    

First, we need to drop the table, and then recreate the table:

DROP TABLE ExportTab;
CREATE TABLE ExportTab(ID INT, NAME VARCHAR(10));

We can check there is no data in the table:

Select * from ExportTab;

Then, we use the bcp in reverse by using "in" instated of "out":

PS C:\> bcp LocalSQLDB.dbo.ExportTab in ExportTab.txt -S localhost\SQLEXPRESS -c -T

Starting copy...

2 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 250    Average : (8.00 rows per sec.)

Let's see if the data is back:

Select * from ExportTab;

Now we see there is our data back into the table:

We can use System administrator (sa) instead of Windows authentication.

The system administrator (sa) is a special login provided for backward compatibility. By default, it is assigned to the sysadmin fixed server role and cannot be changed. Although sa is a built-in administrator login, do not use it routinely.

PS C:\> bcp LocalSQLDB.dbo.ExportTab out ExportTab.txt -S localhost\SQLEXPRESS -U SAlogin -P password -c -T

Starting copy...

2 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1      Average : (2000.00 rows per sec.)

After we generate the data file, if we have created the database and schema on the target DB Instance, we can upload the data to our DB Instance by using a similar command. In this case, we will use the in argument to specify an input file instead of out to specify an output file. Instead of using localhost to specify the local SQL Server instance, we will specify the endpoint of our DB Instance. If we use a port other than 1433, we will specify that, too. The user name and password will be those of the master user and password for our DB Instance. The syntax is as follows:

bcp dbname.schema_name.table_name in C:\table_name.txt -n -S endpoint,port -U master_user_name -P master_user_password -b 10000

In our case, the real bcp command looks like this:

bcp TestDB.dbo.ExportTab in C:\ExportTab.txt -n -S sql-2008-r2-t1.ca1jql6qyqdz.us-west-1.rds.amazonaws.com -U administrator -P Hodoo2015 -b 10000

AWS (Amazon Web Services)

1. AWS : EKS (Elastic Container Service for Kubernetes)
2. AWS : Creating a snapshot (cloning an image)
3. AWS : Attaching Amazon EBS volume to an instance
4. AWS : Adding swap space to an attached volume via mkswap and swapon
5. AWS : Creating an EC2 instance and attaching Amazon EBS volume to the instance using Python boto module with User data
6. AWS : Creating an instance to a new region by copying an AMI
7. AWS : S3 (Simple Storage Service) 1
8. AWS : S3 (Simple Storage Service) 2 - Creating and Deleting a Bucket
9. AWS : S3 (Simple Storage Service) 3 - Bucket Versioning
10. AWS : S3 (Simple Storage Service) 4 - Uploading a large file
11. AWS : S3 (Simple Storage Service) 5 - Uploading folders/files recursively
12. AWS : S3 (Simple Storage Service) 6 - Bucket Policy for File/Folder View/Download
13. AWS : S3 (Simple Storage Service) 7 - How to Copy or Move Objects from one region to another
14. AWS : S3 (Simple Storage Service) 8 - Archiving S3 Data to Glacier
15. AWS : Creating a CloudFront distribution with an Amazon S3 origin
16. AWS : Creating VPC with CloudFormation
17. AWS : WAF (Web Application Firewall) with preconfigured CloudFormation template and Web ACL for CloudFront distribution
18. AWS : CloudWatch & Logs with Lambda Function / S3
19. AWS : Lambda Serverless Computing with EC2, CloudWatch Alarm, SNS
20. AWS : Lambda and SNS - cross account
21. AWS : CLI (Command Line Interface)
22. AWS : CLI (ECS with ALB & autoscaling)
23. AWS : ECS with cloudformation and json task definition
24. AWS Application Load Balancer (ALB) and ECS with Flask app
25. AWS : Load Balancing with HAProxy (High Availability Proxy)
26. AWS : VirtualBox on EC2
27. AWS : NTP setup on EC2
28. AWS: jq with AWS
29. AWS & OpenSSL : Creating / Installing a Server SSL Certificate
30. AWS : OpenVPN Access Server 2 Install
31. AWS : VPC (Virtual Private Cloud) 1 - netmask, subnets, default gateway, and CIDR
32. AWS : VPC (Virtual Private Cloud) 2 - VPC Wizard
33. AWS : VPC (Virtual Private Cloud) 3 - VPC Wizard with NAT
34. DevOps / Sys Admin Q & A (VI) - AWS VPC setup (public/private subnets with NAT)
35. AWS - OpenVPN Protocols : PPTP, L2TP/IPsec, and OpenVPN
36. AWS : Autoscaling group (ASG)
37. AWS : Setting up Autoscaling Alarms and Notifications via CLI and Cloudformation
38. AWS : Adding a SSH User Account on Linux Instance
39. AWS : Windows Servers - Remote Desktop Connections using RDP
40. AWS : Scheduled stopping and starting an instance - python & cron
41. AWS : Detecting stopped instance and sending an alert email using Mandrill smtp
42. AWS : Elastic Beanstalk with NodeJS
43. AWS : Elastic Beanstalk Inplace/Rolling Blue/Green Deploy
44. AWS : Identity and Access Management (IAM) Roles for Amazon EC2
45. AWS : Identity and Access Management (IAM) Policies, sts AssumeRole, and delegate access across AWS accounts
46. AWS : Identity and Access Management (IAM) sts assume role via aws cli2
47. AWS : Creating IAM Roles and associating them with EC2 Instances in CloudFormation
48. AWS Identity and Access Management (IAM) Roles, SSO(Single Sign On), SAML(Security Assertion Markup Language), IdP(identity provider), STS(Security Token Service), and ADFS(Active Directory Federation Services)
49. AWS : Amazon Route 53
50. AWS : Amazon Route 53 - DNS (Domain Name Server) setup
51. AWS : Amazon Route 53 - subdomain setup and virtual host on Nginx
52. AWS Amazon Route 53 : Private Hosted Zone
53. AWS : SNS (Simple Notification Service) example with ELB and CloudWatch
54. AWS : Lambda with AWS CloudTrail
55. AWS : SQS (Simple Queue Service) with NodeJS and AWS SDK
56. AWS : Redshift data warehouse
57. AWS : CloudFormation
58. AWS : CloudFormation Bootstrap UserData/Metadata
59. AWS : CloudFormation - Creating an ASG with rolling update
60. AWS : Cloudformation Cross-stack reference
61. AWS : OpsWorks
62. AWS : Network Load Balancer (NLB) with Autoscaling group (ASG)
63. AWS CodeDeploy : Deploy an Application from GitHub
64. AWS EC2 Container Service (ECS)
65. AWS EC2 Container Service (ECS) II
66. AWS Hello World Lambda Function
67. AWS Lambda Function Q & A
68. AWS Node.js Lambda Function & API Gateway
69. AWS API Gateway endpoint invoking Lambda function
70. AWS API Gateway invoking Lambda function with Terraform
71. AWS API Gateway invoking Lambda function with Terraform - Lambda Container
72. Amazon Kinesis Streams
73. AWS: Kinesis Data Firehose with Lambda and ElasticSearch
74. Amazon DynamoDB
75. Amazon DynamoDB with Lambda and CloudWatch
76. Loading DynamoDB stream to AWS Elasticsearch service with Lambda
77. Amazon ML (Machine Learning)
78. Simple Systems Manager (SSM)
79. AWS : RDS Connecting to a DB Instance Running the SQL Server Database Engine
80. AWS : RDS Importing and Exporting SQL Server Data
81. AWS : RDS PostgreSQL & pgAdmin III
82. AWS : RDS PostgreSQL 2 - Creating/Deleting a Table
83. AWS : MySQL Replication : Master-slave
84. AWS : MySQL backup & restore
85. AWS RDS : Cross-Region Read Replicas for MySQL and Snapshots for PostgreSQL
86. AWS : Restoring Postgres on EC2 instance from S3 backup
87. AWS : Q & A
88. AWS : Security
89. AWS : Security groups vs. network ACLs
90. AWS : Scaling-Up
91. AWS : Networking
92. AWS : Single Sign-on (SSO) with Okta
93. AWS : JIT (Just-in-Time) with Okta