How to Automate SQL Database Maintenance Tasks using SQLCMD

This article is about automating SQL database maintenance tasks through SQLCMD utility which lets you run T-SQL commands directly from the command prompt without using SSMS (SQL Server Management Studio).

Typically, automating database tasks requires SSMS (SQL Server Management Studio) for scheduling jobs that run these tasks, but in this article, an alternative approach is used to automate database tasks without having to use the much-needed SSMS.

The SQLCMD utility can be a real time saver for database developers and DBAs since they can immediately run the necessary SQL scripts from the command line, and automating database maintenance tasks with the SQLCMD utility is a plus.

Basics of Automating SQL Database Tasks

Let us go through some basic concepts for automating SQL database maintenance tasks with the SQLCMD utility.

What is a Database Maintenance Task?

A database maintenance task, as the name indicates, is a task which assists in managing or maintaining a database to ensure its smooth operation.

For example, creating database backups on daily basis is a database maintenance task which ensures that the database is backed up, so that in case of any incident, which ends up either corrupting the database or making it unstable, it can be restored successfully without losing any data.

Who Performs Database Maintenance Tasks?

Typically, professionals responsible for database system maintenance, such as database administrators, perform database maintenance tasks, however, this may change under certain circumstances.

How is a Database Maintenance Task done?

A database maintenance task is normally performed manually by a database administrator from time to time, or it can also be scheduled to run automatically.

What Does Automating a Database Maintenance Task Mean?

Automating a database maintenance task means scheduling a database maintenance task to run automatically without any manual intervention.

What are the benefits of Automating Database Maintenance Tasks?

Automating database maintenance tasks saves time and effort, helps to standardize the process and makes it easier to execute it on a regular basis, and timely alerts configured for an automated job failure can also speed up the process of identifying the cause of a problem if the task fails.

How can you Automate Database Maintenance Task?

One of the advanced uses of the SQLCMD utility is the automation of database maintenance tasks. We are going to automate database maintenance tasks without using SSMS (SQL Server Management Studio) in this article.

Windows Task Scheduler

We are going to take advantage of Windows Task Scheduler to automate database maintenance tasks which does not require SSMS (SQL Server Management Studio).

Prerequisites

This article assumes that you have basic knowledge of database maintenance tasks performed by running T-SQL statements along with a basic understanding of the SQLCMD utility.

Please refer to the article Basics of Running T-SQL Statements from Command Line using SQLCMD to get some solid understanding of SQLCMD basics, before proceeding to its advanced use.

Please also refer to the article Running SQL Database Maintenance Tasks Using SQLCMD to learn how to perform SQL database maintenance tasks.

This article also assumes that a sample University database has been created.

Please check another article Basics of Running T-SQL Statements from Command Line using SQLCMD to create a sample database or use the following T-SQL code to set up the sample database called University:

-- (1) Create University sample database
CREATE DATABASE University;
GO

USE University

-- (2) Create Course table
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_NAME='Course') 
DROP TABLE dbo.Course 

CREATE TABLE [dbo].[Course] (
    [CourseId] INT           IDENTITY (1, 1) NOT NULL,
    [Name]     VARCHAR (30)  NOT NULL,
    [Detail]   VARCHAR (200) NULL,
    CONSTRAINT [PK_Course] PRIMARY KEY CLUSTERED ([CourseId] ASC)
);

-- (3) Create Student table
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES T WHERE T.TABLE_NAME='Student') 
DROP TABLE dbo.Student 

CREATE TABLE [dbo].[Student] (
    [StudentId] INT           IDENTITY (1, 1) NOT NULL,
    [Name]      VARCHAR (30)  NULL,
    [Course]    VARCHAR (30)  NULL,
    [Marks]     INT           NULL,
    [ExamDate]  DATETIME2 (7) NULL,
    CONSTRAINT [PK_Student] PRIMARY KEY CLUSTERED ([StudentId] ASC)
);

-- (4) Populate Course table
SET IDENTITY_INSERT [dbo].[Course] ON
INSERT INTO [dbo].[Course] ([CourseId], [Name], [Detail]) VALUES (1, N'DevOps for Databases', N'This is about DevOps for Databases')
INSERT INTO [dbo].[Course] ([CourseId], [Name], [Detail]) VALUES (2, N'Power BI Fundamentals', N'This is about Power BI Fundamentals')
INSERT INTO [dbo].[Course] ([CourseId], [Name], [Detail]) VALUES (3, N'T-SQL Programming', N'About T-SQL Programming')
INSERT INTO [dbo].[Course] ([CourseId], [Name], [Detail]) VALUES (4, N'Tabular Data Modeling', N'This is about Tabular Data Modeling')
INSERT INTO [dbo].[Course] ([CourseId], [Name], [Detail]) VALUES (5, N'Analysis Services Fundamentals', N'This is about Analysis Services Fundamentals')
SET IDENTITY_INSERT [dbo].[Course] OFF

-- (5) Populate Student table
SET IDENTITY_INSERT [dbo].[Student] ON
INSERT INTO [dbo].[Student] ([StudentId], [Name], [Course], [Marks], [ExamDate]) VALUES (1, N'Asif', N'Database Management System', 80, N'2016-01-01 00:00:00')
INSERT INTO [dbo].[Student] ([StudentId], [Name], [Course], [Marks], [ExamDate]) VALUES (2, N'Peter', N'Database Management System', 85, N'2016-01-01 00:00:00')
INSERT INTO [dbo].[Student] ([StudentId], [Name], [Course], [Marks], [ExamDate]) VALUES (3, N'Sam', N'Database Management System', 85, N'2016-01-01 00:00:00')
INSERT INTO [dbo].[Student] ([StudentId], [Name], [Course], [Marks], [ExamDate]) VALUES (4, N'Adil', N'Database Management System', 85, N'2016-01-01 00:00:00')
INSERT INTO [dbo].[Student] ([StudentId], [Name], [Course], [Marks], [ExamDate]) VALUES (5, N'Naveed', N'Database Management System', 90, N'2016-01-01 00:00:00')
SET IDENTITY_INSERT [dbo].[Student] OFF
GO

Automating the Task of Backing up the University Database

Let’s take a look at the scenario where database backup needs to be automated.

Requirement: Automate the Backup of the University Database

A DBA was asked to automate database backups showing date and time of the backup to save time and avoid confusion over the latest backup and speed up the overall process.

To meet these business requirements, an SQLCMD script which creates database backup needs to be run in the Windows Task Scheduler.

Creating Folders for Database Backup and Script

First, create a folder named Demo on drive C and then create the following sub-folders in the Demo folder:

1. Backups
2. Scripts

Please note that we use drive C for demo purposes only, please use a backup drive in real time scenario.

Creating SQL Database Backup Script

Create SQL database backup script and save it as 01-Backup-University-sql-script.sql in C:\Demo\Scripts which also contains the information on the backup date and time as follows:

-- Creating University database backup with date and time 
DECLARE @Backup NVARCHAR(400)
SET @Backup=CONCAT('C:\Demo\Backups\University-',(FORMAT(GETDATE(),'yyyy-MM-dd-hh-mm-ss-tt')),'.bak')
BACKUP DATABASE [University] TO  DISK = @Backup
WITH NOFORMAT, NOINIT,  NAME = N'University-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Saving SQL Script in Scripts Folder

Save the SQL backup script in C:\Demo\Scripts folder.

Creating SQLCMD Backup Script

The database backup script referring to the SQL script using SQLCMD is created as follows:

Sqlcmd –S . –i c:\Demo\Scripts\01-Bakup-University-sql-script.sql

Where “.” (dot) is the default unnamed SQL instance here which can be replaced by a named SQL instance such as .\<InstanceName>.

Test Running SQLCMD Backup Script

Run the above SQLCMD script from the command line to test the backup script:

The script ran successfully, so we are good to go.

Go to the Backups folder and delete the backup which is created as a result of the test run.

Creating a batch file to reference the SQLCMD Script

Create a batch file with the name 02-Run-Sqlcmd-Backup-script.bat using Notepad and save it as .bat:

@Echo off
sqlcmd -E -S . -i c:\Demo\Scripts\01-Bakup-University-sql-script.sql

Please note that there is a slight change in the SQLCMD script:  -E is added, which is actually the default authentication option and does not need to be specified according to Microsoft documentation.

This is illustrated as follows:

Schedule Database Backup Task in Windows Task Scheduler

Open the Windows Task Scheduler by typing Task Scheduler in the Windows search and then click Task Scheduler:

Once the Windows Task Scheduler is opened, click Create Basic Task… from the options on the right and type Backup University Database Task in the Name input box and click Next:

Next, set up the frequency of the task by leaving the default settings, which is daily:

Set  the daily time at which this task will be activated (it is recommended to set  the time from two to three minutes plus your current time to instantly check that it’s working):

We also need to select Start a program when asked what do we want this task to perform:

Next, point to the batch file which calls SQLCMD to create a backup of the University database and click Next:

Click Finish to complete the process.

It is possible that you spent more time on what was set up in the job schedule to complete the job setup, which means that you have to wait for the next day to see if the task is running or not.

There is a workaroundб you need to reschedule the task again 2-3 minutes ahead of your current time to see it running in 2-3 minutes rather than waiting for the whole day:

Now wait for the task to run automatically and after the specified time, please check the backup folder we created in the Demo folder on drive C:

Congratulations, the database maintenance task has been successfully automated using the Windows Task Scheduler, which calls a batch file that runs the SQLCMD script to create a backup file.

Please delete the automated task which was created for demo purposes once you are done with the walkthrough and are not interested in running it further.

Things to Do

Now that you are ready to automate database maintenance tasks, such as backing up a database using the SQLCMD utility and Windows Task Scheduler after going through this article, you can improve your skills further by trying the following things:

1. Please try to set up and automate the backup of a sample database SQLBookShop mentioned in the article Simplifying Unit Testing Main Stored Procedure Which Also Calls a Utility Procedure.
2. Please try to create multiple databases and automate their backups using the SQLCMD utility with Windows Task Scheduler.
3. Please try to automate the backup and restore database processes by creating two batch files, one for backup and the other for restoring the database referring to SQLCMD scripts and scheduling the job in the Windows Task Scheduler to run daily.

Useful tool:

dbForge Studio for SQL Server – powerful IDE for SQL Server management, administration, development, data reporting and analysis.