locked
Prepare database to move from SQL 2000 to SQL 2008 R2 RRS feed

  • Question

  • I'm preparing a database to move from a SQL 2000 server to a SQL 2008 R2 server. Some time ago I created a VS Project on a test SQL 2008 server and copied the database to the test server. I forget the exact steps I took, but somehow in the process creating or comparing database schema, I ended up with a very helpful list of errors. The list included deprecated commands and several syntax errors (e.g. ambiguous column names, poor syntax, etc.) in the database. I planned to use the error list to make corrections to the database before copying it to the production SQL 2008 server.

    Then I lost the test server (it was "repurposed" by the network admin) and my project. Now I can't remember exactly what I did to create the project, schema, and error list. Can anyone suggest what steps I should take to analyze a SQL 2000 database to identify both critical and non-critical errors that should be corrected before copying the database to a SQL 2008 server?

    Friday, December 24, 2010 4:40 AM

Answers

  • Hello Mike,

    Thanks for your post.

    If you want to create a Database Project and copied the database to the test server, you can try the steps below.

    1)       Create a new database project.

    You first crate a new database project which references the database in the SQL2000. Here is an article which describes the detailed steps to create a database project.

    Note: On the Import Database Schema page, you should configure your connection to the SQL2000.

    2)       Deploy the database project.

    By deploying the database project to the SQL2008 you can get a copy of that database on that machine.

    For more information about how to deploy a database project, please see the following article:

    http://msdn.microsoft.com/en-us/library/aa833271.aspx

    Note: When you deploy your database project, you should set the value of Deploy action to Create a deployment script (.sql) and deploy to the database. And modify the Target connection to connect to the SQL2008.

    If you still have anything unclear, please feel free to contact me.

    Thanks,


    Vicky Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Mike Storms Sunday, December 26, 2010 5:06 PM
    Friday, December 24, 2010 6:45 AM
    Moderator
  • VS2010 does no longer support importing a database from SQL Server 2000.

    Two possibilities:

    1. attach the SQL Server 2000 database to a SQL Server 2008 R2 instance and import the schema from there
    2. script the SQL Server 2000 database using the management tools (SSMS, or some other means) to a .SQL script file and import the script file


    GertD @ www.DBProj.com
    • Marked as answer by Mike Storms Monday, December 27, 2010 8:07 PM
    Monday, December 27, 2010 8:04 PM

All replies

  • Hello Mike,

    Thanks for your post.

    If you want to create a Database Project and copied the database to the test server, you can try the steps below.

    1)       Create a new database project.

    You first crate a new database project which references the database in the SQL2000. Here is an article which describes the detailed steps to create a database project.

    Note: On the Import Database Schema page, you should configure your connection to the SQL2000.

    2)       Deploy the database project.

    By deploying the database project to the SQL2008 you can get a copy of that database on that machine.

    For more information about how to deploy a database project, please see the following article:

    http://msdn.microsoft.com/en-us/library/aa833271.aspx

    Note: When you deploy your database project, you should set the value of Deploy action to Create a deployment script (.sql) and deploy to the database. And modify the Target connection to connect to the SQL2008.

    If you still have anything unclear, please feel free to contact me.

    Thanks,


    Vicky Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    • Marked as answer by Mike Storms Sunday, December 26, 2010 5:06 PM
    Friday, December 24, 2010 6:45 AM
    Moderator
  • Thanks, Vicky. This is very helpful. However, you didn't include the first link to the artilce on creating a database project. I did follow your link on how to deploy a database project and that's helpful.

    Mike

    Friday, December 24, 2010 5:10 PM
  • Vicky,

    I figured out how to create a project, but when I go to the Import Database Schema page and try to configure a connection to SQL2000 I get an error message "This server version is not supported. You must have Microsoft SQL Server 2005 or later."

    Mike

    Friday, December 24, 2010 5:30 PM
  • Thanks, Vicky. You set me in the right direction. I found the articles I needed and figured out how to do everything else. --Mike
    Sunday, December 26, 2010 5:07 PM
  • VS2010 does no longer support importing a database from SQL Server 2000.

    Two possibilities:

    1. attach the SQL Server 2000 database to a SQL Server 2008 R2 instance and import the schema from there
    2. script the SQL Server 2000 database using the management tools (SSMS, or some other means) to a .SQL script file and import the script file


    GertD @ www.DBProj.com
    • Marked as answer by Mike Storms Monday, December 27, 2010 8:07 PM
    Monday, December 27, 2010 8:04 PM
  • Thanks. I already tried the first way and it worked fine.
    Monday, December 27, 2010 8:07 PM