By: K. Brian Kelley | Comments (3) | Related: 1 | 2 | 3 | 4 | More > Import and Export
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':
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.
Then you'll need to select the destination.
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.
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.
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:
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.
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.
In this case, here is the cause of the warning:
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:
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
- Learn why you can't restore a database to an older version of SQL Server.
- Get started in SSIS to build your own data movement packages.
- Read up on SSIS best practices.
- Learn the different ways to execute an SSIS package.
- Consider other options for moving data into and out of SQL Server.
About the author
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
