• Home
  • About
  • Blog
  • Speaking
  • Contact

Migration Gone Wrong! The Fallback Scenario

23 February 2016SQL StijnSQL Server MigrationNo Comments

Today I’ll be handling an issue I had with the migration of a SQL Server 2000 database to SQL Server 2012. About a week ago we migrated the database from the old and 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 and we did not expect any future problems, also the upgrade advisor did not give us any errors!

But then lightning struck this morning, somebody who was on a holiday came back into work and started working with the application on the migrated database. But apparently this user was a bit different than the other users who tested. This guy used the application to its full capacity, unravelling a waterfall of syntax errors & DB errors. The impact was uncontrollable so there was only one option, we had to fall back to the old & abandoned SQL Server 2000 instance.

Now here starts the tricky part, as users had been using 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. But as you all know once a database is on SQL Server 2012, even in lower compatibility mode, it will not restore on a 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 is only one option left, scripting out the database and exporting the data to the old SQL Server 2000 database.

I will now continue on explaining how I executed the fall back scenario.

First we will generate the scripts off the database

We choose to script out the entire database & all database objects! Watch out for triggers, 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

 

And click next, it will now script out your database.

 

Execute the script on the destination server, here it was a SQL server 2005, you might get some errors for adding members & 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.

Now that we have our database on our SQL Server 2005 we want to pump the data into it from the new old database J

Go to the database you want to export the data from and click export data.

Enter the information of your source database

Afterwards insert the information of the destination database

 

Then choose to copy data from table

In the next window select all your tables, and then check table by table by clicking edit mappings

 

Don’t forget to put on 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 this command.

— Disable all the constraint in database

EXEC sp_msforeachtable ‘ALTER TABLE ? NOCHECK CONSTRAINT all’

When you have done all the steps above you should be able to export your data to your SQL server 2000 database.

 

After you did the export don’t forget to re-enable your foreign keys

— Enable all the constraint in database

EXEC sp_msforeachtable ‘ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all’

 

In this way we just kind of restored a SQL Server 2012 database on a SQL Server 2005 instance! So the fall back was 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!

But the application is working again to its full extent, unfortunately it is on a SQL Server 2005 instance with a compatibility mode of SQL Server 2000 with a Windows Server 2003. (unsupportedception J)

 

Hope you enjoyed reading this blog and stay tuned!

Tags: CONSOLIDATION, DOWNGRADE VERSION, FALLBACK, MICROSOFT, MIGRATION, ROLLBACK, SQL SERVER, SQL SERVER 2000, SQL SERVER 2005, SQL Server 2012, SQL SERVER CONSOLIDATION

Related Articles

How to achieve Ultimate PDW load speed (DWLOADER PART 2)

24 January 2014SQL Stijn

THE SQL SERVER MISCONFIGURATION CHRONICLES: PART 2 Instance Memory Settings

17 December 2015SQL Stijn

Clustered Columnstore Index TupleMove Magic

6 November 2014SQL Stijn

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

Recent Posts

  • Building a Modern Lakehouse Data Warehouse with Azure Synapse Analytics: Moving your Database to the lake
  • Azure Synapse Dedicated SQL Pools: Workload Management
  • Synapse Spark – Playing with Data Frames with Python
  • Synapse Spark – Reading CSV files from Azure Data Lake Storage Gen 2 with Synapse Spark using Python
  • Azure Synapse Analytics – Serverless SQL Pools: Data Elimination

Recent Comments

    Archives

    • June 2022
    • December 2021
    • May 2021
    • April 2021
    • March 2021
    • February 2021
    • January 2021
    • December 2020
    • August 2016
    • July 2016
    • March 2016
    • February 2016
    • December 2015
    • October 2015
    • August 2015
    • March 2015
    • December 2014
    • November 2014
    • July 2014
    • February 2014
    • January 2014
    • December 2013

    Categories

    • Azure Synapse Analytics
    • Building a Modern Lakehouse Data Warehouse
    • Geen categorie
    • MS PDW
    • SQL 2016
    • SQL Maintenance
    • SQL Server Migration
    • SQL Server Misconfiguration Chronicles
    • SQL2014
    • Troubleshooting

    Contact Stijn now


    © SQL Stijn 2017 - Privacy Policy - Cookie Policy
    Manage Cookie Consent
    To provide the best experiences, we use technologies like cookies to store and/or access device information.
    Functional Always active
    The technical storage or access is strictly necessary for the legitimate purpose of enabling the use of a specific service explicitly requested by the subscriber or user, or for the sole purpose of carrying out the transmission of a communication over an electronic communications network.
    Preferences
    The technical storage or access is necessary for the legitimate purpose of storing preferences that are not requested by the subscriber or user.
    Statistics
    The technical storage or access that is used exclusively for statistical purposes. The technical storage or access that is used exclusively for anonymous statistical purposes. Without a subpoena, voluntary compliance on the part of your Internet Service Provider, or additional records from a third party, information stored or retrieved for this purpose alone cannot usually be used to identify you.
    Marketing
    The technical storage or access is required to create user profiles to send advertising, or to track the user on a website or across several websites for similar marketing purposes.
    Manage options Manage services Manage vendors Read more about these purposes
    View preferences
    {title} {title} {title}