Back Up, Move, & Restore Database

Database Overview

SQL System Administrator (sa) credentials are required to back up or restore a database if the SQL Server instance is not a TDG instance (a SQL Server Express instance provided with the Roadsoft installer).

It is important that agencies use the tools built into Roadsoft to perform these tasks. Using the backup and restore tools in the SQL Management studio will not work without a considerable amount of extra work.

A backup of the Roadsoft database takes a snapshot of the entire database, which includes all data, shape files, and legend information. The backup is stored on the database server’s hard drive by default.

Backing up data is essential, unless data loss is not a concern. At a minimum, backups should be performed prior to:

Any import of data into Roadsoft

Any updates and framework migrations.

Back up a Database

There are three different options to begin backing up databases in Roadsoft:

Via the Options button located on the Roadsoft User Login window.

Via the Database Manager, a separate utility installed with Roadsoft.

Via the Tools option in the Roadsoft main menu.

Option 1 – The Roadsoft User Login window

Open Roadsoft, but do not log in.

Select the Options button at the bottom left of the User Login window.

Select Backup Database.

Option 2 – The Database Manager Utility

Select the Start Menu on your computer.

Search for the MTU-CTT folder in the search bar.

Select the MTU-CTT folder.

Select the Roadsoft Database Manager Utility.

Select the Backup Roadsoft Database option.

Option 3 – The Tools Menu

Open Roadsoft and log in.

Select Tools from the main menu.

Select the Backup Roadsoft Database button.

After following the steps for one of the three methods for backing up a database, follow the steps below:

Select a server using one of the following methods:

Select a server from the Server combo box pull-down menu.

Enter a valid SQL Server Instance name (not case sensitive) into the Server combo box.

Select <Browse for more> from the Server combo box.

Select a database from the Database combo box.

Enter a name into the Name field.

Enter a description into the Description field.

Select the Folder button to the right of the Backup File field to choose a location for the backup. If a selection is not made the default backup location is selected.

Typically, the default location is:

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup

When making a backup to move the existing database to a new server, select a backup location on the server machine (it cannot be created on a network drive). Once the backup is created, copy the backup file to a network drive or flash drive.

CTT recommends that a folder is created on the server to house backup files. Name the folder with an obvious name like Roadsoft Backups or similar. Using this folder for backing up and restoring files will make it easier to always know where the files are located, and is also easier to navigate to during the backup/restore process. Roadsoft will remember the last location used for backing up and restoring.

Select the Create Backup button.

If a backup file with the same name already exists, the Overwrite Backup File window will appear. Select the Ok button to confirm backup overwrite.

Agencies running their own instance of SQL server (in other words, they’re not using SQL Express that is provided with Roadsoft) will need to know the sa password of their SQL instance to complete the backup. Contact your agency’s technical support staff or IT personnel to obtain this password if it has not already been provided.

Enter the sa credentials into the User ID and Password boxes (for agencies not running SQL Express that is included with Roadsoft).

Select TRY TDG Passwords if it is believed that SQL Server Express is installed (provided with the Roadsoft installer), not the enterprise version of the SQL server or any other paid version.
‎If you are unsure of the SQL instance your agency is running, you can usually check easily by the SQL instance name. The SQL Express provided with Roadsoft will install an SQL instance with TDG in the name (i.e. COMPUTERNAME\TDG2016).

Select the Ok button.

Move a Database

Roadsoft databases can be moved from server to server after being backed up. To back up a database, follow the instructions on how to Back Up a Database above.

Locate the backup file.

Right click the backup file.

Select Copy.

Go to the new storage destination.

Right click on the destination folder.

Select Paste.

If the file is copied to a shared network drive or flash drive, locate the backup copy and ensure it is saved on a physical drive on the server.

The backup file must be saved on one of the server’s physical drives. It cannot be restored from a network path or a flash drive.

To use the backed database, follow the instructions on how to Restore a Database.

Restore a Database

Restoring a Roadsoft database completely overwrites the current database with the selected backup if the existing database name is used.

As mentioned earlier, it is not recommended to use SQL Server Management Studio to restore a database. Tools are specifically developed in Roadsoft to provide this functionality.

There are two different ways to restore databases in Roadsoft:

The Options menu located within the Roadsoft User Login window.

The Database Manager, a separate utility installed with Roadsoft.

Option 1 – The User Login screen

Open Roadsoft, but do not log in.

Select the Options button at the bottom left of the User Login window.

Select the Restore Database option.

Option 2 – The Database Manager Utility

Select the Start Menu on your computer.

Search for the MTU-CTT folder in the search bar.

Select the MTU-CTT folder.

Select the Roadsoft Database Manager Utility.

Select the Restore Roadsoft Database option.

After following the steps for one of the options for restoring a database, follow the steps below:

Select a server using one of the following methods:

Select a server from the Server combo box pull-down menu.

Enter a valid SQL Server Instance name (not case sensitive) into the Server combo box.

Select <Browse for more> from the Server combo box.

Agencies running their own instance of SQL server (in other words, they’re not using SQL Express that is provided with Roadsoft) will need to know the sa password of their SQL instance to complete the backup. Contact your agency’s technical support staff or IT personnel to obtain this password if it has not already been provided.

Enter the sa credentials into the User ID and Password boxes (for agencies not running SQL Express that is included with Roadsoft).

Select TRY TDG Passwords if it is believed that SQL Server Express is installed (provided with the Roadsoft installer), not the enterprise version of the SQL server or any other paid version.
‎If you are unsure of the SQL instance your agency is running, you can usually check easily by the SQL instance name. The SQL Express provided with Roadsoft will install an SQL instance with TDG in the name (i.e. COMPUTERNAME\TDG2016).

Select the OK button.

Select a backup source to restore using one of the following methods:

To restore from a server backup:

Select the From Server radio button.

Select a backup from the Backups In Selected Device list.

To restore from a file backup:

Select the From File radio button.

Select a backup from a location on the server.

Browse for the backup file from physical drives on the server computer. Network shares will not be available for browsing.

Select the OK button.

Select or enter the name of the database in the Restore to Database combo box.

A new database is created by typing a new name in the Database field, use the existing database name to overwrite the database with the backup file, or use a different name to create a new Roadsoft database.

The database name must begin with “Roadsoft” or “RS” (ie – Roadsoft-agency or RS-agency).

Select the Restore button.

Login Warning – Profile Already in Use

By default, there is an “Admin” user profile in all Roadsoft databases. Tools are available for creating additional profiles in Roadsoft (see Add User Options & Manage Profiles for more information). Logging into a shared database with the same user profile is not recommended. If Roadsoft detects an occurrence of a duplicated login, a Login Warning window provides a list of user profiles that the database believes are currently logged in.

Additionally, when backing up a Roadsoft database, any user profiles actively logged into the database at the time of the backup are recorded in the backup. For agencies moving and/or restoring databases, this can cause some confusion when the database is restored from a different computer. The Login Warning window also appears in this case.

Handle this window using one of the following methods:

Select Clear User Profile(s) to permanently remove the shown user profiles from the list and continue logging into Roadsoft using the same login.

Only use this option if there is absolute certainty that the user profiles in the list are not currently logged into the database.

Select Continue Anyway to continue logging into Roadsoft using the same login.

The Login Warning window will continue to appear every time the duplicated profile(s) are used to login to the database until the profiles are cleared.

Select Cancel Login to stop logging into Roadsoft.