2 Ways to Schedule Database Backup SQL Server 2012 Express

How to Schedule Database Backup for SQL Server 2012 Express

We all know that schedule backup is a smart choice to save our time and effort, and generally, we can schedule automatic SQL database backup using SSMS. However, in the SSMS of SQL Server Express versions, you cannot create a schedule database because the lack of Maintenance Plan and SQL Server Agent. So, how can you create a schedule database backup for SQL Server 2012 Express?

In fact, you can backup SQL Express database via command line, save the commands as a batch file, and then use Windows Task Scheduler to auto execute the file. For the specific commands, you can refer to backup SQL database command line.

Considering that some users are not familiar with command line or are worried about making mistakes when entering commands, here I will introduce another way of generating backup script using SSMS and automating it with Task Scheduler.

*Also, you can try another easy way to complete this task by using a professional SQL Server backup software like AOMEI Cyber Backup.

Method 1: Schedule Database Backup in SQL Server 2012 Express using SSMS and Task Scheduler

To complete the entire process, you need to create a basic backup and .sql script using ssms first. Then, run it using Task Scheduler. Without further ado, let’s get started to learn the detailed steps.

Create a basic backup in Microsoft SQL Server Management Studio

Step 1. Launch the Microsoft SQL Server Management Studio and connect it. Then, expand the Databases and right-click the database you want to backup, master, for example.

Step 2. Select Tasks > Back up.

Step 3. In the Back up Database - master window, choose backup type and backup destination(to Disk or Tape). Then, click OK.

Note: By default, the backup will be saved in “C:\Program Files\Microsoft SQL Server\Instancename\MSSQL\Backup”. Also, you can remove the default destination and add another one.

Create SQL Script in Microsoft SQL Server Management Studio

Step 1. Go to the Back up Database - master window, under the General tab, select Script > Script Action to File.

Step 2. Type a name for this script file, sqlbackup, for example. and save it to a safe place.

Use Task Scheduler to Schedule Database Backup in SQL Server 2012 Express

Step 1. Go to Start > Administrator Tools and select Task Scheduler. Then, you will see the Task Scheduler window, select Create Basic Task... to start this process.

Step 3. In the Create Basic Task Wizard, type a name and description for this task and click Next.

Step 4. Select the trigger to start a backup, daily, for example, and click Next.

Step 5. Then, you will be asked to set the start date and time, backup interval, set it and click Next.

Step 6. Select Start a program and click Next.

Step 7. Click Browse in the Program/script window and select sqlcmd.exe in the SQL installation location.

Step 7. Then, type-S \sqlexpress -i "C:\Users\Administrator\Desktop\sqlbackup.sql" in the Add arguments (optional) window. Click Next.

Notes: 

• -S tells the task which instance of SQL server to backup
• -\sqlexpress is the default SQL server instance name during the setup process.
• -i tells it which input file to run on the server and point the location of .sql script file created before. 

Step 8. Tick Open the Properties dialog for this task when I click Finish and click Finish.

Step 9. Then, in its Properties window, tick Run with highest privileges and Hidden under the General tab.

Step 10. Go to Settings option, uncheck Stop the task if it runs longer than and click OK.

That’s all the process of schedule database backup in SQL Server 2012 Express. It only allows you to backup one database every time, that’s to say, you need to repeat all the configuration steps above multiple times if you need to backup multiple instances.

Method 2: Schedule Database Backup in SQL Server 2012 Express using professional SQL backup software

AOMEI Cyber Backup is a professional SQL Server backup software, which helps you take SQL Server database backup automatically in SQL Server 2005-2022 and corresponding SQL Server Express.

More surprising, it allows you to backup all the instances and its databases on a server at once, so you don’t need to backup them one by one. And it has an intuitive interface, so users can easily understand and operate it.

As an enterprise-level backup solution, it also supports VMware backup and Hyper-V backup. You can simply download this free version to have a try.

Now, let’s have a look at the steps to schedule database backup in SQL Server 2012 Express as well as other versions.

☛ How to set up SQL Server (Express) backup software:

1. Run the downloaded .exe file to install AOMEI Cyber Backup on the server machine, and launch its main interface. Navigate to Source Device -> Microsoft SQL -> Add Microsoft SQL.

2. Choose Download proxy program or Copy link to download the Agent program and install it on the device with SQL Server installed. Then, click Already installed proxy to select the device.

Next, click  -> Authentication to validate the database instance. You can choose Windows Authentication or SQL Authentication.

That's it, you can schedule data backup for the added SQL Server now.

☛ How to schedule SQL database backup:

1. Click Backup Task -> Create New Task to launch the task creating page. Choose the Backup Type as Microsoft SQL Backup.

2. Click on Device Name to specify the SQL instances and databases for backup. You can select one or multiple databases as you need.

3. Select Target to save backups. You can either specify a local path or network path.

4. Set up a Schedule to run the SQL database backup daily, weekly or monthly (as you need), and choose the backup method as Full, Incremental or Differential Backup.

Click Start Backup to execute the task. Once it's finished, you will find it in the Backup Task tab.

✍More useful features:

• Backup Cleanup helps you to delete older backup version automatically and therefore save storage space. You can upgrade AOMEI Cyber Backup to enjoy this advanced feature.
• Email Notification enables you to receive email notifications when the task is abnormal or successful.

Wrapping Things Up

You probably find that SQL Server 2012 Express does not contain Maintenance Plan or SQL Server Agent, so how to schedule database backup in SQL Server 2012 Express? You can combine Microsoft SQL Server Management Studio and Task Scheduler to make it.

But it’s not easy for many users to understand, and limited to backup once database at a time. So why not try a professional SQL Server backup software like AOMEI Cyber Backup to make this process simple? You just need to follow the instructions on the GUI-base interface.