Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

Export data to an earlier SQL Server version

MSSQLTips author K. Brian Kelley By:   |   Read Comments (2)   |   Related Tips: More > Integration Services Import and Export Wizard
Problem

I have data in a SQL Server database that I need to get to an older version of SQL Server. I tried the backup and restore method, but received an error indicating that this wasn't allowed. I also tried to detach and attach the database, but that operation failed too. I understand that typical methods I use to move the database around don't work when I have to work with an earlier SQL Server version. What can I do to get the data out? This is a simple database and I want to spend a minimal amount of effort.  Check out this tip to learn more.

Solution

If you're dealing with a relatively simple database, then the easiest way to do this is with the SQL Server Import and Export Wizard. This wizard basically creates a small, simple SSIS package to move tables of data from one database to another. In order to access it, open SQL Server Management Studio then navigate to the database you want to export, right-click on it, choose 'Tasks', and then choose to 'Export Data':

SQL Server Management Studio Export Data with the Import Export Wizard

This starts the wizard. The first thing the wizard is going to do is prompt you for the source, which will default to the database currently selected in SSMS. In the screenshot below I'm moving data from a SQL Server 2008 data source, hence the use of the Native Client 10.0.

Choose the data source in the Import Export Wizard

Then you'll need to select the destination.

Choose the data destination in the Import Export Wizard

Since I'm exporting to a SQL Server 2005 server, I use the SQL Server Native Client, because I happen to have it installed on this system. I've highlighted the 'New' button. If the database doesn't exist at the destination, you can use this button to go ahead and create it before continuing. It'll bring up a dialog window to perform the creation.

Create Database window in the Import and Export Wizard

You will need to have appropriate permissions to create the database. With the source and destinations selected, the next step is to choose how to move the data.

Specify Table Copy or Query in the SQL Server Import Export Wizard

If you choose the first option, you're presented with a GUI interface where you're allowed to mark the tables and view you want to copy. This is self-explanatory and you can change the mappings as you need to. The next interface that requires some thought is what to do with the package that's being generated:

Save and Run Package screen in the SQL Server Import and Export Wizard

If you need to do this data export more than once, choose to save the SSIS package. You can edit it later as needed. Then allow it to run. Sometimes you'll see a warning or an error.

Warning on the SSIS package run

You'll want to investigate why these were reported. Click the 'Report' button and select 'View Report' to determine what the issue(s) was during the data export.

Choosing to view the error report in SSIS Import and Export Wizard

In this case, here is the cause of the warning:

SSIS Warning Generated for the Data Flow Task

In my case, I just cared about the one-time data migration, so the fact that I couldn't get performance counters isn't important. 

Re-Using the SSIS Package

Before you assume that you can re-use the package as is, be sure to review it in the Business Intelligence Development Studio (the right BIDS version to correspond with the SQL Server that created the package) and examine it first. For instance, the simple package I built using the wizard has a step which does the following:

Create Table in the SSIS package

If I'm simply going to truncate the tables each time before I run the package, then I don't want the step (or steps, as there are actually 2 in the package I created, to check everything) with the CREATE TABLE statements. Therefore, examine the package and make the appropriate updates to be able to re-use it as you need to.

Next Steps


Last Update: 4/25/2012


About the author
MSSQLTips author K. Brian Kelley
K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Friday, May 11, 2012 - 2:16:02 AM - Ton Grommen Read The Tip

Hello,

I remember that I noticed in tha past that constraints, keys, etcetera were not copied tohether with the tables with the SQL Server 2008 R2 "Import and Export Data" tool. Is that correct and still the case with the latest SQL Server?


Monday, July 02, 2012 - 9:40:37 PM - Dinesh Vishe Read The Tip

Great It is very Useful...But it want make This SSIs Backage Schedle the what to do???



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.