Restore SQL 2008 R2 Database on SQL 2008 SP1 instance

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
  2. 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.

  3. Step 2 – Select Transfer method
  4. 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

    Select Transfer Method


     
     
     
     
     
     
     
     
     
     
     
     

  5. Step 3 – Select Database
  6. Select Databases

    Select Databases


     
     
     
     
     
     
     
     
     
     

  7. Step 4 – Select Database file location
  8. Select the location on disk for the future database files:

    Configure Destination Database (1 of 1)

    Configure Destination Database (1 of 1)


     
     
     
     
     
     
     
     
     
     
     
     
     
     
     

  9. Step 5 – Select Server objects
  10. You can migrate Logins, SQL Agent Jobs and other objects. I selected only the logins for the migrated database.

    Select Server Objects

    Select Server Objects


     
     
     
     
     
     
     
     
     
     
     

  11. Step 6 – Configure the Package
  12. 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

    Configure the Package


    Schedule the Package

    Schedule the Package


     
     
     
     
     
     
     
     
     
     
     
     
     
     
     
     

  13. Step 7 – Finalize the migration
  14. Review that all the information displayed in below screen are valid.

    Complete Wizard

    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.

Leave a comment ?

14 Comments.

  1. Very Nice Explanation and Representation !!
    Thanks a Lot !!

  2. This is just what I was looking for. The only option for transferring a SQL 2008 R2 db to SQL 2008 seems to be the Copy Database wizard with the SMO option.

  3. Hello, very nice. Do you have any experience with moving reports from SSRS 2008 R2 to SSRS 2008 SP1? I would really appreciate if anybody could give me a hint. Thanks.

  4. Sorry but I never worked wit ssrs. Did you try to search online for d some guidance? Good luck!

  5. Good Post… Same i am looking for. Thanks 😛

  6. I am glad to hear that!

  7. Something for everyone to be aware of…

    I am in exactly the same situation i.e. needing to move a database from a SQL 2008 R2 instance to a SQL 2008 SP1 instance. In my case the database was created by the installer for a 3rd-party supplied application, so I wasn’t 100% familiar with everything in the database.

    It turns out that the database contains encrypted stored procedures, and the Copy Database Wizard fails when trying to copy those, ultimately with this error:

    “Property TextHeader is not available for StoredProcedure ‘[dbo].[Security]’. This property may not exist for this object, or may not be retrievable due to insufficient access rights. The text is encrypted.”

    If I find a way around this I will post details, but I suspect it’s back to option 4 at the top of your article. Wish me luck!

    (However I would certainly recommend the procedure described in the article if you *don’t* have encrypted objects to deal with — a very good post — thanks.)

  8. Thank you for the add, I will update that step of course. I had no idea about this. May I give you some advice… Can you alter the procedures just for the migration part and comment the “with encryption” part in the stored procedure code, so these will not be encrypted anymore and try again the process. I guess you have the text code behind those encrypted stored procedures, other wise you will not be able to re-create them even using the fourth option.

    After you migrate you can alter them again and re-encrypt. Or… the ecryption method is other than the default one used by SSMS?

  9. also an option would be to drop the procedures and re-create them after the copy database process.

  10. Many thanks for your replies.

    As the database is installed by a 3rd-party app, I don’t have the un-encrypted code of the stored procedures, or knowledge of the encryption method. So yes, you are quite right – I’m no better off with option 4.

    Looks like I may have to re-install the application (targeting a SQL 2008 SP1 instance for the database) in order to get the database structure created including the encrypted stored procedures. Then truncate all tables in the new SQL 2008 SP1 database, and use SSIS to copy data into it from the existing SQL 2008 R2 database.

    There are approx 300 tables in the database and almost 500 foreign key constraints, and I doubt if SSIS will “just know” what order the tables should be copied in to avoid FK problems. So I guess some scripting, dropping & re-creating of the FK constraints will be required….

    I’d much prefer not to have to do all this. But it’s a learning experience, if nothing else…!

  11. Yes you’re right ssis doesn’t create table restrictions meaning pk, fk, or other kind of keys. You will have to rebuild them. Good luck!

  12. Thanks Buddy 🙂 very good post what am looking for.

    Our requirement is to move 2008 R2 Databases from standalone to Cluster 2008 SP1.. We need to move SSIS packages also..

    I studied somewhere As per MS best practices SSIS is not recommended on cluster.

    Could you please assist me how to migrate SSIS from 2008 R2 to 2008 SP1 on cluster 🙁

  13. shreyas Jawalikar

    I tried this on Sql server 2008 R2 to Sql server 2008 but problem is on Last Service that is Sql Agent Job is not Successfully executed

    tell me the solution
    😕

  14. DO you mean that when you selected the objects you needed to migrate (step 5), you added SQL Jobs as well and it didn’t work? I can tell you I didn’t try it and would also most surely create them after the database was restored on destination server. you can script the job and execute it manually. perhaps there are some configuration options that can’t be migrated. Show me the error if you have it.

Leave a Comment


NOTE - You can use these HTML tags and attributes:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>