11

I have come across three old databases sitting on SQL Server 2000 which I need to move to 2012. I believe the standard approach is to restore into a 2005 or 2008 instance, update, re-export and finally restore into 2012.

Fine, except we have no 2005 or 2008 instances available.

Are there any workarounds or other methods that might be worth trying?

For information, the databases only contain 15-20 tables and a few views, they appear very simple and the backups are only 100-200MB in size.

21

No, there is no workaround to upgrading a database directly from 2000 to 2012.

Since you don't have a ton of data, you can do all kinds of things to move the data (but not the database as a whole), including:

  • Import/Export wizard
  • SSIS
  • BCP
  • Manual queries using a linked server from 2012 or an application

However these will not necessarily bring over other things like users, permissions, roles, etc. And not everything will create your objects in the right dependency order. So they are more work and error-prone. In my experience it would be worthwhile to just put up an intermediate instance temporarily and then do two backup+restore operations - which will be easier, faster, and less error-prone than any of the above methods.

If your databases are smaller than 10GB, you can install a copy of Express (free) in a few minutes, and use that. You can download 2008 R2 Express here. I don't think there are any Enterprise features in 2000 around database structure etc. that would block that upgrade (a bigger concern for more modern versions).

If your database is too large for Express, you should be able to find Developer Edition for $49 or so (or even cheaper - I found 2005 on eBay for $37), or if you have an MSDN subscription, you should be able to get any SKU from there.

Or you can get away with using an Evaluation Edition, which is amazingly still being offered for download for older versions (e.g. I found 2008 here), however I'm on a phone so was not able to confirm the download for that version still works.

  • Thanks. As internet speed here is poor I was trying to avoid downloads, but I found the 2005 install binaries so I guess that'll be the quickest method. – Lunatik May 1 '15 at 11:43
  • nope, the only really useful one is 2008 otherwise you'll also need 2010 from 2005 – Rostol Jan 13 '17 at 1:31
  • Are you sure @Rostol ? sqlskills.com/blogs/paul/… – Adam Nofsinger Feb 14 '18 at 16:36
  • @AdamNofsinger i was going to say 100% sure, i did this a gajillion times (we have an old SQL 2000 used by an old system) and have a purpose built 2008 vm to restore the data first and then backup and restore to SQL 2016... But if Paul Randal says otherwise ... but bear in mind that the direct method works from 2005+. not for 2000 and 2003 straight. – Rostol Feb 20 '18 at 21:52
6

Stating there is no workaround seems a little premature.

You say your database only contains a few tables and views. Why not export the tables and views schema as SQL create statements and run them on your target database? You could then also export the actual data into something like CSV format and import them into the target database, or perhaps even write something in C#/C++/etc to export/import the actual data.

You wouldn't need to spend a penny on new instances of SQL Server you don't ultimately need.

  • 3
    And include all other objects, stored procedures, triggers, functions, etc. of course. Likewise there is the BCP utility for copying data from and to a database. – RLF May 1 '15 at 12:42
  • 1
    Thanks. That's the kind of thing I was maybe hoping for, but DB wrangling isn't my day job and I'm not too sure if there are any other dependencies or settings (user permissions etc.) that wouldn't get copied across if I just exported the tables etc. – Lunatik May 1 '15 at 12:42
  • 4
    Fair enough, updated my answer. Still think a workaround is a lot more work than just putting up an instance temporarily, and doing two reliable and not-error-prone backup+restore operations. (Also, this doesn't seem to be about money, since the OP already has 2005 binaries, and a database that small should be able to be migrated using SQL Express anyway.) – Aaron Bertrand May 1 '15 at 13:14
3

No, you cannot migrate it directly from 2000 to 2012.

What you can do is install a temporary instance of 2005 OR 2008 oR 2008R2, restore the 2000 database here. Once done you can now upgrade from installed instance by taking the backup of database from that instance and restoring onto sql server 2012.

You will have to make the migration in two steps:

  • Step 1: Make a first migration from SQL 2000 to SQL 2008 for instance. You need to be SQL 2000 SP4, then follow this step : Migration SQL Server 2000 to SQL Server 2008

  • Step 2: Make a second migration from SQL Server 2008 to 2012.

0

You say you "have come across three old databases sitting on SQL Server 2000" which leads me to believe you only need the data. I would guess that if they were being run for a purpose, you would know. If this is the case, ignore all questions of users, security, etc.

Import/Export wizard all the tables Script the stored procedures and views Check for maintenance plans and agent jobs simply to document processes you may want to look at later.

0

You can not migrate directly from SQL Server 2000 to SQL Server 2012, and if you try to do it you will get the following error number 3169, that is related to compatibility issues.

The migration to SQL Server 2012 can be done from one of the following versions (minimum versions) SQL Server 2005 SP4 or SQL Server 2008 SP2 or SQL Server 2008 R2 SP1.

So the first step to migrate from SQL Server 2000 to one of those version, I found one of the Upgrade Advisor for SQL Server 2008 in the following link. To make sure everything will work smoothly before starting the restoration process, you need to run the application. Then "Launch Upgrade Advisor Analysis Wizard" and follow the wizard steps.

After you got the report, you need to restore the database on the target SQL Server 2005 SP4 or SQL Server 2008 SP2 or SQL Server 2008 R2 SP1 and run compatibility level on it (you can find it on the database option). For SQL Server logins please follow the instruction in the following link.

When that is complete, now you need to migrate to SQL Server 2012 by repeating the same steps from the server to which you did the first migration.

0

First I agree you should not migrate from 2000 to 2012 directly. I think that process has been covered well. I wanted to suggest you find out for sure if anyone is using the databases and what they are using them for. It may be that they are not being used. It happens and if that is the case then you have no worries. It might be that adding 2 or 3 tables and the data (for 2000 use bcp if you want to get the data out for an individual table its simple and reliable) to an existing 2012 database would serve the needs of the users. If you do that then create the new tables in a 2012 database and bcp out the data you need to csv you can bcp it back in if you want. If you have to migrate more than a table or two then the simplest and safest and best way is to do the two step migration. Don't forget to take a backup in the 2000 version before you do anything.

  • You should know that the schema scripting in 2000 was not 100% accurate. I remember always having to edit the script. – dkh63493 May 3 '15 at 4:56

protected by Paul White May 3 '15 at 9:50

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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