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 ?
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 ?
Aneesh
Did you try restoring the database from 2005 on to the new server instead of exporting them
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
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
I wrote this article with all steps you need to perform. Please confirm that you didn't miss nothing.
Cheers
ASKER
The compatibility of 2005 and 2014 where already correctly set
ASKER
Well just backup and restore worked fine !