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.
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.
How to connect to an Azure Machine with sqlcmd
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
Now let’s start creating a simple database. You can do that by using the create database command.
1> create database sqlshack
You can verify the database created using the SQL Server Management Studio.
Figure 1. The sqlshack database installed.
Alternatively, you could verify using the sp_databases stored procedure.
How to run a script using the command line locally to Azure
- 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.
Now, we will create a script called backupsqlshack.sql with this content.12345BACKUP 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 = 10GO
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.
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
Look for the path of the .bak file of the path specified in step 2.
Figure 3. The backup created on the VM
- 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.
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.
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
- 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.
First of all, create a script named fragmentationpercentage.sql that retrieves the information of your percentage of fragmentation:12345678910DECLARE @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.
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.
In order to verify the file, in your local machine go to the path used in the step 3 (in my example c:\scripts
Open the file and verify the results
- As you can see, it is very easy to store results periodically from the azure machine in our local machine.
- Functions and stored procedures comparisons in SQL Server - February 20, 2017
- Functions vs stored procedures in SQL Server - February 14, 2017
- How to generate random SQL Server test data using T-SQL - January 26, 2017
How to backup with a double click
How to save your T-SQL results in an output file using your local sqlcmd to Azure
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.
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.
For more information, refer to these links:
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 is also a writer for SQL Server training material for certification exams.
View all posts by Daniel Calbimonte