Monthly Archives: December 2019

How to create a regular backup job for SQL Server Express

Leave a reply

Having a regular backup of your database is vital. In SQL Server, there is a feature called “Agent services” that help you create a regular job to clean up the database, do some operations periodically or backup your database. But if you use SQL express, this feature is not available there. So how can you create a backup which runs in an interval during the day?

First, you need a backup script. I found one on the internet (sorry I forgot the source) and changed it a little bit.  Here is the script :

DECLARE @path VARCHAR(500)
DECLARE @name VARCHAR(500)
DECLARE @pathwithname VARCHAR(500)
DECLARE @time DATETIME
DECLARE @year VARCHAR(4)
DECLARE @month VARCHAR(2)
DECLARE @day VARCHAR(2)
DECLARE @hour VARCHAR(2)
DECLARE @minute VARCHAR(2)
DECLARE @second VARCHAR(2)

-- 1. Getting the time values

SELECT @time   = GETDATE()
SELECT @year   = (SELECT CONVERT(VARCHAR(4), DATEPART(yy, @time)))
SELECT @month  = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(mm,@time),'00')))
SELECT @day    = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(dd,@time),'00')))
SELECT @hour   = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(hh,@time),'00')))
SELECT @minute = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(mi,@time),'00')))
SELECT @second = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(ss,@time),'00')))

-- 2. Defining the filename format

SELECT @name ='Backup' + '_' + @year + @month + @day + @hour + @minute + @second

SET @pathwithname = @name + '.bak'

-- 3. Run the Back up script 

BACKUP DATABASE [DatabaseNameToBackUp] TO  DISK = @pathwithname WITH  RETAINDAYS = 10, NOFORMAT, NOINIT,  NAME = N'PodcastDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

What the script does, is that it creates a backup for a database you want, but for naming the back up file, it uses the current date time and appends them to the file name.

To run and see if it works, you can run the following command in CMD :

"\....\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\SQLCMD.EXE" -S server\sqlexpress -i "C:\backup.sql"

So the tool which we need here is “SQLCMD” and it is located under Tools\binn folder in the installation path of SQL server express. It requires two parameters, the SQL server instance, and the path of the script to run.

The next step would be run it automatically. Run the “Task Scheduler” application in windows and click on the “Create a basic Task”. Define a name for and and go to next step.

Now you need to setup the trigger condition, daily, weekly or etc.

When you reach to action step, choose “Start Program” and click next. In the dialog box, write the path to “SQLCMD” and in “Add Argument” section, write

-S server\sqlexpress -i "C:\backup.sql"

That is it !