How To Schedule Database Backup In MS SQL Server Express

So, you’re using an SQL Server Express Edition for your small (or not so small) database. We are all aware of the importance of the data in the database, so the natural thing to do is schedule database backup on a daily basis.

Here’s what you should take into consideration. Imagine you’re using an SQL Server Express edition for storing very important data. If you want to do a database backup every day, a good way to go about it is to create an SQL Server Agent job which will back up your database on a daily basis…Oh, wait! You’re using an express edition of SQL Server and an express edition doesn’t have an SQL Server Agent, meaning, you can’t schedule database backup job… Or can you…?

Using the MS SQL Server Only – Express Edition and MS Windows As The Operating System

 

To demonstrate, I’ve created a database called MyExampleDatabase and it is attached to the SQL Server instance called Codd. We’ll create a backup device called MyExampleBackupDevice on which we will store the backup sets. Also, I created two folders for this purpose: C:\MyScripts and C:\MyBackups. In the first one I will keep all scripts needed for this task, and in the second one, I’ll save the database backups.

Our task here is to create a “job” that will schedule database backup as follows: every day, except on Saturday, at 11:30 pm it will do the Transaction Log Backup and every Saturday at 11:30 pm it will do the Full Database Backup including the Transaction Log Backup.

Here’s what we need:

1.    SQL script for Full Database Backup,
2.    SQL script for Transaction Log Backup
3.    A “Windows” script for executing SQL scripts

The easiest way to create a script is to open the Notepad and type in the body of the script.

It goes without saying that before we start, we have to have a database (in my example it is the MyExampleDatabase database) and two folders (in my example: C:\MyScripts and C:\MyBackups). So, before you continue, create those three parts.

Step 1: Creating an SQL script for Full database backup

 

Open your notepad and type in next code:


USE [master]
GO
EXEC master.dbo.sp_addumpdevice @devtype = N'disk', @logicalname = N'MyExampleBackupDevice', @physicalname = N'C:\MyBackups\MyExampleBackupDevice.bak'
GO

BACKUP DATABASE [MyExampleDatabase] TO [MyExampleBackupDevice] WITH NOFORMAT, NOINIT, NAME = N'MyExampleDatabase-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
BACKUP LOG [MyExampleDatabase] TO [MyExampleBackupDevice] WITH NOFORMAT, NOINIT, NAME = N'MyExampleDatabase-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
EXEC master.dbo.sp_dropdevice @logicalname = N'MyExampleBackupDevice'
GO

Save this file in folder C:\MyScripts and give it a name FullBackup.sql.
Close your Notepad.

 

Step 2: Creating an SQL script for Transaction Log Backup

 

Open your notepad and type in next code:


USE [master]
GO
EXEC master.dbo.sp_addumpdevice @devtype = N'disk', @logicalname = N'MyExampleBackupDevice', @physicalname = N'C:\MyBackups\MyExampleBackupDevice.bak'
GO
BACKUP LOG [MyExampleDatabase] TO [MyExampleBackupDevice] WITH NOFORMAT, NOINIT, NAME = N'MyExampleDatabase- Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
EXEC master.dbo.sp_dropdevice @logicalname = N'MyExampleBackupDevice'
GO

Save this file in folder C:\MyScripts and give it a name TLBackup.sql.
Close your Notepad.

 

Step 3: Creating a “Windows” Script for executing FullBackup.sql

 

Open your notepad and type in next code:


echo off

sqlcmd -S "Your Sql Instance Name" -i "C:\MyScripts\FullBackup.sql"
Save this file in folder C:\MyScripts and give it a name FullBackup.batl.
Close your Notepad.
Step 4: Creating a “Windows” Script for executing TLBackup.sql
Open your notepad and type in next code:
echo off

sqlcmd -S "Your Sql Instance Name" -i "C:\MyScripts\TLBackup.sql"

Save this file in folder C:\MyScripts and give it a name TLBackup.batl.
Close your Notepad.

So far we created a SQL script that will make a Full Database Backup (including a Transaction Log Backup) and a SQL script that will create a Transaction Log Backup on the backup device called MyExampleBackupDevice.

Schedule a job!

Step 5: Creating a schedule for the “job”

 

To create a schedule first you have to open Windows Scheduler. The easiest way to do this is to open the Run dialogue box by pressing Windows+R keys on your keyboard:

In the Run dialog box type Taskschd.msc and click the OK button

 

 

1.jpg

 

The Task Scheduler opens and now we’ll create a scheduled task for our Full Database Backup. In this example, I’m using MS Windows 8.1 as the operating system, if you have some other version of Windows it is possible that the interface of your Task Scheduler can vary a bit.
First, click on the Create Task… the link in the Actions section

2

 

On the General tab, give your task some name (in this example I named it MyExampleTest) and make sure that the Run whether the user is logged on or not the option is checked in.

3

 

On the Triggers tab, click on button New…to add a new schedule for this task (we are trying to schedule it every Saturday at 11:30 pm).

5

 

On the New Trigger window set options as follows:

  • Choose Weekly,
  • Adjust time at 11:30 pm (or, in my regional settings it’s: 23:00),
  • Check-in Saturday,
  • Make sure Enabled is checked in.

Click the OK button to save this schedule and go back to the Create Task dialogue box.

6

 

 

On the Actions tab, click the button New.

 

 

7

In the “New Action” dialogue box make sure to the Start a program option is chosen and then browse to the FullBackup.bat script we made in step 3.
After that click the OK button to save the changes.

 

8

 

On the Create Task window click the OK button.
If needed, type the password for your windows user and press the OK button.

Now, in the Task Scheduler window, click on the Task Scheduler Library folder and you’ll see your task in the task list window.

9

 

To test this task you can wait until next Saturday at 11:30 pm or you can right-click on its name (MyExampleTask) and choose option Run.
After the task finishes, you will find a file MyExampleBackupdevice in the MyBackups folder.

 

10

 

To create a task for backing up the transaction log, you have to make another scheduled task (with a different name then MyExampleTask or whatever you named the task for a full backup.).

The difference between this two task is in a name, schedule, and action. So, for this task you can define those tree as follows:
Task name:

11

 

Task schedule:

 

12

 

 

Task action:

 

13

 

The second task you can also test with right click on its name and click on the Run option.

 

And there you go: you have fully automated, neatly scheduled database backup process that will run every day at 11:30 pm backing up your transaction log, and every Saturday, backing up the full database.

Have a nice databasing!