Microsoft SQL Server Back Up and Restore the Microsoft SQL Server Databases

Backing up your Sentinel Visualizer databases is very important for protecting your data. Just like you would backup and relocate your Word, Excel, and other files in case of a hard drive crash, you should backup and store copies of your Sentinel Visualizer databases for safekeeping.

Another benefit of backing up your databases is that if a data-import fails, you can restore your database, make changes to the data-import map (svi3 file), and reimport the data.

Additionally, you can create a Master database with all of your customized Entity Types, Relationship Types, and Metadata Types. You can then use a backup of that to create (via the Restore command) new databases so that all of your customizations are already defined in every new database. This can be a significant time-saver!

Microsoft SQL Server Management Studio Sentinel Visualizer uses Microsoft's SQL Server as its database technology. The best way to backup and restore Sentinel Visualizer databases is to use Microsoft SQL Server Management Studio (SSMS).

Users can download the Microsoft SQL Server Management Studio (SSMS) for free on the Microsoft website.


Backup Database

  1. Open Microsoft SQL Server Management Studio.
  2. Connect to the local Sentinel Visualizer instance of SQL Server. The Sentinel Visualizer instance is called (local)\SENT4EXPRESS.
    Login information:
    • Username: sa
    • Password: S3ntinElL0gin:7@FMS
    SQL Server Login

  3. Right-click on the database you wish to backup. Select [Tasks] and then [Back Up...]. SQL Server Database Backup

  4. Specify the destination for the backup. The screen shot below shows the default backup location.
    (C:\Program Files\Microsoft SQL Server\MSSQL13.SENT4EXPRESS\MSSQL\Backup\FraudSample.bak)
    Please make note of the location (folder) specified on your computer for future reference. SQL Server Backup Database Location

Restore Database

  1. Open Microsoft SQL Server Management Studio.
  2. Connect to the local Sentinel Visualizer instance of SQL Server. The Sentinel Visualizer instance is called (local)\SENT4EXPRESS.
    Login information:
    • Username: sa
    • Password: S3ntinElL0gin:7@FMS
    SQL Server Login

  3. Right-click on [Databases] and select [New Database...]. SQL Server New Database

  4. Type the Database name and click [OK]. SQL Server Database Name

  5. Right-click on a newly created Database, and select [Tasks], [Restore] and then [Database...]. SQL Server Restore

  6. Make sure that the 'Source - Database' is set to your new database name. SQL Server Source

  7. Next, select the [Device] button , and click [...]. SQL Server Restore Device

  8. The 'Select backup devices' form will appear, click the [Add] button. SQL Server Restore Backup Device

  9. Select the backup file (.bak) and click [OK]. SQL Server Restore Locate File

  10. Verify that the 'Restore' box is checked. SQL Server Restore Backup

  11. In the left side of the 'Restore Database' window, select [Options]. SQL Server Restore Options

  12. Under the 'Restore Options' section, check the 'Overwrite the existing database (WITH REPLACE)' box, then click [OK]. SQL Server Restore Overwrite

  13. Launch Sentinel Visualizer and connect to the restored database by choosing [Open Database]. Open Database

  14. The database should now appear in your list of databases. List of Databases
    Note: If the database does not appear, then click [Add Database] and then [List Databases] to locate the desired database. My list of Databases

This an alternative backup and restore command that does not rely on Microsoft SQL Server Management Studio and is not subject to your version of Microsoft SQL Server.

Use this approach if you:

  • Have not installed Microsoft SQL Server Management Studio.
  • Are unable to backup a database.
  • Are unable to restore a database (either as a new database or to overwrite an existing one).

Step 1: Create Two Folders

In our examples, we use a folder called "C:\SVbackup" to store the command files that we downloaded (from the FMS website - see Step 2) and to store the backed-up files. We also use a folder called "C:\SVFiles" to store the restored database files.

Step 2: Download and Uncompress the FMS Command Files

Download this Backup.zip file (only 2K) to the folder you created in Step 1 and decompress it. The zip file contains these tiny files:

  • backup.cmd
  • restore.cmd
  • listfiles.cmd
  • readme.txt
  • ex-backup.bat
  • ex-restore.bat

Step 3: Create and Run Batch Files to Backup a Database

Backup.cmd is the command for backing up a SQL Server server database.

Syntax

backup.cmd DatabaseName DestinationFile

where

  • DatabaseName is the name of the existing SQL Server database.
  • DestinationFile is the path and name of the backup file. The convention is to use bak as the file extension.

Example

backup.cmd DatabaseName c:\SVbackup\DatabaseName.bak

Use a Batch File

A batch (*.BAT) file is a text file that lets you specify the command so you can run it over and over again without having to type it each time.

An example of using the backup.cmd command is in the ex-backup.bat batch file. Copy then edit it for your file names. From Windows Explorer, right click on the file and select Edit to edit it.

If you edit or create a BAT file, make sure that you save the file as a BAT file type otherwise, it will save as a TXT file and the batch command will no longer work.

Make a separate batch file for each database you want to backup or create one batch file that handles multiple databases. From Windows Explorer, you can double click the batch file to run it.

Here's an example of what appears when backup.cmd runs:

C:\SQLEXPRESSBACKUP>backup.cmd NameOfDatabase c:\SVbackup\NameOfDatabase.bak

C:\SQLEXPRESSBACKUP>REM SQL Server Instance Name and Login

C:\SQLEXPRESSBACKUP>set sqlserverinstance=SENT4EXPRESS

C:\SQLEXPRESSBACKUP>set SQLlogin=S3ntinElL0gin:7@FMS

C:\SQLEXPRESSBACKUP>sqlcmd -U sa -P S3ntinElL0gin:7@FMS -S .\SENT4EXPRESS -Q "BACK
UP DATABASE NameOfDatabase TO DISK='c:\SVbackup\NameOfDatabase.bak' WITH INIT"
Processed 288 pages for database 'NameOfDatabase', file 'NameOfDatabase' on file 1.
Processed 2 pages for database 'NameOfDatabase', file 'NameOfDatabase_log' on file 1.
BACKUP DATABASE successfully processed 290 pages in 0.273 seconds (8.298 MB/sec)

Look for the "BACKUP DATABASE successfully processed" message.

For disaster recovery, copy the BAK file to another location in case your hard drive crashes. The BAK file can also be used to restore the database on another computer. We also recommend that you backup your database prior to performing a data-import so you can undo the import later should you find the imported data to be unacceptable.

Step 4: Restore a Database from the Backup

Restore.cmd is the command for restoring a SQL Server server database. It has this syntax:

restore.cmd BackupFile DatabaseName NewDatabaseName DatabaseFolder

where

  • BackupFile is the path and name of the backup file (*.BAK) from which you are restoring.
  • DatabaseName is the name of the database to which you are restoring the BAK file.
  • NewDatabaseName is the name that you want to call the restored database. If you're overwriting the existing database, then this is the same as DatabaseName. (Careful: the existing database will be overwritten without warning.) If you are creating a new database, then type a new name of a database.
  • DatabaseFolder is the folder where the new database is saved. Do not include a trailing slash.

Example

restore.cmd c:\SVbackup\BackupFile.bak DatabaseName NewDatabaseName c:\SVFiles

Use a Batch File

A batch (*.BAT) file is a text file that lets you specify the command so you can run it over and over again without having to type it each time.

An example of using the restore.cmd command is in the ex-restore.bat batch file. Copy then edit it for your file names. From Windows Explorer, right click on the file and select Edit to edit it.

If you edit or create a BAT file, make sure that you save the file as a BAT file type otherwise, it will save as a TXT file and the batch command will no longer work.

Make a separate batch file for each database you want to restore or create one batch file that handles multiple databases. From Windows Explorer, you can double click the batch file to run it.

Here's an example of what appears when restore.cmd runs:

C:\SQLEXPRESSBACKUP>restore.cmd c:\SVbackup\NameOfDatabase.bak MyDatabase MyNewDatabase c:\SVFiles

C:\SQLEXPRESSBACKUP>REM SQL Server Instance Name and Login

C:\SQLEXPRESSBACKUP>set SQLinstance=SENT4EXPRESS

C:\SQLEXPRESSBACKUP>set SQLlogin=S3ntinElL0gin:7@FMS

C:\SQLEXPRESSBACKUP>sqlcmd -U sa -P S3ntinElL0gin:7@FMS -S .\SENT4EXPRESS -Q "REST
ORE DATABASE MyNewDatabase FROM DISK='c:\SVbackup\NameOfDatabase.bak' with move 'NameOfDatabase' to 'c:\SVFiles
\MyNewDatabase.mdf', move 'MyDatabase_log' to 'c:\SVFiles\MyNewDatabase.ldf'"
Processed 288 pages for database 'MyNewDatabase', file 'MyDatabase' on file 1.
Processed 2 pages for database 'MyNewDatabase', file 'MyDatabase_log' on file 1.
RESTORE DATABASE successfully processed 290 pages in 0.188 seconds (12.051 MB/sec).

Look for the "RESTORE DATABASE successfully processed" message. The database should now be accessible within the Sentinel Visualizer program:

  • Open Database
  • Select Other
  • List Databases

The restored database should appear in the list.

Microsoft Certified Partner

Microsoft Partner Netework

Partners Welcome

Do you provide services to government and commercial customers seeking analytic solutions?
Contact us

In-Q-Tel Portfolio Company

InQTel Portfolio Company
Learn more

Discover

Sentinel Visualizer Comparison to IBM's i2 Analyst's Notebook