Best way to backup sql database

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

Therefore, you may wonder how to auto back up SQL Server databases. And there is more than one solution:

◆ For ordinary SQL users, the most accessible way is to use the Maintenance Plan or SQL Server Agent in SSMS (SQL Server Management Studio). They function similarily, 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 as alternative. Thus you can easily auto backup databases or instances with simple clicks, whether it’s Express edition or not.

You can click on the anchor text below to jump to the corresponding method:

Method 1. Auto backup SQL database via Maintenance Plan

A maintenance plan creates a workflow of the tasks to maintain you database. You can also use it for regular backup.

1. Launch SSMS and connect to your server. Expand Management menu under an instance, then right-click Maintenance Plans to create New Maintenance 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 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. 

Method 2. 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:

BACKUP DATABASE example
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 do differential 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:

BACKUP LOG example
TO DISK = 'D:\SQL Server\example.trn';

Click OK to confirm the command and then move to Schedule tab.

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.

Method 3. Automate SQL Server (Express) backup with 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.

That is, you need to carry out the following actions:

✎ 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:

sqlcmd -S server -E -Q "EXEC sp_BackupDatabases @backupLocation='path', @backupType='F'"

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

sqlcmd -S .\MSSQLSERVER_01 -E -Q "EXEC sp_BackupDatabases @backupLocation='D:\SQL Server\', @backupType='F'"

*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 a trusted connection and cannot be used together with -U or -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:

sqlcmd -S .\MSSQLSERVER_01 -Q "BACKUP DATABASE example TO DISK ='D:\SQL Server\example.bak'"

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 an automatic backup task to autorun.

Schedule auto backup tasks 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.

Method 4. Schedule SQL database backup with auto backup software

All above methods can backup single or multiple databases, but they are not very simple and straightforward. There is also a high risk of errors due to small omissions during the setup process.

To auto backup SQL Server databases or instances effortlessly, you could also try SQL auto backup software like AOMEI Centralized Backupper Database. It comes with intuitive GUI that enables you to operate without expertise. With it, you can simply back up as many databases as you want automatically. Besides SQL database, it supports other data types such as files, partitions, disk and OS.

This software works with Windows 11/10/8.1/8/7/Vista/XP, Windows Server 2022/2019/2016/2012 (R2)/2008 (R2)/2003 (R2), Microsoft Windows Home Server (WHS) 2011, Windows Small Business Server (SBS) 2011 and allows you to backup SQL Server 2005 - 2019.

You can download the 30-day free trial and get a up-to 60% off discount:

☛ How to set up the SQL Server auto backup software:

To use it, you need to download and install Centralized Backupper Database on the central machine, and copy the Centralized Backupper Agent from the “package” folder to client computers for installation.

Then, launch the software on the central machine and Request Control to those client computers you want to manage. When the client computers Agree to be controlled, you can continue to perform backup and restore.

☛ How to create SQL Server automatic backup with the software:

I will introduce how to auto backup SQL Server 2012 as an example.

1. Select SQL Server Backup in Home tab, or from the New Task menu under Tasks tab.

2. Click on + Add Computers in Step 1 to detect all the controlled computers with SQL Server database. Then select the client you want to backup, and click OK.

3. Click on Step 2. Select the client computer from the left side and can click + Add to detect all the SQL Server instances on it. On the selection screen, you can check multiple databases or even multiple instances.

4. Now move to Step 3. In the popping out window, click Add Storage to specify a share or NAS device as target location.

5. After that, click Schedule to automate the SQL Server backup. Options include Once/Daily/Weekly/Monthly, and more specific frequency & intervals. Full Backup and Differential Backup are also optional in Advanced window. When the setup is complete, you can click Start Backup to execute the task.

Conclusion

4 methods are provided in this post to help you auto backup SQL Server databases for recovery needs. If you are not an expert nor familiar with Transact-SQL script, the Method 4 could be an easier solution.

This software, AOMEI Centralized Backupper s not only for SQL database backup. It also applies to centralized backup and restore for Windows PCs & Servers. For example, you can use it to backup all computers over network, or sync folders to other locations on client computers.