Backup SQL database from SQL 2008 and restor to SQL 2014

Math Tec
Math Tec used Ask the Experts™
on
Hi,

I am trying to backup SQL database from old server (SQL 2008) which is installed on windows server 2008R2 to new server (SQL 2014) which is installed on windows server 2012. The database is large (around 200G). I did the backup using SQL management Studio and I got the backup file. However, I tried to restore in the new server using restore database from management studio (SQL 2014) but I got an error.

Any suggestion how to backup and restore database from SQL 2008 to SQL 2014

Thx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ste5anSenior Developer

Commented:
Well, what error?

SQL Server 2014 can restores user databases from SQL Server 2005+.

Do you have enough space on the target drive? If your 2008 instance was Enterprise or higher, than the backup maybe also compressed.
Hi,

indeed an error would come in handy but you can directly backup from SQL Server2008R2 and restore it into SQL Server 2014 without any limitation. Just be sure to change the compatibility level of your database after restore complete by following query:

ALTER DATABASE <yourdb> SET COMPATIBILITY_LEVEL = 120

Open in new window


Cheers

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial