Search code examples
sql-server-2008migrationsql-server-2000

Migrate from sql server 2000 to 2008 r2 - how to

I have a database working on SQL Server 2000. We are now migrating to a new server with SQL Server 2008 r2. Can anyone please point me to some resource or howto? I'm not really finding my way around SQL 2000.

Thank you!

Solution

  • Basically, what you need to do is:

    • backup your database in SQL Server 2000 to a .bak file
    • move that *.bak file to your new server
    • restore that database onto your new server

    You're done! There's really nothing more to it..... just backup (on your old system) and restore (on your new system).

    So where exactly is your problem ??

    Update: as @Péter correctly mentions: this leaves your database in the SQL Server 2000 compatibility mode. This means: even though you've "migrated" to SQL Server 2008 R2, you can still only use the 2000 features.

    In order to see what compatibility mode your database is in, check the sys.databases catalog view:

    SELECT * FROM sys.databases WHERE name = 'YourDatabaseName'

    One column is called compatibility_level and contains an INT; 80 = SQL Server 2000, 90 = SQL Server 2005, 100 = SQL Server 2008 / 2008 R2 and 110 = SQL Server 2012

    In order to change your database to a different compatibility level, use this command:

    ALTER DATABASE YourDatabaseNameHere
SET COMPATIBILITY_LEVEL = 100;

    This will put your database into the "native" SQL Server 2008 (and 2008 R2) mode and now your migration is complete, you can use all the new SQL Server 2008 R2 features.