SQL SERVER – Restoring 2012 Database to 2008 or 2005 Version and 2 other Most Asked Questions

Some questions never get old. Let me list a few of them. As the year 2012 is about to end, today I will talk about three of the most asked questions to me in an email.

Q 1: What is AdventureWorks and where can I get it?
A 1: AdventureWorks is sample database build by Microsoft for running sample/demonstration queries for the user. You can get that from CodePlex. I have written an article about this subject earlier and have created video as well.

Q 2: Can I restore database backup taken from 64-bit server to 32-bit server and vice-versa?
A 2: Absolutely, You can do it as backups are just a file. Just like any other files (JPG, docs, etc) you can use them in 32 bit and 64 bit application server without any issue. The issue related to the platform should only arise with the application (like SQL Server, Office etc.).

Q 3: Can I restore a SQL Server 2012 database to earlier versions like 2008 or 2005? Or How to Downgrade my Database to earlier versions?
A 3: No, you can only move forward. This means, you can restore the database from 2005 or 2008 to 2012 version but can not do it in reverse order. The next most popular question I receive is if we can’t restore a SQL Server 2012 database to earlier version what is the next best option? I have personally faced this issue once before and I had manually created T-SQL script using Script and Data Generator Wizard in SQL Server 2012  (Watch Video over here) and rebuild my database in SQL Server 2008R2. If you have many different databases which you want to address, you can use SSIS to automate the script generation.

Here is one question, I always ask back to my users – why do you want downgrade your database? I have hardly gotten valid detailed answer on this subject. Did you face similar situation ever in your future? If yes, I would like to know more about it.

And one more thing…

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

35 thoughts on “SQL SERVER – Restoring 2012 Database to 2008 or 2005 Version and 2 other Most Asked Questions

  1. Normally no need to downgrade database but some time we have to downgrade our product’s database because of higher version database not available at client side OR client want to use same database version for their old & new products.

  2. Pingback: SQL SERVER – Take Database Backup using SSMS – SQL in Sixty Seconds #037 – Video « SQL Server Journey with SQL Authority

  3. One scenario to downgrade databases:
    - the database is created in-house and delivered to customers as backups
    - databases from customers are needed ( for example my company does complex analysis on the Cloud using backups we retrieve from customers) .

    In this case you have to have one machine with SQL 2005 ( our lowest supported version ) to create new databases and then SQL 2012 ( our highest supported version ) to do data analysis.

    It would be nice to be able to downgrade and keep only SQL 2012.
    But a workaround with two machines works fine.

  4. I’ve used Transactional Replication to migrate a database from 2008 R2 to 2008. This was because prior to my hiring the business bought COTS and manually installed SQL Server 2008 R2 and started running. After some time they had performance issues when usage scaled. Vendor only supported 2008, not R2.

  5. pinal and madhivana sir

    happy new year

    sir this question is very important for me for me help this question

    i am use express edition sql server 2005

    i am payroll administrator in export company

    some database coding use to payroll software

    i want to change employee(helper & talior) data hours

    means payroll software se excel sheet main month ka backup le liya

    after that i want to import to sql server 2005 express edition

    after that some coding

    pinal dave we can use import and export function in express editon if yes then me question

    means if one employee work in one day 9;30 to 6.00 p.m
    some day work in 9.30 to 8.00 p.m
    some day work in 9.30 to 1.00 am.

    total employee 2000 thousand

    means if 100 employee work in 9.30 a.m to 1.00 p.m am than i want to change hours 9.30 to 6.00 P.m means 5 hours less only for one time coding

    if yes plz send me query for this question

    and also happy new year you and your great family

    happy

  6. Dear sir

    i am waiting your answer

    madhivana and pinal dave sir this changes only for audit time

    means as 1.30 a.m change to 6.00 pm

    by sir

  7. dear pinal dave sir

    my question i want to update column in one time command

    if i want to work in one company

    my in time 9.29 out time 19.44 hour1=10.29 ot hour=2
    means 8 hour working and 2 hours ot

    if i want to change my in time 9.29 out time 19.00 hour1=9 ot hour=1
    whne i command use for this query

    if total 100 employee what are command use for this query

    this question important for me becuase its my project work

    happy

  8. Dear Sir,
    I used * for Outer Join till SQL server 2008 R2. But Without Testing , I upgrade my Database to SQl server 2012 , So now i had to Upgrade my All queries 7 it is a Very hatic task for me as project is quite big. Plz help me out of this to direct run that queries in SQL server 2012.

  9. The reason I want to move a database from 2012 to 2005 is to make a test copy. I don’t see why upgrading the SQL product should legally compel me to upgrade my DATA. As in, MY data. It’s my data. I have full valid licenses to both tools.

    MS is doing everything it can to end its run for this product, in my opinion. Maybe they don’t need the revenue at like $30K a pop. Raise it to $60K and I can hire a guy for months to find me some other option.

    Not to mention what it’s costing my client to research the situation. And what it costs to figure out the byzantine licensing rules in the first place. And to this day I haven’t found the legal document defining CALs. Please don’t respond and try to explain it to me. I know what MS WANTS it to mean. I want the legal mumbo jumbo the lawyers will argue over so I can ask them what it means. The opinion of no one else counts, including MS “agents” who are not actually legal agents at all.

    It’s a world full of children these days.

  10. Paul:I am in the same situation as you are with wanting to have a test database. I have the licensing to run 2005 and to run 2012. I would like to be able to run only production data on the 2012 box and then every once and a while copy the production to test.

    I am pretty sure it has nothing to do with licensing rules, but more to do with the fact that the backup files made in server 2012 have been changed (probably at least three times) since the last update to 2005 has been released.

  11. Pinal,

    I am trying to restore a backup from 2008 R2 to another server running 2008 R2 only but the versions are different means the source database running 10.50.2500 but the server where I am trying to restore the database is running 10.00.1600. How do I restore the backup?

  12. onkar,

    The destination server 10.00.1600 is SQL Server 2008 RTM. You would need to have 2008R2 with at least Service Pack 1 installed in order to restore the DB

  13. Paul, I faced the same situation twice:

    First time, I worked on a company with SQL server 2008 Standard Edition and We bought a new software, they sent us a preconfigured backup (with data) in SQL Server 2008 R2. We have to use a script but the database was very big and it took a lot of time to run (because of the data)

    Now, I face almost the same situation, I’m developing a software, I have SQL Server 2012 Enterprise Edition and I have a customer with SQL Server 2012 Standard Edition. I can restored a backup from Standard to Enterprise Edition, but I cannot restored a backup from Enterprise to Standard Edition… they are in another city and they prefer to send me backups when they have problems… and I send back the backup with the problem solved.

    I can to manage my own licenses, but I can’t do anything if they can’t buy a greater license.

  14. I use backups of databases as a way for my customers to send me their data. I will ask for the customer to send me a backup and I just restore it. I am using SQL Server 2012, but some of my customers are still on 2005. So, for me to send the data back to the customer, I would like to backup the database and send them the backup so they can restore it. Problem is, I can’t do that backwards. So, this is a need for me.

    • I have a similar need. I currently have 2008 R2 running on my laptop because all of my customers but one are on 2008 R2 or earlier versions.

      But, now I have a 2012 customer and I can’t restore his backup on my laptop without installing 2012.

  15. Hi, in answer to the downgrading… I am not actually downgrading. My development database for the application I am working on is SQL Server 2008 and my training/production DBs are on 2012. Now the prod/training have gotten out of sync with the dev db so I have to work out some way of getting all 3 in sync again (having to UAT test against the new production environment is a nightmare). I am going to give that Script and Data Generator wizard a shot and see how that goes.

    Cheers,

  16. I ran into a truly valid situation with a customer who was migrating data. Their old platform used SQL 2012 Express and their new platform uses SQL 2008 R2. So, not really downgrading just migrating data but need to be able to access both and customer only has SQL 2008 R2 (old version was hosted).

  17. I use GoDaddy.com for hosting purposes, and it is almost the only one that supports MS SQL. And that too, it only supports 2008 Or 2005.

  18. Our production servers all run 2008 R2 enterprise. As a developer, ideally I would use SQL Server 2008 Developer Edition. However, this edition is NO LONGER AVAILABLE, so the only available option is to install 2012 Dev. Edition. Deploying databases from my development environment to our production servers is a major headache because of this. Having to generate scripts causes all sorts of issues. Why do they discontinue the older versions of the developer edition?

  19. I will tell you why we need to restore in both directions. We have dozens of clients using our mission critical software to run there businesses. We need to get copies of their data for testing and training and updating there databases. We have copies of ALL of our customers data and it is becoming a real problem because if their version is more recent than ours we can’t use their data and vice versa.

  20. What’s the problem with using Tasks > Generate Scripts (change to Schema + Data) to export, sqlcmd to import? Maybe a bit of editing required. Version-, even provider- agnostic.

    • I agree Kevin Shackleton. I can understand backup and restore not working, but it’s not uncommon to generate Schema + Data to move from one DBMS to even another brand, or collation. You can either edit the script it manually or use a design tool.

      The versions would need to be the same to use backup and restore, or detach and attach to another DBMS + fix users.

  21. Because we are still in the early stages of developing a 2012 solution for our environment and need to be able to update dbs in 2012 before pushing live in 2008 until our prod environment is ready to be updated to 2012.

  22. The reason why is classic…

    One small company uses SQL Express 2008, our company is much larger, so to get past the limitations of Express we bought SQL 2005 many years ago, and have licenses per user, for about 300 staff.

    Now our companies merged, and we need to merge the data as well.

    We simply can not go to any version of Express.

  23. Hi All,
    Good day to You,

    Here in my organization i am facing issue in restoring DB to Lower version from 2012 to 2008, 2008R2..
    One of my client using SQL 2012 version & i want to take entire backup & want to restore it on 2008R2.
    Is it possible me to do this? As i searched on net, i found its not possible to do in reverse order.
    Can someone help me out how to do this. It would be very helpful if i get any hint from your side.

  24. Well, here is a case where downgrading is required: A partner gave me a database created on SQL server 2012. I run windows XP, which means that SQL Server 2012 is not supported. Can you tell me how am I supposed to load this database?

  25. Hey Dave, you asked for meaningful reason to downgrade a database.

    I have worked at two companies at least that take production versions of their database and restore it on a Dev server, having an older version of SQL Server than production because they purchased the production server after the release of the new version and did not have the media to install the older version on production.

    The reason for restoring back on a dev box is to capture real time user interactions with the database. Both companies had a scrub process. This made debugging production database issues much easier as well.

  26. It is terrible, because I’m in situation where there are 3 deployments – develop, testing and production. Testing + develop has same version but production has newer version. I was restoring testing enviroment from production. I have bad luck :(

  27. Use the Red Gate SQL Compare tool …………….that will make your life easy…..you can easily restore database from 2012 to 2008R2 which i did in my case.

  28. When you have two departments in the organization not related to each other that manage the data in different database versions. Sometimes it is necessary to transfer a data from one department to another. (from 2012 to 2008).

  29. “Here is one question, I always ask back to my users – why do you want downgrade your database?”

    Well, cost of implementing a new dbms environment, and for the current operations it’s what we need, this is our only choice to get the data where we need it. I know that you didn’t write the DB application to “only move forward” as you say, but I do think it’s pretty naive to ask such a question, every company has their reasons for what they do, and as the person tasked with doing it, I just trying to figure out how to do that.

    • Hi Donald,

      Good point and I may be naive. However, I am not sure why upgrade first if they want to downgrade afterwards. They should do enough test before upgrading it.

  30. “Here is one question, I always ask back to my users – why do you want downgrade your database?”

    We developed an application using SQL Express 2012 and when we ready to move to a hosting, where they support only 2008.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s