How Do I Move My Database To A New Server? - SQL Edition

This article will outline how to move your SQL Edition database from an old server computer to a new server computer.

When you are ready to move your database, make sure the software is closed on all computers. Make sure no one is adding or modifying the database at all until you are done with the database move. It is also recommended to install with full local administrator privileges, and to temporarily pause or disable your antivirus, to prevent any installation issues.

Setting Up The New Server

On the old server computer create a backup of your SQL database via Microsoft SQL Server Management Studio. You should be able to right click the database and go to Tasks | Backup. Make sure it is a full backup. Incremental will NOT work. The backup file will more than likely be in a .BAK format. You may also wish to make a copy of the C:\MP_DATA\ directory which contains your .MDF file (it may be a different folder depending on initial install settings). Place the backup somewhere handy, like a shared network location, or a backup drive, as we'll need to access it from the new server.
On the new server computer, make sure SQL is installed with a running database instance and with Microsoft SQL Server Management Studio available to use.
Install Maintenance Pro using the setup file provided. The file is typically called mpversion_sql.exe (for example, mp2020_sql.exe) . Make sure to run the file as administrator. Go ahead and open the setup file. If the file is not available, please refer to your original order email for the download link. You can also email us at support@mtcpro.com with the name the software is licensed to and we'll send you a download link.
Choose Client/Server for the install option. This way we'll have access to the program files for testing.
Click Next. Choose the location for the database. C:\MP_DATA\ is the default. This will be the location of your .MDF file.
Click Next to choose the Program Files location. The default is usually fine here.
Click Next to view the Database Connection screen. Set the Remote Host name to be the NEWSERVERNAME\SQLINSTANCE if you have your SQL Server setup as a named instance. Here is an example:If your SQL Server is the default instance, then entering in SERVERNAME only in the Remote Host Name box will suffice. If you are unsure of whether or not your SQL server is setup as a default or named instance please contact your IT administrator.
Click Next to choose the start menu folder location, then press Install to finish the installation process.

The installation process will create a blank MP.MDF file with sample data and attach it to your SQL server.
Make sure to right click the icon on the desktop and choose 'Run as Administrator' for this step.
Open the software on the server computer. It will ask you to register. Enter your User Name and Serial Number that has been provided to you via email. You can re-use the same registration info as your previous installation, if we have not assigned you a new license key.
Once the software is registered it should let you open the program. You'll see your home screen and sample equipment list. Make sure our software opens successfully with sample data before continuing to the next step.
Close Maintenance Pro if it's open on the new server. At this point, we need to restore our SQL database backup file. Open up the Microsoft SQL Server Management Studio on our new server. You should see an MP database listed. Right click on it, go to Tasks | Restore | Database:
On the Restore Database screen, choose From Device and click Add. Browse to the .BAK file you created from your old server computer.Make sure to check the Restore option here:Note: If you have an option here to restore the .LDF log file, you can ignore it. We do not wish to restore any log files, just our main database.

On the left panel, click on Options and check the option for Overwrite the existing database at the top. This ensures we overwrite the sample database with our backup file.
Press OK to restore the SQL data. You should be warned of any errors that occur. The most common error is that a connection to the database is already present. Make sure you turned off your instance of the Fleet Maintenance Pro client before attempting to restore.
Once the restore is complete, open Maintenance Pro on the new server computer. Check and make sure all of the data looks good. If it does, our data migration is almost complete!

Cleaning Up The Old Server And Configuring The Workstations

On the old server computer we will need to turn off the SQL database being used for Maintenance Pro. You can turn off the SQL database instance through Windows Services. This will effectively prevent any old workstations from accessing the data. Another method is to turn off the database within the Microsoft SQL Server Management Studio by right clicking on the MP database and going to Tasks | Take Offline. We want the database to be unreachable, so the clients do not accidentally connect to it.
Once the database is turned off, open up the program on the workstations. After double clicking the icon to open the software you will get an error message about not being able to connect. Please note that it could take up to 2 minutes for the message to appear before the timeout is exceeded. Once the error appears, click through it to see the Network Data Path Configuration window:Here you will want to enter in new Host information. This will be your SERVERNAME\SQLINSTANCE (or just SERVERNAME if SQL is the default instance) and will be the same setting as what you used on initial install on the new server. If you choose to use an IP address here for the new server, make sure the IP of the server is a local static IP address. Windows Authentication is generally enabled for most installs, but you can uncheck it here if you wish.
Once you enter the new information, click Save and try to open the software. It should open and you should see you current equipment list. If it does not open, check your settings on the Network Data Path screen to make sure they are correct.

Repeat these steps for all workstations until they are all able to connect to the new server's database.
Once you've confirmed everything is up and running on the workstations, you can uninstall our software from the old server. Please note, uninstalling will unattach the database from SQL and completely delete the .MDF file, so it may be wise to make an additional SQL backup file, and an additional copy of the C:\MP_DATA\ directory, just in case you need to reference the data later for any reason.

At this point you're done! Data is migrated and workstations are all pointed to the new database. Old server no longer has an accessible database, so if you missed any clients they will be presented with that Network Data Path screen.

Additional information which may be useful:

Do I need to get a new registration key for my new computer?
You do not need a new registration key. You can re-use the same user name and serial number that we sent to your email address upon initial purchase. If you have a key for a previous version it will not work. You'll have to purchase an upgrade from us to get a valid version key. Please contact us at sales@mtcpro.com for upgrade pricing.

I put in my user name and serial number in the registration window. However, the program doesn't open. It keeps asking to register.
On your desktop, right click the Maintenance Pro icon and choose "Run as Administrator". Then try to register. The software needs the extra admin rights in order to register the product.

I'm receiving a message saying the program version is out of date, or doesn't match the database version. What do I do?
This can happen if the installation file we used on the new server is more up-to-date than the clients at the rest of the office. You can access SQL updates via our website here: https://www.mtcpro.com/updates-sql/. You can also email support@mtcpro.com and we'll send you a link to the latest SQL build update. You'll want to run the update anywhere the program can be opened. If the server computer does not have an icon for Maintenance Pro then the update does not need to be run there.

Will SQL Express work for us?
As you can see, we have used SQL Express in our example screenshots. However, there is a memory usage limit on the Express version. If you are not using very many clients, Express may be an option for you. However, if you use it heavily, you may want to invest in a full SQL database license from Microsoft that does not have the same limitations as Express.

I'm having problems restoring my .BAK file. What can I do?
Microsoft SQL can be picky about SQL versions. If the .BAK file is created in a new version of SQL, and you are loading it onto an older version, or different version, you may run into issues with restore. Generally, you can install a newer version of SQL on your new server and be okay, but versions that are older than what you currently have on your old server may give you an error. You will also want to make sure that there are no active database connections before attempting to restore the .BAK file.

I am getting messages about not being able to login to the SQL server, or login is denied, when opening the program.
On SQL Server there is a Security section where you can allow access to users on your network, such as the Domain Users group. Make sure that users have full DB access to the MP database. Note that you can also un-check the 'use windows authentication' option as this will use our built-in IMS user which was added to SQL during the initial install.