Problem exporting database from SQL Server 2005 to 2014

LeTay
LeTay used Ask the Experts™
on
I am trying to export a database from a SQL Server 2005 to SQL Sever 2014
I use SQL Studio installed on the "2014" machine
The "2005" is on another (production) machine
In Studio, I connect to both server then on the 2005 item, I right click, select Export and fill in the fields.
Then GO, and I get this after a while
Login timeout expired
A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
Named Pipes Provider: Could not open a connection to SQL Server [2].  (Microsoft SQL Server Native Client 11.0)
I verified the configurations and both server have named pipes enabled
Whatelse shoudl I check ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
AneeshDatabase Consultant
Top Expert 2009

Commented:
Did you try restoring the database from 2005 on to the new server instead of exporting them

Author

Commented:
I tried right now and it worked
I think that the problem comes from the fact that the server (running SQL Server 2014) refuses non-local connections.
But I don't know how to enable that
Running the SQL Server configuration manager, I see that all Client protocols are enabled.
But the items SQL Native client 11.0 configuration 32 bit and SQL Server Network Configuration 32 bits are "empty"
Maybe that's the problem but I don't know how to fix it
Mohammed KhawajaManager - Infrastructure:  Information Technology

Commented:
You cannot export the database.  The way to get SQL 2005 DB on to SQL Server 2014 is as follows:

1.  Perform a backup of the database
2.  Restore the backup on to SQL Server 2008
3.  Go to properties, Options, change compatibility level SQL Server 2008
4.  Backup the databases again and restore in SQL Server 2012
5.  Change compatibility level to SQL Server 2012.  Backup the database
5.  Restore the database on SQL Server 2014.  If you choose, you could change the compatibility to SQL Server 2014 or leave it at 2012

Below are some other options:

- Upgrade the older version of SQL Server to be at the same level as the newer SQL Server and this will allow you to either backup and restore or move the databased by attaching DB files
- Script out the objects from the newer database and then use a process to extract the data from the newer database and import it into the older database
- Build an SSIS package to move the data by using Import and Export Wizard (it will move the data only and database needs to be created on the target)
- Build a custom SSIS package to do the data move
- Use replication to move the data to the newer database from the older database
- Use some third party tools
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
I succeeded to restore on SQL Server 2014 the backup of SQL Server 2005 !
Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
Don't forget to change the database compatibility, rebuild the indexes and run a full backup.
I wrote this article with all steps you need to perform. Please confirm that you didn't miss nothing.
Cheers

Author

Commented:
The compatibility of 2005 and 2014 where already correctly set

Author

Commented:
Well just backup and restore worked fine !

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