Importing and exporting SQL Server data using other methods

Create a DB instance. For more information, see Creating an Amazon RDS DB instance.

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 don'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 an earlier database snapshot, the only changes that you lose are the imported data. You can import this data again after you resolve the issue.

For information about controlling access to your DB instance, see Controlling access with security groups.

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 for a Single-AZ DB instance for Amazon RDS.

Disable automated backups on the target database.

Disabling automated backups on the target DB instance improves 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. Automated backups are required to perform a point-in-time recovery. Thus, you can't 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 unless you choose to retain them.

Choosing to retain the automated backups can help protect you against accidental deletion of data. Amazon RDS also saves the database instance properties along with each automated backup to make it easy to recover. Using this option lets you can restore a deleted database instance to a specified point in time within the backup retention period even after deleting it. Automated backups are automatically deleted at the end of the specified backup window, just as they are for an active database instance.

You can also use previous snapshots to recover the database, and any snapshots that you have taken remain available. For information about automated backups, see Introduction to backups.

Disable foreign key constraints, if applicable.

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

--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  

Drop indexes, if applicable.

Disable triggers, if applicable.

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

--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  

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. This file should include all logins and passwords that you want to add to the destination DB instance. This file also should include role memberships and permissions that you want to transfer.

For information about querying the master database, see Transfer logins and passwords between instances of SQL Server 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:

p.type IN 

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

p.type = 'S' 

Import the data using the method in Import the data.

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 Controlling access with security groups.

Enable automated backups on the target DB instance.

For information about automated backups, see Introduction to backups.

Enable foreign key constraints.

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

--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;  

Enable indexes, if applicable.

Enable triggers, if applicable.

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

--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;  

In SQL Server Management Studio, connect to your RDS for SQL Server DB instance. For details on how to do this, see Connecting to a DB instance running the Microsoft SQL Server database engine.

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.

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 RDS for SQL Server DB instance.

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

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

5. Choose Next.

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. These 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. For details on the connection information to provide next, see Choose a destination in the SQL Server documentation.

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.

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.

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.

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.

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

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

On the The execution was successful page, choose Close.

In SQL Server Management Studio, connect to your RDS for SQL Server DB instance. For details on how to do this, see Connecting to a DB instance running the Microsoft SQL Server database engine.

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

Follow the instructions in Generate and publish scripts Wizard in the SQL Server documentation to create a script file.

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

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.

If you have scripted the entire database, review the CREATE DATABASE statement in the script. Make sure that 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.

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 fail otherwise. For more information, see Create a login in the SQL Server documentation.

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

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

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:

The following shows an example command.

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.

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.

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.

Bulk import the data from the data files that you created using the bcp utility. To do so, follow 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.