Menu
Amazon Relational Database Service
User Guide (API Version 2014-10-31)

Importing and Exporting SQL Server Data Using Other Methods

Following, you can find information about importing your Microsoft SQL Server data to Amazon RDS, and exporting your data from an Amazon RDS DB instance running SQL Server, by using snapshots.

If your scenario supports it, it is easier to move data in and out of Amazon RDS by using the native backup and restore functionality. For more information, see Importing and Exporting SQL Server Databases.

Note

Amazon RDS for Microsoft SQL Server does not support importing data into the msdb database.

Importing Data into SQL Server on Amazon RDS by Using a Snapshot

To import data into a SQL Server DB instance by using a snapshot

  1. Create a DB instance. For more information, see Creating a DB Instance Running the Microsoft SQL Server Database Engine.

  2. Stop applications from accessing the destination DB instance.

    If you prevent access to your DB instance while you are importing data, data transfer is faster. Additionally, you won't need to worry about conflicts while data is being loaded if other applications cannot write to the DB instance at the same time. If something goes wrong and you have to roll back to a prior database snapshot, the only changes that you lose are the imported data, which you can import again after you resolve the issue.

    For information about controlling access to your DB instance, see Working with DB Security Groups (EC2-Classic Platform).

  3. Create a snapshot of the target database.

    If the target database is already populated with data, we recommend that you take a snapshot of the database before you import the data. If something goes wrong with the data import or you want to discard the changes, you can restore the database to its previous state by using the snapshot. For information about database snapshots, see Creating a DB Snapshot.

    Note

    When you take a database snapshot, I/O operations to the database are suspended for about 10 seconds while the backup is in progress.

  4. Disable automated backups on the target database.

    Disabling automated backups on the target DB instance will improve performance while you are importing your data because Amazon RDS doesn't log transactions when automatic backups are disabled. However, there are some things to consider. Because automated backups are required to perform a point-in-time recovery, you won't be able to restore the database to a specific point in time while you are importing data. Additionally, any automated backups that were created on the DB instance are erased. You can still use previous snapshots to recover the database, and any snapshots that you have taken will remain available. For information about automated backups, see Working With Backups.

  5. Disable foreign key constraints, if applicable.

    If you need to disable foreign key constraints, you can do so with the following script.

    Copy
    --Disable foreign keys on all tables DECLARE @table_name SYSNAME; DECLARE @cmd NVARCHAR(MAX); DECLARE table_cursor CURSOR FOR SELECT name FROM sys.tables; OPEN table_cursor; FETCH NEXT FROM table_cursor INTO @table_name; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @cmd = 'ALTER TABLE '+QUOTENAME(@table_name)+' NOCHECK CONSTRAINT ALL'; EXEC (@cmd); FETCH NEXT FROM table_cursor INTO @table_name; END CLOSE table_cursor; DEALLOCATE table_cursor; GO
  6. Drop indexes, if applicable.

  7. Disable triggers, if applicable.

    If you need to disable triggers, you can do so with the following script.

    Copy
    --Disable triggers on all tables DECLARE @enable BIT = 0; DECLARE @trigger SYSNAME; DECLARE @table SYSNAME; DECLARE @cmd NVARCHAR(MAX); DECLARE trigger_cursor CURSOR FOR SELECT trigger_object.name trigger_name, table_object.name table_name FROM sysobjects trigger_object JOIN sysobjects table_object ON trigger_object.parent_obj = table_object.id WHERE trigger_object.type = 'TR'; OPEN trigger_cursor; FETCH NEXT FROM trigger_cursor INTO @trigger, @table; WHILE @@FETCH_STATUS = 0 BEGIN IF @enable = 1 SET @cmd = 'ENABLE '; ELSE SET @cmd = 'DISABLE '; SET @cmd = @cmd + ' TRIGGER dbo.'+QUOTENAME(@trigger)+' ON dbo.'+QUOTENAME(@table)+' '; EXEC (@cmd); FETCH NEXT FROM trigger_cursor INTO @trigger, @table; END CLOSE trigger_cursor; DEALLOCATE trigger_cursor; GO
  8. Query the source SQL Server instance for any logins that you want to import to the destination DB instance.

    SQL Server stores logins and passwords in the master database. Because Amazon RDS doesn't grant access to the master database, you cannot directly import logins and passwords into your destination DB instance. Instead, you must query the master database on the source SQL Server instance to generate a data definition language (DDL) file that includes all logins and passwords that you want to add to the destination DB instance, and also role memberships and permissions that you want to transfer.

    For information about querying the master database, see How to Transfer the Logins and the Passwords Between Instances of SQL Server 2005 and SQL Server 2008 in the Microsoft Knowledge Base.

    The output of the script is another script that you can run on the destination DB instance. The script in the Knowledge Base article has the following code:

    Copy
    p.type IN

    Every place p.type appears, use the following code instead:

    Copy
    p.type = 'S'
  9. Import the data using the method in Import the Data.

  10. Grant applications access to the target DB instance.

    When your data import is complete, you can grant access to the DB instance to those applications that you blocked during the import. For information about controlling access to your DB instance, see Working with DB Security Groups (EC2-Classic Platform).

  11. Enable automated backups on the target DB instance.

    For information about automated backups, see Working With Backups.

  12. Enable foreign key constraints.

    If you disabled foreign key constraints earlier, you can now enable them with the following script.

    Copy
    --Enable foreign keys on all tables DECLARE @table_name SYSNAME; DECLARE @cmd NVARCHAR(MAX); DECLARE table_cursor CURSOR FOR SELECT name FROM sys.tables; OPEN table_cursor; FETCH NEXT FROM table_cursor INTO @table_name; WHILE @@FETCH_STATUS = 0 BEGIN SELECT @cmd = 'ALTER TABLE '+QUOTENAME(@table_name)+' CHECK CONSTRAINT ALL'; EXEC (@cmd); FETCH NEXT FROM table_cursor INTO @table_name; END CLOSE table_cursor; DEALLOCATE table_cursor;
  13. Enable indexes, if applicable.

  14. Enable triggers, if applicable.

    If you disabled triggers earlier, you can now enable them with the following script.

    Copy
    --Enable triggers on all tables DECLARE @enable BIT = 1; DECLARE @trigger SYSNAME; DECLARE @table SYSNAME; DECLARE @cmd NVARCHAR(MAX); DECLARE trigger_cursor CURSOR FOR SELECT trigger_object.name trigger_name, table_object.name table_name FROM sysobjects trigger_object JOIN sysobjects table_object ON trigger_object.parent_obj = table_object.id WHERE trigger_object.type = 'TR'; OPEN trigger_cursor; FETCH NEXT FROM trigger_cursor INTO @trigger, @table; WHILE @@FETCH_STATUS = 0 BEGIN IF @enable = 1 SET @cmd = 'ENABLE '; ELSE SET @cmd = 'DISABLE '; SET @cmd = @cmd + ' TRIGGER dbo.'+QUOTENAME(@trigger)+' ON dbo.'+QUOTENAME(@table)+' '; EXEC (@cmd); FETCH NEXT FROM trigger_cursor INTO @trigger, @table; END CLOSE trigger_cursor; DEALLOCATE trigger_cursor;

Import the Data

Microsoft SQL Server Management Studio is a graphical SQL Server client that is included in all Microsoft SQL Server editions except the Express Edition. SQL Server Management Studio Express is available from Microsoft as a free download. To find this download, see the Microsoft website.

Note

SQL Server Management Studio is available only as a Windows-based application.

SQL Server Management Studio includes the following tools, which are useful in importing data to a SQL Server DB instance:

  • Generate and Publish Scripts Wizard

  • Import and Export Wizard

  • Bulk copy

Generate and Publish Scripts Wizard

The Generate and Publish Scripts Wizard creates a script that contains the schema of a database, the data itself, or both. If you generate a script for a database in your local SQL Server deployment, you can then run the script to transfer the information that it contains to an Amazon RDS DB instance.

Note

For databases of 1 GB or larger, it is more efficient to script only the database schema and then use the Import and Export Wizard or the bulk copy feature of SQL Server to transfer the data.

For detailed information about the Generate and Publish Scripts Wizard, see the Microsoft SQL Server documentation.

In the wizard, pay particular attention to the advanced options on the Set Scripting Options page to ensure that everything you want your script to include is selected. For example, by default, database triggers are not included in the script.

When the script is generated and saved, you can use SQL Server Management Studio to connect to your DB instance and then run the script.

Import and Export Wizard

The Import and Export Wizard creates a special Integration Services package, which you can use to copy data from your 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 might not be the fastest way to remotely export data from your local deployment. For an even faster way, consider the SQL Server bulk copy feature.

For detailed information about the Import and Export Wizard, see the Microsoft SQL Server documentation.

In the wizard, on the Choose a Destination page, do the following:

  • For Server Name, type the name of the endpoint for your DB instance.

  • For the server authentication mode, choose Use SQL Server Authentication.

  • For User name and Password, type the credentials for the master user that you created for the DB instance.

Bulk Copy

The SQL Server bulk copy feature is an efficient means of copying data from a source database to your DB instance. Bulk copy writes the data that you specify to a data file, such as an ASCII file. You 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. For detailed information about bulk import and export operations, see the Microsoft SQL Server documentation.

Note

Before you use bulk copy, you must first import your database schema to the destination DB instance. The Generate and Publish Scripts Wizard, described earlier in this topic, is an excellent tool for this purpose.

The following command connects to the local SQL Server instance to generate a tab-delimited file of a specified table in the C:\ root directory of your existing SQL Server deployment. The table is specified by its fully qualified name, and the text file has the same name as the table that is being copied.

Copy
bcp dbname.schema_name.table_name out C:\table_name.txt -n -S localhost -U username -P password -b 10000

The preceding code includes the following options:

  • -n specifies that the bulk copy will use the native data types of the data to be copied.

  • -S specifies the SQL Server instance that the bcp utility will connect to.

  • -U specifies the user name of the account that will log in to the SQL Server instance.

  • -P specifies the password for the user specified by -U.

  • -b specifies the number of rows per batch of imported data.

Note

There might be other parameters that are important to your import situation. For example, you might need the -E parameter that pertains to identity values. For more information; see the full description of the command line syntax for the bcp utility in the Microsoft SQL Server documentation.

For example, suppose a database named store that uses the default schema, dbo, contains a table named customers. The user account admin, with the password insecure, will copy 10,000 rows of the customers table to a file named customers.txt.

Copy
bcp store.dbo.customers out C:\customers.txt -n -S localhost -U admin -P insecure -b 10000

After you generate the data file, if you have created the database and schema on the target DB instance, you can upload the data to your DB instance by using a similar command. In this case, you 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, you will specify the endpoint of your DB instance. If you use a port other than 1433, you will specify that, too. The user name and password are the master user and password for your DB instance. The syntax is as follows.

Copy
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

To continue the previous example, suppose the master user name is admin, and the password is insecure. The endpoint for the DB instance is rds.ckz2kqd4qsn1.us-east-1.rds.amazonaws.com, and you use port 4080. The command is as follows.

Copy
bcp store.dbo.customers in C:\customers.txt -n -S rds.ckz2kqd4qsn1.us-east-1.rds.amazonaws.com,4080 -U admin -P insecure -b 10000

Exporting Data from SQL Server on Amazon RDS

You can choose one of the following options to export data from an Amazon RDS SQL DB instance :

SQL Server Import and Export Wizard

You can use the SQL Server Import and Export Wizard to copy one or more tables, views, or queries from your Amazon RDS SQL DB instance to another data store. This choice is best if the target data store is not SQL Server. For more information, see SQL Server Import and Export Wizard in the SQL Server documentation.

The SQL Server Import and Export Wizard is available as part of Microsoft SQL Server Management Studio, a graphical SQL Server client that is included in all Microsoft SQL Server editions except the Express Edition. SQL Server Management Studio is available only as a Windows-based application. SQL Server Management Studio Express is available from Microsoft as a free download. To find this download, see the Microsoft website.

To use the SQL Server Import and Export Wizard to export data

  1. In SQL Server Management Studio, connect to your Amazon RDS SQL DB instance. For details on how to do this, see Connecting to a DB Instance Running the Microsoft SQL Server Database Engine.

  2. In Object Explorer, expand Databases, open the context (right-click) menu for the source database, choose Tasks, and then choose Export Data. The wizard appears.

  3. On the Choose a Data Source page, do the following:

    1. For Data source, choose SQL Server Native Client 11.0.

    2. Verify that the Server name box shows the endpoint of your Amazon RDS SQL DB instance.

    3. Select Use SQL Server Authentication. For User name and Password, type the master user name and password of your Amazon RDS SQL DB.

    4. Verify that the Database box shows the database from which you want to export data.

    5. Choose Next.

  4. On the Choose a Destination page, do the following:

    1. For Destination, choose SQL Server Native Client 11.0.

      Note

      Other target data sources are available, include .NET Framework data providers, OLE DB providers, SQL Server Native Client providers, ADO.NET providers, Microsoft Office Excel, Microsoft Office Access, and the Flat File source. If you choose to target one of these data sources, skip the remainder of step 4 and see Choose a Destination in the SQL Server documentation for details on the connection information to provide.

    2. For Server name, type the server name of the target SQL Server DB instance.

    3. Choose the appropriate authentication type. Type a user name and password if necessary.

    4. For Database, choose the name of the target database, or choose New to create a new database to contain the exported data.

      If you choose New, see Create Database in the SQL Server documentation for details on the database information to provide.

    5. Choose Next.

  5. On the Table Copy or Query page, choose Copy data from one or more tables or views or Write a query to specify the data to transfer. Choose Next.

  6. If you chose Write a query to specify the data to transfer, you see the Provide a Source Query page. Type or paste in a SQL query, and then choose Parse to verify it. Once the query validates, choose Next.

  7. On the Select Source Tables and Views page, do the following:

    1. Select the tables and views that you want to export, or verify that the query you provided is selected.

    2. Choose Edit Mappings and specify database and column mapping information. For more information, see Column Mappings in the SQL Server documentation.

    3. (Optional) To see a preview of data to be exported, select the table, view, or query, and then choose Preview.

    4. Choose Next.

  8. On the Run Package page, verify that Run immediately is selected. Choose Next.

  9. On the Complete the Wizard page, verify that the data export details are as you expect. Choose Finish.

  10. On the The execution was successful page, choose Close.

SQL Server Generate and Publish Scripts Wizard and bcp Utility

You can use the SQL Server Generate and Publish Scripts Wizard to create scripts for an entire database or just selected objects. You can run these scripts on a target SQL Server DB instance to recreate the scripted objects. You can then use the bcp utility to bulk export the data for the selected objects to the target DB instance. This choice is best if you want to move a whole database (including objects other than tables) or large quantities of data between two SQL Server DB instances. For a full description of the bcp command line syntax, see bcp Utility in the Microsoft SQL Server documentation.

The SQL Server Generate and Publish Scripts Wizard is available as part of Microsoft SQL Server Management Studio, a graphical SQL Server client that is included in all Microsoft SQL Server editions except the Express Edition. SQL Server Management Studio is available only as a Windows-based application. SQL Server Management Studio Express is available from Microsoft as a free download.

To use the SQL Server Generate and Publish Scripts Wizard and the bcp utility to export data

  1. In SQL Server Management Studio, connect to your Amazon RDS SQL DB instance. For details on how to do this, see Connecting to a DB Instance Running the Microsoft SQL Server Database Engine.

  2. In Object Explorer, expand the Databases node and select the database you want to script.

  3. Follow the instructions in Generate and Publish Scripts Wizard in the SQL Server documentation to create a script file.

  4. In SQL Server Management Studio, connect to your target SQL Server DB instance.

  5. With the target SQL Server DB instance selected in Object Explorer, choose Open on the File menu, choose File, and then open the script file.

  6. If you have scripted the entire database, review the CREATE DATABASE statement in the script to make sure the database is being created in the location and with the parameters that you want. For more information, see CREATE DATABASE in the SQL Server documentation.

  7. If you are creating database users in the script, check to see if server logins exist on the target DB instance for those users. If not, create logins for those users; the scripted commands to create the database users will fail otherwise. For more information, see Create a Login in the SQL Server documentation.

  8. Choose !Execute on the SQL Editor menu to execute the script file and create the database objects. When the script finishes, verify that all database objects exist as expected.

  9. Use the bcp utility to export data from the Amazon RDS SQL DB instance into files. Open a command prompt and type the following command.

    Copy
    bcp database_name.schema_name.table_name out data_file -n -S aws_rds_sql_endpoint -U username -P password

    The preceding code includes the following options:

    • table_name is the name of one of the tables that you’ve recreated in the target database and now want to populate with data.

    • data_file is the full path and name of the data file to be created.

    • -n specifies that the bulk copy will use the native data types of the data to be copied.

    • -S specifies the SQL Server DB instance to export from.

    • -U specifies the user name to use when connecting to the SQL Server DB instance.

    • -P specifies the password for the user specified by -U.

    The following shows an example command.

    Copy
    bcp world.dbo.city out C:\Users\JohnDoe\city.dat -n -S sql-jdoe.1234abcd.us-west-2.rds.amazonaws.com,1433 -U JohnDoe -P ClearTextPassword

    Repeat this step until you have data files for all of the tables you want to export.

  10. Prepare your target DB instance for bulk import of data by following the instructions at Basic Guidelines for Bulk Importing Data in the SQL Server documentation.

  11. Decide on a bulk import method to use after considering performance and other concerns discussed in About Bulk Import and Bulk Export Operations in the SQL Server documentation.

  12. Bulk import the data from the data files you created using the bcp utility, following the instructions at either Import and Export Bulk Data by Using the bcp Utility or Import Bulk Data by Using BULK INSERT or OPENROWSET(BULK...) in the SQL Server documentation, depending on what you decided in step 11.

Related Topics