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
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.
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.
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.
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.
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?
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.
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 :(
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.
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).
“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.
“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.
We have a client with legacy software running SQL 2000 – and SQL 2012 will not install on same computer. So now we need to downgrade our data from 2012 (which is in earlier compatibility mode for just such a situation – but of course that doesn’t work) to an earlier version. As it happens, we can do it via our own transfer tools .. but what a pain! A backup/restore would be so much easier ..
Regards
brewmanz
We use SQL2012, with data in earlier compatability mode. We have a client with legacy software using SQL2000, so they can’t install SQL2012 on same computer. Of course, our DB in old compatability mode can’t be backup/restored to say SQL2008. So what do we do now? As it happens, we can convert the data with our own tools, but what a hassle!
All M$ need to do, is allow ONE level of backwards compatibility for each version. Obviously new data types won’t convert, but doesn’t old compatability modes help to stop that …. ?
regards
brewmanz
Sir, I am downgrading from sql server 2012 Because it generating the gaps in Identity column and I have already entered data in my sql server 2012.
What if you have a client who is still on SQL 2005 and your company has already moved forward, do we need to maintain a license for 2005 just so we can continue to support such clients? Or do we have to force such clients to upgrade, at whose expense?
THE REASON OF DOWNGRADING NEED may come from the requiremensts of the application provider. In Poland there is a nationwide software called Program Płatnika that runs on a SQL2005 provided by the government. If someone upgrade it to a newer version of SQL (2008 or 2012) it works even better but prohibits of moving the database to the other entity. This is a frequent case since Program Platnika is a software for calculating liabilities to government and entity changing tax office (that helds databases) happens.
@cineS,
I agree. Before making any upgrade decision, application vendor should be contacted to know if they support new version.
Downgrading is mostly for developing in my case. I develop in SQL 2012, and lots of clients have SQL 2005… Sometimes you have only to create the DB, but other times you pre-load some information for those clients, so you have to export all DB to 2005…
Regards!!!
Hi,
I install an express version of 2005 R2 then use the export to copy the data into the older format … works like a charm…
Should it be possible to downgrade within the same release but different cumulative update level?
Got a backup of a production database that runs on SQL Server 2012 SP3 CU2 (Version 11.00.6523).
Our development database server currently is SQL Server 2012 SP3 without any CU (Version 11.00.6020).
When I try to restore I get error 3203 severity 16 state 1 with
read on “E:\path\to\backup.bak” failed: 13(The data is invalid.)
I’m questioning if this could be caused by the different SQL Server cumulative update levels.
Or if it’s more likely that the backup file got corrupted.
Thanks for your blog :-)
FYI: finally we found out that the backup got corrupted somewhere between customers SQL Server and our SQL Server :-)
Hi,
Please let me know whether it is possible to
restore user databases backup (not system databases) of SQL Server 2005(RTM)-32 bit on SQL Server 2012 (SP1) 64-bit
restore databases backup (not system databases) of SQL Server 2008(RTM)-32 bit on SQL Server 2012 (SP1) 64-bit
YES