Home
Restore Database From SQL Server 2008 to SQL Server 2005, Part 3: Export Data Wizard

Restore Database From SQL Server 2008 to SQL Server 2005, Part 3: Export Data Wizard

August 17, 2009 Administration 8 Comments
This entry is part 3 of 3 in the series Restore Database From SQL Server 2008 to SQL Server 2005

Restore Database From SQL Server 2008 to SQL Server 2005

Export Data Wizard

On Part 1: Introduction, I mentioned about error messages when you try to restore a database from SQL Server 2008 to SQL Server 2005. You can see the first solution at Part 2: Generate SQL Server Scripts Wizard. Now let’s see the second solution to solve the problems.

On this post, you see how to export tables on ‘Northwind’ database from SQL Server 2008 to SQL Server 2005 using export data wizard.

Step-by-step

  1. On Microsoft SQL Server Management Studio, connects to SQL Server 2008. Right-click on the database that you want to export data -> select Tasks -> Export Data.
    Export Data
  2. On Welcome to SQL Server Import and Export Wizard, click Next.
    Welcome to SQL Server Import and Export Wizard
  3. On Choose a Data Source, select the source from which to copy data. Set Data source to SQL Server Native Client 10.0. Verify that Server name is the source of SQL Server 2008 that you want and select Database as ‘Northwind’. Click Next.
    Choose a Data Source
  4. On Choose a Destination, specify where to copy data to. Set Destination to SQL Server Native Client 10.0. Type the Server name to the destination of SQL Server 2005 that you want. You can also click Refresh to verify if you can connect to the specify server name. Currently, I don’t have ‘Northwind’ database on SQL Server 2005 so I will create a new one, click New.
    Choose a Destination
  5. On Create Database, type name as ‘Northwind’ and click OK.
    Create Database
  6. Back to Choose a Destination, I have created ‘Northwind’ database so select it as Database. Click Next.
    Choose a Destination
  7. On Specify Table Copy or Query, select Copy data from one or more tables or views and click Next.
    Copy data from one or more tables or views
  8. On Select Sources Tables and Views, select tables that you want to export. On this example, I select all tables on ‘Northwind’ database.
    Select Sources Tables and Views
  9. On Save and Run Package, click Next.
    Save and Run Package
  10. On Complete the Wizard, you can verify the choices made in the wizard. Then, click Finish.
    Complete the Wizard
  11. Wait until the wizard finishes execution.
    Export Data Successful
  12. Now I have exported tables of ‘Northwind’ database from SQL Server 2008 to SQL Server 2005 successfully.
    The Exported Tables
Series Navigation<< Restore Database From SQL Server 2008 to SQL Server 2005, Part 2: Generate SQL Server Scripts Wizard

Related Posts

8 Comments

  1. Mark April 22, 2010

    I followed this step but i keep getting this error message:

    the data type could not be assigned to the column “dDate” in “SQL Server Native Client 10.0”.

    can someone help me about this error?

    thanks

  2. petercory April 26, 2010

    Can I use this way or previous way(part 2) in c# code or command line??

  3. petercory April 26, 2010

    Is there any way to set backward compatible for a database in sql 2008.
    and what is ‘Microsoft SQL Server 2005 Backward Compatibility Components’ in ‘http://www.microsoft.com/downloads/details.aspx?FamilyId=C6C3E9EF-BA29-4A43-8D69-A2BED18FE73C&displaylang=en’?

  4. linglom April 29, 2010

    Hi, Mark
    The error tells that the data type of the “dDate” column is not supported on SQL Server 2005. You have to change data type to one that available in SQL Server 2005.

    Hi, Petercory
    I believe that you can programming to run these wizards. See SQL Server 2008 Books Online -> Scripting.

    To set compatibility of SQL Server, see SQL Server 2008 Books Online -> ALTER DATABASE Compatibility Level . About SQL Server 2005 Backward Compatibility Components, it already told you what packages are in it on the download page.

  5. sumit May 4, 2010

    kutte

  6. James June 23, 2010

    In SQL 2008 You can set the data type “Date” but it doesn’t exists in SQL 2005, you need to change it to “DateTime”

    Greets

  7. shox December 24, 2010

    I am trying to export database from sql server 2005 std to sql server 2008 r2 it is successfull but i am getting an error

    The package contains two objects with the duplicate name of XXXXXXX error 226………………

    and a warning

    but i see all the tables in the exported database.

    also when i try to mirror it says i can mirror only from 2005 std to sql server 2008 std.

    any help will be appreciated

    thank you

  8. Akbal September 25, 2011

    Gracias por el tutorial , me ayudo a resolver el problema!!

Leave a Reply