Avatar of LeTay
LeTay
 asked on

Problem exporting database from SQL Server 2005 to 2014

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 ?
Microsoft SQL ServerDelphi

Avatar of undefined
Last Comment
LeTay

8/22/2022 - Mon
Aneesh

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

ASKER
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 Khawaja

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
LeTay

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Vitor Montalvão

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
LeTay

ASKER
The compatibility of 2005 and 2014 where already correctly set
LeTay

ASKER
Well just backup and restore worked fine !
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.