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. Let us learn about Restoring databases.
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 here. I have written an article on 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 to downgrade your database? I have hardly gotten a 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 (https://blog.sqlauthority.com)
54 Comments. Leave new
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.
the video is not visible properly…
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.
Facing this in a similar way. Database is from third party who uses 2012 and we use 2008 in house.
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.
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
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
How do we check the latest DB resore date using SQL server 2005/2008?
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
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.
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.
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.
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?
Hi
Perhaps the versions are different because the service packs are no the same?
10.00.xxxx is SQL 2008 whereas 10.50.xxxx is SQL 2008 R2. You can’t go backwards.
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
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.
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.
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,
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).
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.
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?
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.