I have a production environment running Windows Server 2000 and a development environment using SQL Server 2012. I need to make updates to the DB and implement some new features using .ASP. Is there a way to export the 2000 database and import it into 2012? All that I could find is this MS support doc about transferring the DB to 2008.
Sql-server – Export a SQL Server 2000 database to Server 2012
exportsql serversql-server-2012
Related Question
- Sql-server – Support to SQL Server 2000 from Visual Studio 2013(SQL Server 2012)
- Sql-server – Copy SQL Logins and Permissions From One Server to Another
- Sql-server – migrating legacy databases from SQL Server 2000 via SQL Server 2008 to SQL Server 2012: users and logins
- Sql-server – Migrating SQL Server 2000 to 2012, getting old versions of Views (edit: offender SQL Server 2008 R2)
- Sql-server – Does SQL Server 2012/2014 Standard support Hot Add memory on a Hyper-V guest
- Sql-server – SQL Server 2012 Support on Windows Server 2008 R2
Best Answer
Since your source server is SQL Server 2000, you can't restore directly to SQL Server 2012. You will need to restore to another instance first (2005, 2008, or 2008 R2).
On 2000:
On 2005 or 2008 or 2008 R2:
Then from there, back it up again, and then restore on the 2012 server.
Once you've restored, you'll probably want to adjust the compatibility level:
As well as update statistics. But note that 80 compatibility will automatically be upgraded to 90 (and you will have issues if you use syntax that is no longer supported, e.g.
*=
joins). While it won't catch everything, you may want to run the 2012 Upgrade Advisor while the database is in its temporary home on a 2005, 2008 or 2008 R2 instance to catch any potential issues. Also see this answer for ways the change in compatibility level may affect your existing code (the upgrade advisor won't catch many of those).