Back Up and Restore the Database

On this page

The database backup tool (DbBackup.exe, titled Database Backup/Restore Wizard) makes a full backup of one SQL Server database or restores it from a backup file. It is installed with Database Tools. Scheduling, backup verification, retention, and differential or log backups require other tools.

Moving closed tickets into a separate archive database is a different task, described in Data Archiving.

Before you begin

  • You need sysadmin credentials on the SQL Server that hosts the database.
  • The tool refuses an Azure SQL server outright, with "Alloy Database Backup works only with SQL Server databases. Azure SQL is not supported."
  • Back up the database regularly. The tool has no schedule, and each run overwrites its backup file. For regular backups, use the backup tools of SQL Server itself, or run this tool from the command line with an external scheduler, as described in Run the tool from the command line. You can also back up and restore the database with SQL Server's own tools instead of this tool.

Open the database backup tool

No shortcut or menu entry leads to the tool. Run DbBackup.exe from the Bin folder under the Alloy Navigator installation folder in Program Files\Alloy Software\Alloy Navigator. If the tool is already running on that computer, running it again activates the existing window instead of opening a second one.

For each Windows user, the wizard remembers its window placement and up to ten SQL Server names used before, the last used first, and lists them in the SQL Server box above the entry <Browse for more....>.

Back up a database

IMPORTANT: Each run overwrites the target backup file. The tool cannot append a backup to an existing backup media set.

  1. On the Action to perform page, leave Backup selected (it is selected by default) and click Next.
  2. On the page headed "Specify the target server and choose the database to back up", under Login details, choose the SQL Server and the Authentication method: Windows Authentication (selected by default) or SQL Server Authentication, which enables Login and Password. Choose the Database to back up from the list, which does not include the SQL Server system databases. Next stays disabled until a server and a database are named, and a login too under SQL Server authentication.
  3. On Backup location, choose a backup file on the SQL Server host. Use the ellipsis button to browse the server's folders in Select File Location. If the file already exists, confirm whether to replace it.
  4. The Ready to backup page names the database, the server, and the file. Click Next to start the backup.
  5. The Backup Completed page reads "The backup of database '<name>' has completed successfully." Click Finish to close the wizard.

The backup runs with no time limit. On a SQL Server edition that supports it (Enterprise, Enterprise Core, Enterprise Evaluation, Business Intelligence, Developer, or Standard), the backup is compressed; on any other edition it is written uncompressed, with no message.

Backup file location

The path is on the SQL Server host, even when you run the wizard on another computer. Select File Location shows that server's drives and folders and a File name box.

When the file box is empty, the wizard proposes the database's newest backup file if one exists. Otherwise, it uses the server's default backup folder with <database>.bkp as the file name. If the server reports no default folder, it uses the folder stored for your Windows user. If the server's setting cannot be read, it uses the SQL Server data root folder and stores that choice for your Windows user. A relative path is completed with the same folder.

For an existing file, the confirmation reads "File '<path>' already exists." and "Do you want to replace it?".

Restore a database

IMPORTANT: Restoring to an existing database name replaces that database after confirmation. The wizard does not close user sessions or check for open database connections before restoring.

  1. On the Action to perform page, select Restore and click Next.
  2. On the page headed "Specify the target server and database", choose the SQL Server and Authentication as for a backup. The Database box is editable, so you can restore into a name no database carries yet. If you name an existing database, the wizard shows "Database '<name>' already exists." and asks "Do you want to replace it?" before it goes on.
  3. On the Restore location page, select the backup file to restore from. As with a backup, the file is always on the SQL Server host machine, a path is proposed the same way when the file box is empty, a relative path you type is completed with the same folder, and the ellipsis button opens the Select File Location window. To choose where the restored database's files are placed, select Change physical files location for database to restore.
  4. If you selected that check box, review each file's Original File Name from Backup File and Restore As path on Restore options. Edit each target path, browse with its ellipsis button, or use Set Default Paths. Confirm any file replacements before continuing.
  5. The Ready to restore page names the database, the server, and the file, followed by where each database file will be placed. Click Next to start the restore.
  6. The Restore Completed page reads "Database '<name>' has been restored successfully." Click Finish to close the wizard.

A restore always relocates every file: to the paths set on the Restore options page, or to the paths the backup itself names when you leave the check box cleared and skip that page. A restore always brings the database online; there is no option to leave it in a non-recovered state.

If a backup or restore fails, the wizard shows the error, prefixed "SQL server reply: " when SQL Server raised it, and stays on the page where it was.

Restore file paths

Set Default Paths uses the server's default data directory for data files and its default log directory for log files, keeping each file's name. If no default data directory is set, data files go to Data under the SQL Server data root. If no default log directory is set, log files go to the data directory.

An empty Restore As box is refused with "The field 'Restore As' cannot be empty." For each existing file, the wizard shows "File '<path>' already exists." and asks "Do you want to replace it?" Choose Yes, Yes to All, or No; No returns to Restore options.

Move the database to another SQL Server

To move the database to another SQL Server, back it up from the original server and restore the copy on the new one. The tool reads and writes backup files only on the SQL Server computer it is connected to, so you copy the file between the two computers yourself. The new server must be a SQL Server: the tool refuses Azure SQL.

  1. Back up the database with the tool connected to the original SQL Server, as described in Back up a database.
  2. Copy the backup file from the original SQL Server computer to a folder on the new SQL Server computer.
  3. Restore the database with the tool connected to the new SQL Server, selecting the copied file on the Restore location page, as described in Restore a database.
  4. Update the license. The license is bound to one or more SQL Server names; unless its SQL Server name is Unlimited or already includes the new server, the license check fails on the new SQL Server. See License Management.

Run the tool from the command line

You can run DbBackup.exe with command-line parameters for an unattended backup or restore. The wizard window is not shown; the backup or restore runs with the values you pass:

  • The mode switch, /Backup or /Restore, is required.
  • /Server= names the SQL Server and /Database= the database. /DB= is also accepted and takes precedence over /Database=.
  • /WinAuth=True|False chooses the authentication. Only the values TRUE and YES turn on Windows authentication, as does a bare /WinAuth or /NTAUTH; any other value, or neither switch, means SQL Server authentication.
  • /User= and /Password= give the SQL Server login and are read only under SQL Server authentication.
  • /File= takes the full path of the backup file on the SQL Server.

Switches are matched case-insensitively and by prefix. A single /H, /?, or /help argument opens a window captioned Command Line Parameters that lists the mode switch and the /Server=, /Database=, /WinAuth=, /User=, /Password=, and /File= parameters with a worked example, instead of running the wizard; press F1 in that window to open the tool's online help. The help switch is the one exception to case-insensitive matching: /HELP typed in all capitals opens the wizard instead.

DbBackup.exe is not an Automation Server job executable and does not take the command-line options of one. A fully unattended backup or restore needs an external scheduler, such as Windows Task Scheduler, to invoke it.