Automating SQL Server Backups With Windows Task Scheduler

SQL Server Standard and Enterprise editions come with their own version of Task Scheduler called Maintenance Plans. However, this feature is not available for SQL Server Express Edition. If you want to automate the backups, you will need to use Windows Task Scheduler instead.

Step 1 => Create the backup script
You may write a simple backup script, such as:

Use master;
BACKUP DATABASE company1 
TO DISK = 'C:\sql_server_backups\COMPANY1_FULL_BACKUP.bak'
WITH CHECKSUM;

Or you may have built a procedure to take the backups, such as the one found here.
Whatever the code you choose to run, save it as a file with the .sql extension. In this case, I have saved it as “DailyFullBackup.sql”

Step 2 => Create a batch file with the following syntax:
sqlcmd -S your_server_name\your_instance_name -i your_sql_script -o your_output_file
In our example, it can be:
sqlcmd -S PRODSVR\COMPANY_DB -i DailyFullBackup.sql -o DailyFullBackup_log.txt

Save the batch file with the .bat extension (DailyFullScript.bat) in the same folder as the DailyFullBackup.sql:

Now, you can create a task to run this batch script. Select the time and set the action to execute the batch file:

When the task is run, you will see the back in the location specified in the DailyFullBackup.sql file:
C:\sql_server_backups\COMPANY1_FULL_BACKUP.bak