2 Ways to Schedule Database Backup SQL Server 2012 Express

How to Create 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 Centralized Backupper Database Edition.

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 a professional SQL backup software

AOMEI Centralized Backupper Database is a professional SQL Server backup software, which can help you  take SQL Server database backup automatically in SQL Server 2012/2014/2016/2019 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. It’s tedious and boring. And it has an intuitive interface, so users can easily understand and operate it like a professional.

Then, let’s have a close look at the steps to take SQL Server database backup automatically.

Step 1. Download and install Centralized Backupper Database. Then, launch it and registry for use.

Step 2. Select Computers > Uncontrolled Computers, select the computer with SQL server database and then click Request Control (By entering accounts or By sending a message).

Step 3. Go to Controlled Computers and check if the computer with SQL server database are here. Then, click Install client program > select Manual Client Installation or Remote Client Installation to install Centralized Backupper Database Agent in the client computer.

Step 4. Select Tasks > New Task and select SQL Server Backup. Then, you will see the main page of Database Backup, it has 3 steps in total.

Step 5. In Step 1, click Add Computers to scan the controlled computer with SQL server database and select the one you want to backup in the list. Then, click OK.  

Step 6. In Step 2, you will be asked to select source, double-click the box and click Add to scan for all the instances on the server. Then, select one or more you want to backup.

Step 7. In Step 3, you will be asked to select backup destination, double-click the box, click Add storage and type network path, display name, username and password. After that, the newly added storage will be listed under the Share or NAS device tab, select it and click OK.

Step 8. Now things are almost done. You just need to activate the schedule backup and then click Start Backup.

You can choose how often you want to run this backup, such as, daily, weekly, monthly, and specify backup type in the advanced settings, full backup or differential backup. To schedule backup only changed files instead of everything, select the second backup type.

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 Centralized Backupper Database Edition to make this process simple? You just need to follow the instructions on the GUI-base interface.

In addition, it still a centralized backup software that can help you backup items on all the client computer to network share or NAS from a central console. For specific backup types, you can use System Backup, Disk Backup, File Backup, Mirror Sync, etc.