How to automate database backup for Microsoft SQL Server Express and Desktop Edition

Install first Microsoft SQL server express edition 2019 for example along with management studio. See my below writeup to learn how to install that.

Trend Micro ApexOne installation with SQL Express db on a single server

Once the database is installed, access the database either using Administrator account or sa account from management studio.

Now it needs 4 steps to complete the whole process:

1. Create a sample Database.

2. Create a .SQL file for backup the database

3. Create a .BAT file for run the .SQL file that created in step 2.

4. Create a task scheduler to run the .BAT file with the time schedule.

So let’s start the 1st step.

You need a database for this to work right? So, lets create a dummy database first.

You can create database using gui but we have sample query code that will help you to create the db in more shortest time. Click on New Query and paste the below code then run the query.

USE [master]
GO

CREATE DATABASE [SQLTestDB]
GO

USE [SQLTestDB]
GO
CREATE TABLE SQLTest
   (
      ID INT NOT NULL PRIMARY KEY,
      c1 VARCHAR(100) NOT NULL,
      dt1 DATETIME NOT NULL DEFAULT getdate()
   );
GO

USE [SQLTestDB]
GO

INSERT INTO SQLTest (ID, c1) VALUES (1, 'test1')
INSERT INTO SQLTest (ID, c1) VALUES (2, 'test2')
INSERT INTO SQLTest (ID, c1) VALUES (3, 'test3')
INSERT INTO SQLTest (ID, c1) VALUES (4, 'test4')
INSERT INTO SQLTest (ID, c1) VALUES (5, 'test5')
GO

SELECT * FROM SQLTest
GO

After running the query, a database called SQLTest will be created.

2. Now let’s create the .SQL file to backup the database. We can just manually create this using the following command in New Query section.

BACKUP DATABASE SQLTestDB TO DISK='c:\backup\SQLTestDB.bak'
GO

But we need a automate way to create the database backup. So create the .sql file using following code:

DECLARE @DatabaseName NVARCHAR(100)
DECLARE @BackupPath NVARCHAR(500)
DECLARE @BackupFileName NVARCHAR(500)
DECLARE @DateTime NVARCHAR(20)
DECLARE @SQLCommand NVARCHAR(1000)

-- Set the database name
SET @DatabaseName = 'SQLTestDB' -- Replace 'YourDatabaseName' with the name of your database

-- Set the backup path
SET @BackupPath = 'C:\Backup\' -- Change this to your desired backup path

-- Get the current date and time for the backup file name
SET @DateTime = REPLACE(CONVERT(NVARCHAR(20), GETDATE(), 120), ':', '')

-- Set the backup file name
SET @BackupFileName = @BackupPath + @DatabaseName + '_' + @DateTime + '.bak'

-- Set the backup command
SET @SQLCommand = 'BACKUP DATABASE [' + @DatabaseName + '] TO DISK = ''' + @BackupFileName + ''''

-- Execute the backup command
EXEC(@SQLCommand)

Note: C:\Backup\ this location must exist prior taking the backup.

Now save this file as for example, C:\utility\MSSQL_Backup.SQL

3. Now we need to create a batch file to run the above created .SQL file.

OSQL -U sa -P Admin2123$ -S localhost\PRIMAVERA -i "C:\utility\MSSQL_Backup.SQL"

PRIMAVERA is the database instance name.

4. Now configure the schedule task to run this batch file.

Open the task scheduler application. Click on Create Task.

Self explanatory

Self explanatory

Browse the location where your batch file is located.

Now automatically backup will be created just like the above screenshot.

Thanks to my colleague Mohammad Alamin for this wonderful work.

I hope i explained easily the whole process. Feel free to ask me if you have any confusion. Please subscribe below if you find this useful.

LinkedIn:

https://www.linkedin.com/in/md-mahimbin-firoj-7b8a5a113/

YouTube:

https://www.youtube.com/@mahimfiroj1802/videos