SQL Services News



Restoring/Downgrading SQL Server Database to Older Version

08 Sep 2016

Sometimes we may need to restore a higher version of SQL Server database to lower version which was considered impossible before. Now we have different options to perform the downgrade.

These options include:

  • Generate Scripts wizard of SQL Server Management Studio
  • SQL Server Integration Services
  • Custom scripting and BCP
  • Using SQLAzureMW.exe from codeplex

Among the options available, I found using SQLAzureMW.exe is extremely simple and fast.

This method is tested for downgrading SQL 2014 to SQL 2012 and SQL 2012 database to SQL 2008 R2. This may work for lower versions as well.

The following steps will restore SQL Server 2012 database to SQL 2008 R2.

  • Download the right version of tool from http://sqlazuremw.codeplex.com/
  • Run SQLAzureMW.exe
  • Select the Analyse/Migrate radio button from the right hand side of the main window
  • Select the Target Server as “SQL Database latest service version (V12)”. Click [Next].
  • Connect to the SQL Server 2012 instance. Use Windows authentication, select “Master DB (list all databases)” from the database options and “Save Login Information”. Click [Connect].
  • Select the required database to migrate .Click [Next].
  • Select “Script all database objects”.
  • Click [Advance] and change the following options:
    • Under General set “Target Server” to “SQL Server”.
    • Under “Table/View Options” set “Script Table / Data” to “Table Schema with Data”. Set “Database Engine Stored Procedures” to “True”. Set “Security Functions”, “Security Stored Procedures” and “System Functions” to “True”
    • Click [OK]. Click [Next].
  • Review your selections. Click [Next].
  • You will be prompted “Ready to Generate Script?” click [Yes]. This will start the script generation. Once this is done, click [Next].
  • Now you will get another connection dialog. This time select the database on the target server (the SQL Server 2008 R2 instance). Select Master database so you get a choice of target DB. Click [Connect].
  • Now, it is likely that you want to migrate into a new database, so click [Create Database].
  • Enter a database target name and leave the “Collation” as the “”, this does not concern us. Click [Create Database]. Click [Next].
  • We will now be prompted “Execute script against destination server?”, click [Yes].
  • This will now go off and do loads of stuff, setting up the schema using the generated script, but unlike the previous method we found, the data is bulk loaded using BCP, which is blazingly fast. All of this is also done internally, so no generation of massive .sql script files etc.
  • Click [Exit].

We are done. Now if you open up Management Studio and connect to both the SQL Server 2012 and 2008 R2 instances we have just worked with you can see that the schema for the 2012 source database matches the target database which was just created.

This Hot Tip was provided by Kokila, one of our Auckland based SQL Server consultants. If you require any assistance, please contact SQL Services Limited.

 
Click here to return to the news items...

Microsoft Gold Data, Silver BI partner © Copyright SQL Services Limited 2019. For information on how SQL Services processes personal data please refer to our group privacy statement on our Group Website URL Privacy Statement Oracle Partner Logo