4 Methods to Automatically Backup SQL Server Databases

How do I auto backup SQL Server database?

It's may not a big deal to backup several SQL Server databases every now and then, but if databases number is large, or you need to backup frequently for data consistency, setting it up manually every time would be laborious.

Therefore, you may wonder how to take SQL Server backup automatically. And there is more than one solution:

◆ For normal SQL users, the most accessible way is to use Maintenance Plan or SQL Server Agent in SSMS (SQL Server Management Studio). They function similarly, but if you don't want to use any command or script at all, you can choose the former. ◇ However, these tools are not available in SSMS if you are running Express editions of SQL Server. Instead, you need to create a batch file with command line, and use Task Scheduler to automate it. ◆ If you find these methods cumbersome, you could also use SQL auto backup software - AOMEI Cyber Backup. Thus you can easily auto backup databases or instances with simple clicks, whether it’s Express edition or not.

Just keep on reading, and choose a way to backup your SQL database.

Method 1. Best practice to auto backup SQL Server Database

To auto backup SQL Server databases or instances effortlessly, you could also try the SQL auto backup software - AOMEI Cyber Backup. It comes with an intuitive GUI that enables you to operate without expertise. With it, you can automatically back up as many databases as you want automatically daily, weekly, monthly, etc. with ease.

And it has more advantages and features to meet different needs, such as

You can download the 30-day free trial of the SQL backup software and get a time-limited discount:

☛ How to schedule auto backup MSSQL database:

1. Run the installed AOMEI Cyber Backup on the server machine, navigate to Source Device -> Microsoft SQL -> Add Microsoft SQL.

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

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

☛ How to take SQL Server database backup automatically:

1. Click Backup Task -> Create New Task to launch the task creating page. Choose 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. Before Start Backup, you can also set up a Schedule to run the SQL database backup daily, weekly or monthly, and set the backup method as Full, Incremental or Differential Backup.

Execute the task. Once it's finished, you will find it in the Backup Task tab.

✍Note:

• You can enable Backup Cleanup and set a backup retention rule to delete old backup versions.
• Email Notification enables you to receive email notifications when the task is abnormal or successful.

Method 2. Auto backup SQL database via Maintenance Plan

A maintenance plan on SQL Server Management Studio creates a workflow of the tasks to maintain your database. You can also use it for SQL Server backup schedule.

Here are the steps to create scheduled automatic SQL database backup using SSMS:

1. Launch SSMS and connect to your server. Expand Management menu under an instance, then right-click Maintenance Plans to create NewMaintenance Plan, or open Maintenance Plan Wizard.

They are essentially the same, but the wizard will lead you through the whole operation. Here I will choose the former as an example.

2. You will be asked to give a name to this plan. After confirmation, you can click Toolbox on the left side and drag Back Up Database Task to the lower part of MaintenancePlan window.

3. Double-click the generated task, you can configure it in the pop-up window. First, choose Backup type from “Full”, “Differential” and “Transaction Log”. Then select one or more Databases(s) to backup. Click OK to confirm it. If you selected more than one database, you can also choose to Create a backup file for every database if you want.

• Full backup includes all database objects, system tables, data, and transactions that occur during the backup.
• Differential backup includes the data that has changed and transactions that occur during the backup process since the last full backup.
• Transaction log backup includes transactions that have occurred on a database since the previous transaction log backup and then truncates the transaction log and then truncates a transaction log. A transaction log backup ensures database recovery to a specific point of time e.g. to a moment prior to data loss.

4. Now you can click on the calendar icon, namely Subplan Schedule to automate the database backup task. For a time based SQL backup schedule, you can keep the selection of “Recurring” as the Schedule type, and further configure the frequency and duration.

Alternatively, you can also make the task “Start automatically when SQL Server Agent starts”, or “Start whenever the CPUs become idle”.

5. Save the changes and now you’ve set up the SQL Server automatic backup. You can then find the task under SQL Server Agent and all your backups will be made according to the specified schedule.

This is how to automate SQL Server backup with GUI wizard. If you have some knowledge of T-SQL, you may consider the next method.

Method 3. Scheduled job to backup database with SQL Server Agent

SQL Server Agent is a service that executes scheduled administrative jobs. Each job contains one or more job steps, and each step contains its own task, database backup, for example.

1. To use it, launch SSMS and connect to your instance. Make sure the SQL Server Agent is enabled. If not, right-click it and Start it. Then, choose New > Job from the right-click menu.

2. The New Job window will pop out. You can type its “Name” and “Description” in General tab.

3. Then move to Steps tab, click New at the left-bottom corner to configure it. First make sure the Type is “Transact-SQL script (T-SQL)”, and choose Database as “master”. Then you can enter the command to perform backup.

The basic command for database backup was like:

You just need to replace the database name and file path as you need. If you want to dodifferential backup for the database(s), you can add a line of “WITH DIFFERENTIAL”. But please note a full backup must be created beforehand.

To backup transaction log, just replace the “DATABASE” with “LOG” in the command:

Click OK to confirm the command and then move to Schedule tab to automate SQL Server backup.

4. Similarly, click New to create a new schedule. You can give it a name, choose a schedule type and configure the frequency as you like. When it’s complete, click OK to save it.

5. The main setup is complete. You can click OK to execute it right away, or continue to configure Alerts and Notifications.

Once everything is in place, you can right-click the created job, choose Start Job at Step... to test if it works properly. If not, please hit View History from the right-click menu to check for errors.

Note this function is not available for SQL Express backup. If you are using an Express edition, please move to the next solution.

Method 4. Automate SQL Server (Express) backup via Task Scheduler

Although there are multiple solutions to perform SQL Server automatic backup, in Express editions, you don’t have so many choices. Without the Maintenance Plan and SQL Server Agent functions built into SSMS, you have to create a batch file of Transact-SQL script, and use Windows Task Scheduler to automate it.

Here's what to prepare and how to automate SQL Server Express backups:

✎ Note: the SQLCMD utility is required to enter Transact-SQL statements, system procedures, and script files. It’s shipped as part of the product in SQL Server 2014 and lower versions. But if you are running SQL Server 2016 and above version, you need to download it in this page.

1. Connect to your SQL Server Express instance, find Stored Procedures under Databases > System Databases > master > Programmability. Right-click it and click New Stored Procedure.

2. In the SQLQuery window, copy all the content in this SQL Express Backups script provided by Microsoft, then click Execute button above. It will create a sp_BackupDatabases stored procedure in your master database.

3. Open text editor and enter the backup script in it. The basic command to perform full backup was like:

For example, I want to backup all databases in the local named instance of MSSQLSERVER_01by using Windows Authentication, the command is:

*You can copy the name of server and instance from Server Properties. Make sure it’s correct.

*If you want to use SQL Server Authentication, you can add the switchs: -U -P

*The-E switch means to use atrusted connection and cannot be usedtogether with-Uor-P.

*To do differential backup, please replace the last “F” with “D”. Similarly, you can use “L” for Transaction log backup.

You can also back up individual SQL database as you like, for example:

4. Save the file as .bat extension. You could give it a name such as Sqlbackup.bat.

5. Open Task Scheduler to automate the backup batch file. You can find it either by searching for “task scheduler” in Windows, or going to Control Pane > Administrative Tools > Task Scheduler.

6. Click Create Basic Task and follow the wizard to choose a Trigger. If you want to perform SQL Server auto backup every day, then choose “Daily”.

7. Choose the Action as “Start a program”, and specify the batch file as the program. When the setup is complete, you’ve got a SQL Express automatic backup task to autorun.

Schedule sql backups may accumulate many backup files in your disk, so if you want to distinguish these daily backups, you can also create daily database backups with unique names in sql server.

Conclusion

Now you know how to schedule automatic backup in SQL server 2022, 2019, 2017, 2016, etc. Methods are provided here to help you schedule SQL backup and auto backup SQL Server databases for recovery needs.

But by comparison, AOMEI Cyber Backup is the best practice, it is easy to use, powerful, and performs fast, especially If you are not an expert nor familiar with Transact-SQL script, or find other ways to perform SQL server backup taking a long time.