Failed attempt restoring a newer DB version backup file to older MSSQL Server Express 2008

I am using MSSQL Server Mgmt Studio 2008.

I haven't worked with MSSQL Server in 10 years. Back then it was MSSQL Server 2000. I have MSSQL Server 2008 Express installed on a Windows-7 PC. I am trying to restore a DB provided by a client from a backup named MP.BAK. The attached screen shot error message is telling me that the client backed up the DB with a newer version of MSSQL Server than the one I am using. So should I uninstall MSSQL Server 2008 Express and replace it with a newer version of MSSQL Server Express? If so which one? 2010 or 2012?. Is there  a work-around that will allow me to restore from a new version to an older version?

No need to answer every question. Just a solution that will work.

Thank you,
Ted Palmer
MSSQL-Server-DB-RestoreVersionEr.jpg
Ted PalmerInformation Technology ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mohammed KhawajaManager - Infrastructure:  Information TechnologyCommented:
I recommend using SQL Server 2012 Express Edition as it allows restoring DBs from other versions
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David ToddSenior DBACommented:
Hi,

Its possible to upgrade a database by restoring (or attaching the datafiles) on a more recent version of SQL - up to 2 versions, but not possible to go backwards.

Of course, to go backwards, you can always use the export/import mechanisms ...

So, 10.5 is SQL 2008R2.  You need to install either SQL 2008R2 or SQL 2012.

HTH
  David
0
Brian CroweDatabase AdministratorCommented:
10.50.2500 is Sql Server 2008 R2 A.K.A. 2010 so I would try that version of Express.   Express has a 4GB limit on the .mdf so if it's a large .bak you might need a non-express version.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Ted PalmerInformation Technology ConsultantAuthor Commented:
Thanks guys. If I can't complete this tonight, I'll get it done in the morning. Assuming MSSQL Server 2012 Express is the solution, which I am confident it will be, I will split the points between just the 2 of  you so as not to reduce the points awarded each any further.

Ted Palmer
0
David ToddSenior DBACommented:
Hi,

Just to clarify, SQL 2008R2 and SQL 2012 Express Editions have a 10GB size limit on databases

http://msdn.microsoft.com/en-us/library/cc645993(v=SQL.110).aspx
See section titled Cross-Box Scale Limits

http://msdn.microsoft.com/en-us/library/cc645993(v=sql.105).aspx
See section titled Scalability and Performance

HTH
  David
0
Brian CroweDatabase AdministratorCommented:
Thank you dtodd... I ran into it just last week but it was a 2005 express...didn't realize the extended that in later versions.
0
David ToddSenior DBACommented:
Hi BriCrowe,

I think for MSDB on SQL 2000 the limit was 1GB, and there was some query throttling over 10 concurrent queries, SQL 2005 (and SQL 2008) had a 4GB limit, which was lifted for 2008R2, but not for SQL 2012.

Unfortunately MS is always adjusting things, so often need to check out exactly which version/edition is under discussion.

Regards
  David

PS This also extends sometimes to the OS Version/Edition - ie While Standard Edition SQL 2008R2 can use 64GB Ram, Standard Edition of Windows around that era can only support 32GB of Ram ...
0
Ted PalmerInformation Technology ConsultantAuthor Commented:
A few minutes ago my download completed. I hope the MSSQL Server Mgmt Studio 2012 is in there somewhere. The download was 1.88 GB. Probably the biggest download I have ever done. I chose the Advanced option to get the extra tools. It looks like I may be doing MSSQL Server for a while. I am not going to start the uninstall / reinstall process tonight. I don't what to be up all night doing this. Once I get started, I can't stop till the process is finished.

Talk to you all in the morning.

Thank you for your help.
Ted Palmer
0
Ted PalmerInformation Technology ConsultantAuthor Commented:
Guys,

The 1st thing I want to say is that I now have exactly what I asked for. After installing/Upgrading to MS-SQL-Server 2012, I was able to "restore" the database from the MP.BAK file that I mentioned in my question. I know that it worked because I could look at data in some of the tables. Not a lot; but enough to know that it worked. There were a lot of tables.

I chose to do an update from  MS-SQL-Server 2008 to MS-SQL-Server 2012 because in Control Panel >> Programs and features there were about 20 individual programs to be removed. I didn't want to spend my time deleting/removing all those programs. In the run-up to selecting the down load there was mention of an "Advanced" version of MS-SQL-Server 2012 that had some extra software tools for DBAs and Developers. That is the one I chose so I could get the extra software tools. The install process now referred to what I installed as an evaluation version. Does that mean the what I installed will quit working after 180 days or something?

This whole update process was really convoluted. Without dumping my burdens of grief on all who might read this, The MS-SQL-Server 2012 MS-SQL-Server Management Studio showed that there were still 2 different server instances. The server instance that I restored the MP.BAK database to was named HP001\SQLSERVER2008EXP even though it was in MS-SQL-Server Management Studio 2012. The short cut on my desktop still points to MS-SQL-Server Management Studio 2008, and it appears to still be working OK.

Did I just get lucky or is this kind of upgrade experience or is this a common occurance?

Oh! I have to spread the points among 3 experts not 2 because another expert responded with the same answer as the previous 2 while I was still typing a comment.


Thanks a lot guys.
0
Ted PalmerInformation Technology ConsultantAuthor Commented:
Oh! A quick related question. Does the export/import process use SQL files; i.e. Does the export process create a text file of SQL INSERT statements that the import process can run as a script to load the data independent of version issues?

Thanks,

Ted Palmer
0
Ted PalmerInformation Technology ConsultantAuthor Commented:
Oh! A quick related question. Does the export/import process use SQL files; i.e. Does the export process create a text file of SQL INSERT statements that the import process can run as a script to load the data independent of version issues?
0
David ToddSenior DBACommented:
Hi,

It depends.

If on an older version of SQL it could use the BCP import/export files - there are a few formats to choose from. On the newer versions of SQL, it will try an SSIS package which can be saved for later, which will try and connect and push or pull the data directly to/from another source or destination.

HTH
  David

PS Should be another question ...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.