How To Move Your Database From One Server To Another

This is an advanced topic please consult an IT specialist or NumberCruncher if you are unfamiliar with any of the steps below.

Please note that after performing the move you will need to reenter your license keys in All Orders.

1) On the server that the database is going to be moved to, make sure that SQL Server is already installed. The version should either be the same or higher than the version of SQL Server that the database currently resides on. For example if the database is currently running on SQL Server 2012, it must be moved to SQL Server 2012, SQL 2014, or SQL 2016. The server should be accessible to all the workstations and all the correct ports should be opened in the firewall and protocols enabled. If you install your own instance ensure the Server Collation is set to SQL_Latin1_General_CP1_CI_AS. You can verify this in the SQL Server Configuration Manager tool. On the Start menu, point to All Programs, point to Microsoft SQL Server 20XX, point to Configuration Tools, and then click SQL Server Configuration Manager.

If you have a firewall and need to configure which port SQL Server is using before opening the port in the firewall read the following article:

If you want to ensure the proper protocols are enabled (for All Orders TCP/IP must be enabled) read the following article:

If you do not already have a SQL Server installation package for the new server you can download the SQL Server 2012 installer from NumberCruncher by using the following link:

**NOTE-- If you are installing on Windows server 2016, you must install SQL 2016 from the below link as it is the only version that is compatible.


You must also install SQL server Management Studio 2017 separately. Link below.

Once the SQL Server is up and running you can test connectivity by creating an ODBC connection to your SQL Server. This will also help your client workstations to connect to the new server. The following article will walk you through the setting up of a new ODBC connection:

2) Open All Orders as the admin. Go to the File menu and click on Maintain. Select the Advanced tab. Make sure everyone is logged out of All Orders and then click the Detach button to take the database offline so that it can be moved. If you get an error detaching the database stating that it is still in use you might need to reboot the current database server to drop lingering connections to the database. Once detaching is successful a message will display where the database file is located. Click OK and leave All Orders open.

3) On the new server create a new folder in the root of one of the drives (for example C:\All Orders Data). This folder is where the database files will be moved to and permanently stored.

4) Browse to the folder where the newly detached database is stored on the current database server. It will end with a .MDF file extension. In addition there is another file with the same name but a .LDF extension. Copy both of these files to the new folder which was created in the root of one of the drives on the new server. If the .NCD file resides on the current database server as well do not move it yet. We will move it later in this article.

5) In All Orders go to the File menu and bring up the Maintain screen again. Select the Advanced tab. Click the Server Settings button. Since the company is currently closed it will prompt you to open the last opened company file. Do this and settings that have been set for the current database server will come up. Under the "Computer" section make sure the new installation of SQL Server is selected. You can check the "Select instance of SQL Server" checkbox to get more details. By default it will be the name of the new database server with a \ALLORDERS at the end. If it is not in the drop down you might have to manually type it in. Under "Database" select the .MDF file in its new location on the new database server. If you are not performing this move on the new server itself you will have to temporarily share the newly create folder that the .MDF/.LDF file were moved to so that they can be selected in this step. If a new SQL user name or password have been setup be sure to enter them, otherwise the default user name and password (user: sa password: Sysadmin1) will work on the new server as well.

6) Once you have verified all of the information, click the OK button. All Orders will see that the file is currently detached and prompt you to allow it to attach the database to the new server. Click the Yes button and if all of the steps have been followed correctly All Orders will open as usual. You will see in the title bar of the All Orders window that you are now connected to the new server. If you previously shared the new folder the .MDF/.LDF files are located in you can now remove the sharing.

7) If the .NCD file is on a shared folder on the current database server you will also want to move it to a new shared folder on the new database server. Be sure to close out of All Orders before moving the file. Please note that the .NCD file and the .MDF/.LDF files do not need to be in the same folder. I addition only the .NCD file will need to be in a shared folder as All Orders will need to be able to access it across the network. Once you have moved the file each workstation will be prompted to browse for and select the new .NCD file before All Orders can open.