Import/Export Services

Everything up to this point has revolved around schema. Schema is interesting for development scenarios and we have talked about portability of your schema between different servers but why can’t your data be portable too? Welcome to the new Import and Export services.

Have you ever needed to migrate a database to a new server? Assuming you can’t backup/restore to the new server, what do you typically need to do? Move your schema, BCP data to files, and then BCP it back into the database. If you’ve gone through this process, you know this is not a trivial task. Additionally, BCP.exe is based on the native ODBC drivers and therefore doesn’t have full fidelity support for some of the newer data types.

Reasons for importing and exporting could include:

• Migration
  
• Eliminating database fragmentation
  
• Creating a logical database backup
  
• Long-term archival into XML and JSON
  

The Import and Export services essentially automate the process of extracting the schema of a database and serializing out data in a logical format. The schema and data are stored in a single compressed file: a BACPAC.

Let’s talk export first. The Export service first performs a DAC extraction from the target database. Because the Extraction service is used, the same servers are supported for Extraction:

• SQL Server 2000
  
• SQL Server 2005
  
• SQL Server 2008
  
• SQL Server 2008R2
  
• SQL Server Code Name “Denali”
  
• SQL Azure
  

Note that if your database schema can’t be extracted using the DACFx Extraction service covered earlier then we can’t export your database. The schema that is extracted is then stored in the same XML files as a DACPAC. Once the schema extraction is complete, DACFx bulk loads the data from SQL Server (or SQL Azure) into the file. Table data is encoded in an open JSON format. Once the data load is complete, the file is closed and you have yourself a shiny new BACPAC.

Similar to the DACPAC, the BACPAC can be imported to a variety of supported servers:

• SQL Server 2005 SP4 and above
  
• SQL Server 2008 SP2 and above
  
• SQL Server 2008 R2
  
• SQL Server Code Name “Denali”
  
• SQL Azure
  

The Import service typically creates a new database. I say typically because you can use the import API directly and import into an existing but empty database. Empty in this case means that you don’t have objects defined in the database. You can however set up logins and users. Unsurprisingly, the import process leverages the Deploy service covered earlier to recreate the schema contained in the BACPAC in the target database. Once the schema is created, then the data is bulk loaded into the database.

Extra notes about import/export:

1. It’s slower than backup/restore because we are pulling the data out of the SQL engine to make it available in a logical format
   
2. The SQL variant data type is not yet supported, support will be added prior to final release
   
3. Passwords for logins are not carried through the import/export process
   
4. For SQL Azure customers, DACFx is subject to the same throttling restrictions and issues as any other client side SQL Azure traffic
   

You can get some additional information from the TechNet wiki located here.