Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
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.
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:
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.
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.
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.
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.
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.
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.
