Updating a Microsoft SQL Database from Command Line
Check out this article and learn how to update your Microsoft SQL Database using the Command Line.
This article describes the procedure for updating a Microsoft SQL database from command line, using the sqlcmd utility. This procedure is useful when Microsoft SQL Server Management Tool is not available.
The sqlcmd utility can be used to:
For more information about the sqlcmd utility, please follow this link: sqlcmd Utility.
For more information about the Transact-SQL statements, please follow this link: Transact-SQL Statements.
Using sqlcmd utility to run SQL statements from command line
Starting the sqlcmd
When installing the Microsoft SQL Server 2005 or newer, the sqlcmd utility is installed and set as an environment variable in Windows. This way, the utility is accessible in command line from any location.
To update a WEBfactory 2010 SQL database, the sqlcmd utility must be started and connected to the desired SQL server, instance and database, using the appropriate user name and password:
sqlcmd -S <ServerName>\<InstanceName> -d <DatabaseName> -U <UserName> -P <Password>
where
Syntax | Description |
|---|---|
<ServerName> | The name of the SQL server. It can be either the localhost or a remote SQL server. |
<InstanceName> | The name of the SQL instance that needs to be addressed. By default, when installing the SQL Server using the WEBfactory 2010 CD, the instance is set to WEBFACTORY2010. If a different SQL instance name was specified, use it here. |
<DatabaseName> | The name of the database that needs to be updated. |
<UserName> | The SQL user name. By default, when installing the SQL Server using the WEBfactory 2010 CD, the SQL user name is set to "sa". If a different user name was specified, use it here. |
<Password> | The SQL password. By default, when installing the SQL Server using the WEBfactory 2010 CD, the SQL password is set to "webfactory". If a different user name was specified, use it here. |
Example:
sqlcmd -S TEST-PC\WEBFACTORY2010 -d DemoProject -U sa -P webfactory
The SQL statements
After the sqlcmd has successfully connected to the desired server/instance/database, the Transact-SQL statements can be entered. The sqlcmd lines are marked with the line number and the > sign:
1> example statement 2> example statement 3> example statement
The entered Transact-SQL statements are stored and will be executed only when the GO command is entered.
Using sqlcmd utility to run SQL scripts
Creating the SQL scripts
The SQL scripts can be created using any text editor (ex. Notepad). Write the needed SQL statements in the text editor and save the file using the .sql extension.
Running the SQL scripts
If applying the WEBfactory Service Packs/Update Packs on a machine where the Microsoft SQL Server Management Tool is not available, the following method can be used to run the Service Pack/Update Pack SQL script.
To run an SQL script using the sqlcmd utility, call the sqlcmd utility with the required options to connect to the desired server, instance and database and with the additional input option -i. The -i option represents the input script that needs to run:
sqlcmd -S <ServerName>\<InstanceName> -d <DatabaseName> -U <UserName> -P <Password> -i "<PathToScript>"
where
Syntax | Description |
|---|---|
<ServerName> | The name of the SQL server. It can be either the localhost or a remote SQL server. |
<InstanceName> | The name of the SQL instance that needs to be addressed. By default, when installing the SQL Server using the WEBfactory 2010 CD, the instance is set to WEBFACTORY2010. If a different SQL instance name was specified, use it here. |
<DatabaseName> | The name of the database that needs to be updated. |
<UserName> | The SQL user name. By default, when installing the SQL Server using the WEBfactory 2010 CD, the SQL user name is set to "sa". If a different user name was specified, use it here. |
<Password> | The SQL password. By default, when installing the SQL Server using the WEBfactory 2010 CD, the SQL password is set to "webfactory". If a different user name was specified, use it here. |
<PathToScript> | The path to the SQL script that needs to run on the database. If the path has a space in it and the double quotes are omitted, the script will not run. |
Example:
sqlcmd -S TEST-PC\WEBFACTORY2010 -d DemoProject -U sa -P webfactory -i "C:\TestScript.sql"