4

I need to restore a SQL Server 2000 database to SQL Server 2016. Is there any possible way to do this without having to install a complete copy of SQL Server 2008, restore to 2008, change the compatibility level and then restore the 2008 backup to 2016? I know this would work but I really don't want to install 2008 R2 just for this single purpose unless I have no other choice.

7

It is NOT possible to restore a SQL Server 2000 database to SQL Server 2016 without restoring it to 2008 R2 first.

Here is an answer by Aaron Bertrand with details on alternate options to get the data out of the SQL 2000 database https://dba.stackexchange.com/a/99340/60898.

6

Adding more info and complementing @sql_handle's answer ...

You can upgrade from any version 2005+ to any other version.

Since you are on SQL 2000, you have to have a jump server to facilitate the upgrade to sql server 2016.

Also, entire migration can be automated using dbatools - PowerShell script - start-sqlmigration.

I have answered Upgrade SQL Server 2000 database.. with steps and best practices which will be useful to you.

3

It's not possible to restore your data from SQL 2000 to SQL 2016 directly but you can import it from SQL 2000 to SQL 2016.

Here is how I import the data, just follow the steps below.

Step 1. Right click in any table then choose Import data

enter image description here

Step 2. Click Next after DTS Import/Export Wizard appeared

enter image description here

Step 3. Choose your Data Source (SQL 2000)

enter image description here

Step 4. Choose your destination (SQL 2016), You need to create DSN connection (ODBC connection) to connect to SQL 2016

enter image description here

Step 5. Select all tables

enter image description here

Step 6. Choose copy tables and views from the source database

enter image description here

Step 7. Select Run immediately

Step 8. Click Finish

Step 9. Process import data to SQL 2016, this process will take a minute, it depends on how many data do you have. Just relax and wait.

enter image description here

Step 10. Finish

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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