Schedule Database backup on SQL Server Express Edition

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.

“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