Ads by Google

Schedule database backup jobs by using SQL Server Express and Windows Task Scheduler

The free SQL Server Express edition does not provide a built-in utility to schedule database backup jobs (SQL Server Agent is present in the paid editions only); in this article we use the free alternative – we schedule database backup jobs by using SQL Server Express and Windows Task Scheduler to create automated daily, weekly, and monthly backups of SQL databases.

Exclusion of SQL Agent will not stop us to find a workaround – we can exercise benefits of Windows Task Scheduler, a the tool built-in the Windows operating system. Workaround, to perform daily, weekly, and monthly backups, consists of:

  1. Custom T-SQL scripts,
  2. SQL integrated utility, present in the Express editions: sqlcmd.exe,
  3. Windows Task Scheduler – to interconnect everything:
    • custom scripts,
    • integrated console tool with extra arguments,
    • time when it is executed (daily, weekly, monthly triggers).

1. Custom T-SQL script

Let’s say that name of our database is EasyDatabase. For this article, we’ll create three T-SQL scripts located e.g. at path: D:\DBBackup. In this folder create also three sub-folders: Daily, Weekly, Monthly.

Name of the first script: DailyBackupScript.sql
Open it with Notepad or other text editor, and paste this code:

BACKUP DATABASE [EasyDatabase] TO DISK = N’D:\DBBackup\Daily\EasyDatabase.bak’ WITH NOFORMAT, INIT, NAME = N’EasyDatabase-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Name of the second script: WeeklyBackupScript.sql
Content of this script:

BACKUP DATABASE [EasyDatabase] TO DISK = N’D:\DBBackup\Weekly\EasyDatabase.bak’ WITH NOFORMAT, INIT, NAME = N’EasyDatabase-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Name of the third script: MonthlyBackupScript.sql
Content of the script:

BACKUP DATABASE [EasyDatabase] TO DISK = N’D:\DBBackup\Monthly\EasyDatabase.bak’ WITH NOFORMAT, INIT, NAME = N’EasyDatabase-Full Database Backup’, SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

Refer to this image to double check if you created everything well:

Backup SQL database script

2. Integrated tool sqlcmd.exe

This tool is used to execute those three *.sql scripts. Location of the tool when SQL Server 2012 Express is installed:
C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE

Tool must be used with the following arguments (example):
-S SQLSERVER\EASYDBSQL -i “D:\DBBackup\DailyBackupScript.sql”

  • -S argument tells which instance of SQL server to back up (\sqlexpress if the default was used during the SQL setup),
  • -i argument tells which input file to run on the server (the input file is the name/location of the .sql script file created for backup step).

Before scheduling, the script can be tested in the command prompt (SQL Server Express 2012):

C:\Program Files\Microsoft SQL Server\110\Tools\Binn\sqlcmd.exe -S SQLSERVER\EASYDBSQL -i “D:\DBBackup\DailyBackupScript.sql”

If everything went OK, the backup named: EasyDatabase.bak is created in the directory: D:\DBBackup\Daily\

The similar logic is applied to the weekly and monthly backups, as well.

 

3. Windows Task Scheduler

Task scheduler is used to schedule three backup jobs – Daily, Weekly and Monthly. For all jobs, in the job wizard tick:
a) to execute them with the highest priority,
b) to execute even if the user is not logged in,
c) to execute program; browse for program sqlcmd.exe and enter extra arguments (as previously mentioned). Note that every job must match its own script (e.g. daily job must match daily T-SQL script).

Job1: DailyBackupTask

Task Scheduler Database Backup

• Trigger: every day
• Time: 23:35

Task Scheduler SQL database

On the Actions tab, in the New Action dialog box, select to “Start a program”, and find SQLCMD.EXE (actual path depends on your version of SQL Server). Also is mandatory to add arguments, depending on the job.

For example, for this daily job, you’d enter this: -S SQLSERVER\EASYDBSQL -i “D:\DBBackup\DailyBackupScript.sql”

Task Scheduler Start Program

Perform the similar procedure for the other tasks.

Job2: WeeklyBackupTask
• Trigger: weekly
• Time: every Sunday, at 23:00

Job3: MonthlyBackupTask
• Trigger: monthly
• Time: 23:15, every last day of January, February, ……, December.

 

Note that every backup created in these three folders overwrites the old one.
Location of the backups:

D:\DBBackup\Daily\
D:\DBBackup\Weekly\
D:\DBBackup\Monthly\

 

If you like this article, please share it and leave the comments. :)
Fil

Summary
Schedule database backup jobs by using SQL Server Express and Windows Task Scheduler
Article Name
Schedule database backup jobs by using SQL Server Express and Windows Task Scheduler
Description
The free SQL Server Express edition does not provide a built-in utility to schedule database backup jobs (SQL Server Agent is present in the paid editions only); in this article we use the free alternative - we schedule database backup jobs by using SQL Server Management Studio Express and Windows Task Scheduler to create automated daily, weekly, and monthly backups of SQL databases.
Author
www.CreativForm.com