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).
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:
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:
- 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.
- Please try to create multiple databases and automate their backups using the SQLCMD utility with Windows Task Scheduler.
- 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.
dbForge Studio for SQL Server – powerful IDE for SQL Server management, administration, development, data reporting and analysis.