How to load SQL Server databases from the command line

In some cases, NavView users will use SQL Express for their data archiving needs. When using SQL Express, database management remains easiest using sql server management studio, available from Microsoft:

The question has been asked more than once how one can attach database files to a SQL Server instance (local db or sqlexpress) without having SSMS available. This is often the case if it is determined that this needs to be done, however the vessel internet does not allow for the downloading of SSMS. The following steps show how to attach database files to a SQL Server instance using the SQL Server command line tools from Microsoft, which are a small download (approximately 2mb).

This guide assumes that you have a correctly installed version of NavView of at least version 3.4. Correctly installed means that the prerequisites for installation have been included, so that SQL Server localdb has been installed.

1. Make sure that sql server command line tools are installed. The tool can be downloaded from the Microsoft website: (link is current at the time of publishing this article - however you may want to search for the tool in case there is a newer version available).

2. SQL Databases consist of two files the .mdf file (main database file), and the .ldf file (transaction log). Both files are needed.

3. Typically if using localdb with NavView, the database files are stored in the project folder ([Project Folder]\Shared\Data\Database. This is the default, however the files could be stored in another location if selected in the configuration. It is important to place the .mdf/.ldf files in the location where NavView will look for them, otherwise NavView will create new empty database files instead of using the provided data. If using SQLExpress, this is not a concern and the files can be placed anywhere that is convenient.

The following commands can be run from a bat file to attach a databsase to a localdb server. Items in all caps such as MYDBNAME and PROJECTNAME  and DBFILENAME will need to be changed to the correct names for each use.

@echo off

set "databaseName=MYDBNAME"
set "localDbInstance=(localdb)\v11.0"
set "mdfFilePath=C:\users\public\documents\4d nav\navview\PROJECTNAME\shared\data\database\DBFILENAME.mdf"
set "ldfFilePath=C:\users\public\documents\4d nav\navview\PROJECTNAME\shared\data\database\DBFILENAME.ldf"

REM Drop the existing database if it exists
sqlcmd -S %localDbInstance% -d master -Q "IF EXISTS (SELECT name FROM sys.databases WHERE name = '%databaseName%') DROP DATABASE [%databaseName%];"

REM Attach the new database
sqlcmd -S %localDbInstance% -d master -Q "CREATE DATABASE [%databaseName%] ON (FILENAME = '%mdfFilePath%'), (FILENAME = '%ldfFilePath%') FOR ATTACH;"

REM Verify by listing tables in the attached database
sqlcmd -S %localDbInstance% -d %databaseName% -Q "SELECT name FROM sys.tables;"

4.  Go into NavView, in Database services section, go to the Project Databases page, and click the + button in the On Demand Databases section or the Archive Databases section, depending on the case.

5. A dialog will open with a search button, and the option to filter to project name. If you set the database name to something that is not prefixed with this project's name, then leave that option unclicked, and perform the search. All databases currently listed in the localdb server will be shown. Use the checkboxes to select which dbs to add to the project. Once added to the project, the data from the added file is available for query or other uses in NavView.




Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request


Please sign in to leave a comment.
Powered by Zendesk