Irina Tudose's blog

It has been a long time since I wrote a new post, but now I ‘m back after a very busy period and I would like to share with you a recent problem I had with a restore from a higher version of SQL Server – SQL 2008 R2 to a lower version – SQL 2008 SP1.

If you search the internet, you will be sad to find out that:

1. The restore from a database backup made on a higher SQL version will fail on a lower SQL version because the SQL is not up-level compatible.
2. The attaching of data and log files created on a higher SQL version will fail on a lower SQL version because the SQL is not up-level compatible.
3. Scripting entire database (SQL 2008 R2: Tasks –> generate scripts –> script entire database –> advanced: select to script schema and data) and executing on destination server can get you what you need but it works only if the database is not very big and the output of the sql file is in reasonable size parameters.
4. Another scripting method is to do it step by step (object by object): create database, export tables with export/import tool (easier because you don’t have to deal with constraints), create primary keys, foreign keys, default and other check constraints, indexes, views, stored procedures, migrate users security, cdc feature, triggers… etc. As you can see it is time consuming and you can’t do a single mistake. If the database was as big as I had to deal with (over 60 tables) then it can become a nightmare.

Gladly, there is one single possibility of “restoring” the database and having all of needed objects inside in a very elegant way
The duration depends on the database size. If you have do it relatively urgent like I did and can’t afford to go through the above alternative steps (3 and 4), please read further. By the courtesy of one of my readers, as he pointed out: very important to know is also that this method will work if the database migrated doesn’t have encrypted procedures. To use this method, you need to remove encrypted procedures and recreate them after the migration. I couldn’t find anywhere this tip even if this is an SQL feature and I thought many can find it usefull. On MSDN, the solution presented below that works for a database downgrade is treated only as an database upgrade option. (Using the Copy Database Wizard).

Things that are good to now

The database version is a number stamped in the boot page of a database that indicates the SQL Server version of the most recent SQL Server instance the database was attached to. The database version number does not equal the SQL Server version. For example, doing the following:

SELECT @@version;
GO

will display information about the SQL Server installation build, meaning the whole engine. The database version however is not the same.
Running this will get you the database version:

USE master
GO
SELECT DatabaseProperty ('DATABASE NAME', 'version');
GO

Database compatibility level determines how certain database behaviors work. This is set on database level. You can have a lower database compatibility level than the default one. On 2008 instances the default compatibility level is 100, and it can be 90 or 80 if you need the database to be compatible with a lower SQL Server edition. Setting a lower compatibility version does not allow the database to be restored on a lower SQL Server version.

In the following example, I will detail how you can use the Copy Database Wizard to migrate a database created on SQL 2008 R2 to SQL 2008 SP1, meaning to downgrade it from database version 661 to 655.

As a note to this, and by the courtesy of one of my readers I have to add that this option is only available if you don’t have encrypted stored procedures inside your database. If you do you will receive an error and will need to escalate this problem: either drop the procedures and re-create them after the copy database process described below, either un-encrypt the procedures before the copy database process and re-encrypt after.

Copy Database method

1. Step 1 – Select source and destination servers

Start by selecting the database that you want to downgrade. Right click on it and go to: Tasks –> Copy Database… Press Next. In “Select Source Server” window specify the source server name and the authentication type. In my example I used Windows. If you use SQL authentication, it should be with a user that has sysadmin role assigned. Both Windows and SQL need sysadmin role.

Press Next and in following screen, select the destination server with same considerations on authentication type as on previous screen.

2. Step 2 – Select Transfer method

There are 2 options: Use detach and attach method and Use SQL Management Object method.
If I would select the detach/attach method, the database would be detached, the data and log files would be copied to the destination server and finally attached to the destination server. This method would fail because the attach method is not supported on lower SQL versions.

This is why you need to select the second option: SMO method.



Select Transfer Method


 
 
 
 
 
 
 
 
 
 
 
 
3. Step 3 – Select Database


Select Databases


 
 
 
 
 
 
 
 
 
 
4. Step 4 – Select Database file location

Select the location on disk for the future database files:



Configure Destination Database (1 of 1)


 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
5. Step 5 – Select Server objects

You can migrate Logins, SQL Agent Jobs and other objects. I selected only the logins for the migrated database.



Select Server Objects


 
 
 
 
 
 
 
 
 
 
 
6. Step 6 – Configure the Package

The SSIS package created will be integrated into a new SQL Agent job with the same name as the package but you can change it. This can be executed immediately or scheduled. In this example I will execute it immediately. Also, you can store the log file and to check afterwards what exactly happened beyond this wizard.



Configure the Package





Schedule the Package


 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
7. Step 7 – Finalize the migration

Review that all the information displayed in below screen are valid.



Complete Wizard


 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

At last if someone is curious of what exactly this SSIS package is doing, I thought it would be useful to post the package log from both types of methods: Detach/Attach & SMO. You will see the difference between them starting the moment when the data and log files are created. In SMO, these are created not copied.

Detach/Attach
 

Transferring database DB_2008_R2 from SERVER2008R2 server as DB_2008_R2 to SERVER2008SP1 server
Dropping all connections to DB_2008_R2
All active connections to DB_2008_R2 are dropped
Detaching database DB_2008_R2 from source SQL Server
Database DB_2008_R2 detached from source SQL Server
Copying file D:\SERVER2008SP1\MSSQL\DATA\DB_2008_R2.mdf to G:\SERVER2008R2\MSSQL10.LCS\MSSQL\DATA\DB_2008_R2.mdf overwrite set to False
Copying file D:\SERVER2008SP1\MSSQL\DATA\DB_2008_R2_log.ldf to G:\SERVER2008R2\MSSQL10.LCS\MSSQL\DATA\DB_2008_R2_log.ldf overwrite set to False
Attaching database DB_2008_R2 to destination SQL Server as database DB_2008_R2
Attaching database DB_2008_R2 to SERVER2008SP1, SetBrokerState=True, AttachOptions=None
OnError,SERVER2008R2,DOMAIN\SQLUSER,SERVER2008R2_SERVER2008SP1_Transfer Objects Task,10/21/2011 12:00:32 PM,10/21/2011 12:00:32 PM,0,0x,Attach database failed for Server ‘SERVER2008R2′.
[...]
InnerException–>The database ‘DB_2008_R2′ cannot be opened because it is version 661. This server supports version 655 and earlier. A downgrade path is not supported.
Could not open new database ‘DB_2008_R2′. CREATE DATABASE is aborted.

SMO
 

Transferring database DB_2008_R2 from backenddc server as DB_2008_R2 to backenddc\LCS server
Added data file DB_2008_R2 in path G:\LCS_INSTANCE_2008_SP1\MSSQL10.LCS\MSSQL\DATA\DB_2008_R2.mdf to file group [PRIMARY]
Added log file DB_2008_R2_log in path G:\LCS_INSTANCE_2008_SP1\MSSQL10.LCS\MSSQL\DATA\DB_2008_R2_log.ldf
Transferring data to database DB_2008_R2 from DB_2008_R2
Transferred data to DB_2008_R2
Transferring server objects to server backenddc\LCS from backenddc
Database transfer is complete.
Transfer objects finished execution.