Export data to an earlier SQL Server version

By:   |   Comments (3)   |   Related: 1 | 2 | 3 | 4 | More > Import and Export


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.


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

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, February 13, 2019 - 5:14:28 PM - Francesco Mantovani Back To Top (79031)

I tried to use it and export a database fom SQL Server 2017 to SQL Server 2012 and it didn't work. 

Is this out of scope? 

Thank you

Monday, July 2, 2012 - 9:40:37 PM - Dinesh Vishe Back To Top (18312)

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

Friday, May 11, 2012 - 2:16:02 AM - Ton Grommen Back To Top (17412)


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?

get free sql tips
agree to terms