Migration gone wrong – fall back scenario


Migration gone wrong – fall back scenario


Today I’ll be handling an issue I had when migrating a SQL Server 2000 database to SQL Server 2012. We migrated the database from the old, grumpy, and more importantly, unsupported Windows Server 2003 and SQL Server 2005 instance. All was well, and we had no instant issues when users started working with the application. The migration was seamless, we did not expect any future problems. Even the upgrade advisor did not give us any errors!

But then lightning struck: somebody who was on a holiday came back into work and started working with the application on the migrated database. Apparently this user was slightly different from the other users who had been testing, since he used the application to its full capacity. This unravelled a waterfall of syntax errors & DB errors. The impact was uncontrollable, so there was only one option: fall back to the old & abandoned SQL Server 2000 instance.

Now here the tricky part starts. As users had been utulizing the migrated database in production for several days, they had uploaded a lot of data which had to be present when we fell back to the SQL Server 2000 instance. However, as most of you know, once a database is on SQL Server 2012, even in lower compatibility mode, it will not restore ina version lower than the running version! It is also not possible to detach and attach the database, as there are changes which happened on system table level. Due to these restrictions there was only one option left: scripting out the database and exporting the data to the old SQL Server 2000 database. How can you do this? I’ll explain step-by-step how we execued thsis fall back scenario.


Step one: generate scripts


First, we generated the scripts off the database. We chose to script out the entire database and all database objects. Watch out for triggers, though, because these are not scripted out. You will have to manually add them later. Click next to create the scripts and copy them to clipboard/file/new query window, whatever has your preference. Then click next again, it will now script out your database.


Step two: execute scripts

Execute the script on the destination server, we used a SQL server 2005. You might get some errors for adding members and altering compatibility mode, depending on the SQL server version you are working with. You can alter the statements to statements that work on your version. Also you might want to set your database owner to sa at this point.


Step three: transfer data

Now that we have our database on our SQL Server 2005, we want to pump the data from the new old database. Go to the database you want to export the data from and click export data.


Enter the information of your source database and insert the information of the destination database.Then choose to copy data from table. In the next window, select all your tables, and check table by table by clicking Edit Mappings…



Don’t forget to tick off Enable identity insert on the tables! Also, if you have foreign keys, don’t forget to disable them when loading your data between the instances. You can easily do that with the following command:

-- Disable all the constraint in database

After the export, also remember to re-enable your foreign keys:

-- Enable all the constraint in database

If you followed the steps above, you should be able to export your data to your SQL server 2000 database. This way we restored a SQL Server 2012 database on a SQL Server 2005 instance, so we consider the fall back as successful! All data was present and the business was happy. Now we can start testing the application again for compatibility with the SQL Server 2012 version, which will prove to be a long and though process! The application is working again to its full extent, though. Unfortunately it is on a SQL Server 2005 instance with a compatibility mode of SQL Server 2000 with a Windows Server 2003. (unsupportedception)

Hope you enjoyed reading this blog and stay tuned!

Group of computer programmers working in the office. Focus is on blond woman showing something to her colleague on PC.
Updating your Azure SQL server OAuth2 credentials in Power BI via PowerShell for automation purposes
The better way to update OAuth2 credentials in Power BI is by automating the process of updating Azure SQL Server...
Under (memory) pressure
A few weeks ago, a client asked me if they were experiencing memory pressure and how they could monitor it...
Managing files from other devices in a Fabric Lakehouse using the Python Azure SDK
In this blogpost, you’ll see how to manage files in OneLake programmatically using the Python Azure SDK. Very little coding...
Database specific security in SQL Server
There are many different ways to secure your database. In this blog post we will give most of them a...
SQL Server security made easy on the server level
In this blog, we’re going to look at the options we have for server level security. In SQL Server we...
Microsoft SQL Server history
Since its inception in 1989, Microsoft SQL Server is a critical component of many organizations' data infrastructure. As data has...