5

How do I restore a SQL Server 2000 database to a SQL Server 2008 instance?

1
  • if you need to restore from a SQL Server 2000 database to a SQL Server 2012 (or higher), you must first restore to a SQL Server 2008. Then it is often necessary to reindex the database. Then back it up, and restore the backup to a SQL Server 2012. Aug 11, 2016 at 13:45

5 Answers 5

3
  • right click on the database
  • tasks
  • restore
  • select file and click ok
4
4
  • thankz 4 the reply that i tried earlier but it is showing error
    – Nighil
    Dec 15, 2010 at 8:26
  • 1
    @user543047: what error is showing?
    – Parkyprg
    Dec 15, 2010 at 8:30
  • System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'Banking' database. (Microsoft.SqlServer.Smo)
    – Nighil
    Dec 15, 2010 at 8:37
  • The way it was resolved for us was to 1) Selecting Options within Restore Database dialog box. 2) Specifying correct path to the mdf and log file for the database. 3) Checking Overwrite the existing database option. That’ s it ..
    – Nighil
    Dec 15, 2010 at 9:38
2

Upward compatibility is supported - you can easily restore a SQL Server 2000 database on SQL Server 2008, but backwards compatibility isn't - you cannot restore a SQL Server 2008 backup on SQL Server 2000.

1
  • In this this case yes - there is upward compatibility. But you won't be able to e.g. restore SQL 7 backup on SQL 2008, nor you will be able to restore SQL 2000 bak on SQL 2012.
    – Nux
    Jul 27, 2016 at 10:32
0

In SQL Server 2008, follow these steps

  1. Create New database with the same name as in SQL Server 2000
  2. After writing database name, go to Options tab
  3. In Compatibility Level dropdown select 'SQL Server 2000 (80)
  4. After creating database, go to Task and select restore
  5. After selecting restore resource go to options tab and select 'Overwrite the existing database (With Replace)
  6. Your database will be restored
0

You can restore the database directly on 2008 as that upgrade path is supported, if you were going high i.e. to 2012+ then you'd need to restore to 2008 backup and then restore to 2012. Just ensure once you've restore the database on 2008 you alter the compat level so you're able to use new t-sql features on the database.

Just use the RESTORE WITH MOVE to locate the files onto the drive structure of your new server.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.