Backup Scheduling in SQL Server Express

Sep 20, 2019

Have you ever attempted to set up an automated backup of your SQL Server Express Edition and found that there’s no SQL Server Agent where you can schedule the job which will took a backup of your database.

Alas, the world does not end there and you don't need to pay extra bucks just to have the back up via an SQL Agent which is available only to Standard and Enterprise editions. There are many options to automate the backup job which runs on a specific time and does not require manual intervention.

Here, we will learn how to do it via SQL Command using batch file and Windows in-build Task Scheduler. Hope, you may find this useful.

  1. Create a BAT(batch) file to execute the command to take a backup of Database and save it.
echo off
:: --------------------------------------------------
:: clear console
cls
:: --------------------------------------------------
:: Define variables
set SERVERNAME=YOUR_SERVER_NAME
set DATABASENAME=DATABASE_NAME
set MyTime=%TIME: =0%
set MyDate=%DATE:~-4%.%DATE:~7,2%.%DATE:~4,2%.%MyTime:~0,2%.%MyTime:~3,2%.%MyTime:~6,2%
set FileName=%DATABASENAME%_%MyDate%.bak
set BAK_PATH=DIRECTORY_PATH
set DEST_FILE=%BAK_PATH%%FileName%
:: --------------------------------------------------
:: BACKUP Database
sqlcmd -E -S %SERVERNAME% -d master -Q "BACKUP DATABASE [%DATABASENAME%] TO DISK = N'%DEST_FILE%' WITH INIT , NOUNLOAD , NAME = N'%DATABASENAME% backup', NOSKIP , STATS = 10, NOFORMAT"
:: --------------------------------------------------
:: Optional Part
:: --------------------------------------------------
:: Zip file
7z a -tzip "%DEST_FILE%.zip" "%DEST_FILE%"
:: --------------------------------------------------
:: Delete unziped file
DEL "%DEST_FILE%"

 

“SERVERNAME” is the name of SQL Server physical machine.
“DATABASENAME” is the database which will be backup.
“FileName” sets as a database name and append date which has .bak extension 
“BAK_PATH” is the path in which a database backup file will be saved.
“DEST_FILE” is use backup path and file name.
After defining all the variables database backup will be generated and save as zip file in “DEST_FILE” path and at the end, the unzipped file will be deleted from “DEST_FILE”

  1. Now, it's time to schedule this created batch file in #1
  2. Start Menu -> Task Scheduler -> Run as administrator
  3. Click on Create Task... from the right bar and configure it with Triggers and Actions

 

Raviraj Patel

About the Author

Raviraj Patel

A results-driven, customer-focused, articulate and analytical Senior Software Engineer who can think “out of the box”. Strong in design and integration of problem-solving skills. Expert in C#, .Net Core, Web API, ASP.NET, jQuery, SQL Server, SSIS, SSRS.

Skilled in developing business plans, requirements specifications, user documentation and architectural systems research.