Introduction

The sqlcmd is a very powerful tool to run SQL Server scripts, T-SQL commands. It is also very useful in disaster recovery situations like restoring the master database.

The sqlcmd can also be used in the Cloud, specifically with SQL Server Azure. In this new article, we will describe how to connect from a local machine to an Azure Virtual Machine (VM) with SQL Server Installed using sqlcmd.

Requirements

You can watch the requirements configured step by step in my article related to migrate a database to Azure.

• An Azure VM with SQL Server installed.
• The port 1433 enabled if the default instance is used or the port of the instance used.
• The Endpoint enabled for the Azure VM.
• The remote subnet enabled for our machine.
• Enable the SQL authentication and create a database user.
• The Adventureworks database installed.

Getting started

How to connect to an Azure Machine with sqlcmd

1. First of all, connect to an Azure VM with SQL Server. To do this, go to the command line and write this:

   sqlcmd -U daniel -S sqlshack.cloudapp.net -P ”yourpassword” -d master

   The command runs the sqlcmd executable, with the SQL user Daniel, with the SQL Servername sqlshack.cloudapp.net with the password of your user and connecting to the system database master.

How to create a database in Azure with sqlcmd

1. Now let’s start creating a simple database. You can do that by using the create database command.
   1> create database sqlshack
   2> go
   You can verify the database created using the SQL Server Management Studio.

   
   Figure 1. The sqlshack database installed.

2. Alternatively, you could verify using the sp_databases stored procedure.
   1> sp_databases
   2> go

How to run a script using the command line locally to Azure

1. Another very useful parameter is the –i which is the input parameter. We can run sql server scripts using this parameter. In order to test it, let’s quit the sqlcmd.
   1> quit

2. Now, we will create a script called backupsqlshack.sql with this content.

   1 2 3 4 5 6 7

   BACKUP DATABASE [sqlshack] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\sqlshack.bak' WITH NOFORMAT, NOINIT,  NAME = N'sqlshack-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,   STATS = 10 GO

3. Run the scripts with the following command line:

   sqlcmd -U daniel -S sqlshack.cloudapp.net -P ”yourpassword” -d master -i “c:/scripts/backupsqlshack.sql”

   The command line with execute the sqlcmd with the Azure credential for the SQL database and run the backupsqlshack.sql script which will create a backup for the sqlshack database. You may need to modify the path of the backupsqlshack.sql and the patch of the backup in the .sql file.

4. In order to verify that the backup of the VM Azure machine was created, connect to your Azure VM.

   
   Figure 2. Connecting to an Azure VM

5. Look for the path of the .bak file of the path specified in step 2.

   
   Figure 3. The backup created on the VM

How to backup with a double click

1. We learned how to create a backup en the section above. Now let’s create a batch file in the Windows Desktop to run the sqlcmd commands.

2. In .bat file named automaticbackup.bat write this:

   sqlcmd -U daniel -S sqlshack.cloudapp.net -P “yourpassword” -d master -i “c:/scripts/backupsqlshack.sql”

   I am assuming that you already have the backupsqlshack.sql script created on the step 2 of the How to run a script using the command line locally to Azure section.

3. Save the automaticbackup.bat file in your local machine desktop and double click on it.

   
   Figure 4. The batch file to automatically backup a SQL Server in an Azure VM

How to save your T-SQL results in an output file using your local sqlcmd to Azure

1. The –o parameter helps you to store the output in a file. The following example shows you how to check the fragmentation percentage of a file. For this example, we need 2 things. The database ID, the object ID. I will use a table from the Adventureworks Database which contains some index and data already created. Alternatively, you can use your own tables with indexes if you already have some created.

2. First of all, create a script named fragmentationpercentage.sql that retrieves the information of your percentage of fragmentation:

   1 2 3 4 5 6 7 8 9 10 11 12

   DECLARE @db_id SMALLINT; DECLARE @object_id INT; set @db_id= DB_ID(N'AdventureWorks2014'); SET @object_id = OBJECT_ID(N'AdventureWorks2014.Person.Address');           SELECT index_id,avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED');   GO

   The sys.dm_db_index_physical_stats function requires two values:

   • The Database ID, which can be retrieved using the DB_ID() function. This function gives you the ID of a specified database name. In this example, the Adventureworks2014 database.

   • The OBJECT_ID() functions returns de ID of an specified object. In this example, the Person.Address table is the object.

   The next step is to use the function specified the database id and object id.

3. Once we have all ready, let’s invoke the script and store the results in a file name results.txt:

   sqlcmd -U daniel -S sqlshack.cloudapp.net -P “yourpassword” -d master -i “c:\scripts\fragmentationpercentage.sql” -o “c: \scripts\results.txt”

   The command line connects to Azure and executes the fragmentationpercentage.sql script and stores the results in the results.txt file. This file is stored locally.

4. In order to verify the file, in your local machine go to the path used in the step 3 (in my example c:\scripts

   

5. Open the file and verify the results

   

6. As you can see, it is very easy to store results periodically from the azure machine in our local machine.

Restrictions

There are some limitations in SQL Azure. For example, the maximum number of Databases in Azure is 150 per server.

In addition, there is a limitation per size according to the Database Edition. For example, the Basic Tier is limited to 2 GB, the standard tier 250 GB and the premium tier 500 GB.

For more information about the different limitations in Azure, verify the references the Azure SQL Database General Guidelines and Limitations and the Service tier.

Conclusion

In this article, we saw how to work connect to an Azure VM with SQL Server installed using sqlcmd.

We also learned how to create a database in Azure using sqlcmd and how to run a script. Specifically we run a backup script. We also learned how to execute the commands in a batch file.

The other example was how to get the fragmentation information of an Azure table in a local file.

As you can see, the process to automate Azure tasks with sqlcmd is very simple and easy. If you have more questions, ask in the comments.

• Author
• Recent Posts

Daniel Calbimonte
Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases.

He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.

He also helps with translating SQLShack articles to Spanish

View all posts by Daniel Calbimonte

Latest posts by Daniel Calbimonte (see all)
• PostgreSQL tutorial to create a user - November 12, 2023
• PostgreSQL Tutorial for beginners - April 6, 2023
• PSQL stored procedures overview and examples - February 14, 2023

Related posts:

1. How to work migrate backups, files and scripts to/from the cloud using the command line
2. Working with the SQL Server command line (sqlcmd)
3. How to automate Azure Active Directory (AAD) tasks using the Cloud Shell
4. How to work handle SQL Azure Databases with PowerShell
5. Introduction to mssql-cli, a new interactive command line tool for SQL Server