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 - Qiling 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 - Qiling 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
Fast backup and recovery: Qiling Backup performs auto with fast speed, which is vital for business continuity.
Incremental & Differential Backup: Support incremental and differential SQL backup to only backup newly added data or changed data to save time and space.
Auto Backup cleanup: It helps you to delete old or outdated SQL server backup versions automatically and therefore save storage space.
Great Compatibility: This software works with Windows 7 and Windows Server 2008 R2 onwards, and it allows you to backup SQL Server 2005 - 2022.
You can download the 30-day free trial of the SQL backup software and get a time-limited discount:
✍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:
TO DISK = 'D:\SQL Server\example.bak';
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:
TO DISK = 'D:\SQL Server\example.trn';
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, Qiling 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.
Related Articles
- Schedule Database Backup in SQL Server 2012/2014/2016/2019
You will learn how to schedule database backup in SQL server 2012/2014/2016 using SQL Server Agent jobs and SQL Serve Maintenance Plans. Also, an easier way is included. - Backup SQL Server Database and Restore to Another Server | Guide
Here's a simple guide to backup SQL Server databases and restore to another server. In addition, you can also migrate databases via Copy Database Wizard, or try a centralized backup solution. Keep reading to learn the details. - Best Server Centralized Backup Strategy for Multiple Windows Servers
Do you need a centralized server backup strategy to protect multiple Windows servers or PCs on network? Here's a simple solution for home or business use. - Create Schedule Automatic Backup in Windows 10 [3 Ways Included]
You can easily create schedule automatic backup in Windows 10 with the help of Backup and Restore (Windows 7), File History and Qiling Disk Master Standard.