Can I restore or move a SQL database from MS SQL 2008 r2 web edition to MS SQL Express edition

Hi.

I have a dedicated server running MS SQL 2008 r2 for my web hosting business.

I'd like to set up a backup system on a lower spec (cheaper) environment that uses MSSQL Express.

I have tried backing up the 2008 r2 databases and restoring them on SQL Express, but get various errors.

Can anyone let me know if what I am trying to do is possible before I try to tackle all the errors.

Thanks!
rwlloyd71Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

Éric MoreauSenior .Net ConsultantCommented:
is your MSSQL Express also 2008 r2 (or better)? Are you using features not supported by the Express edition?

What are the errors?
lcohanDatabase AnalystCommented:
"I have tried backing up the 2008 r2 databases and restoring them on SQL Express, but get various errors."
What exact errors are you getting?
Please check the DIFFs between SQL Server EDITIONS at link below and see wich ones can lead to the errors (like DB size maybe?)

https://msdn.microsoft.com/en-us/library/cc645993(v=sql.105).aspx
https://msdn.microsoft.com/en-us/library/cc645993(v=sql.100).aspx
rwlloyd71Author Commented:
I get the following error:

"Specified cast is not valid"

The SQL version creating the backup is 11.0.5343.

The SQL version I am restoring on to is 10.50.4042
lcohanDatabase AnalystCommented:
The problem is you can't go back to earlier version and your SQL server where you need to restore it must be at least 11.0.5343 but that shouldn't be a problem as SQL Express is free so just download SQL 2012 express and it should work for you.

here are the build/version numbers association
https://buildnumbers.wordpress.com/sqlserver/

sql 2012 express dwonload link and put SP2 on the top - that should be enough to get the databases restored
https://www.microsoft.com/en-ca/download/details.aspx?id=29062

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
rwlloyd71Author Commented:
Thanks . I'll give this a try and see how I get on.
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.