How to Backup Database in SQL Server 2019? (6 Methods)

SQL Server 2019 backup guide

SQL Server backup is a daily task for database maintenance. The purpose of backup is to restore the database and transaction log to the most recent point when there is a data loss, corruption, or even hardware failure.

In the following article, I will share 6 commonly used methods to backup SQL Server 2019 database. You can choose a proper one that suits your condition。

6 Methods to backup SQL Server 2019

*After making a backup, you may want to learn how to restore SQL database from bak file.

Method 1. Best practice - auto backup multiple SQL 2019 databases easily

Some users utilize backup scripts or the SSMS GUI to execute SQL backups, but backup scripts can be complicated for some users and can occasionally result in errors. They are not the ideal answer as a result.

Therefore, I would like to suggest AOMEI Cyber Backup as the best practice for backing up SQL databases. Its user-friendly console makes it simple to backup all SQL servers without the need for complicated instructions.

It comes with various advantages and features making it fulfill nearly all SQL backup conditions:

You can click the download button to have a 30-day free trial.

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

Step 1. Run the downloaded .exe file to install AOMEI Cyber Backup on the server machine, navigate to Source Device -> Microsoft SQL -> Add Microsoft SQL.

Step 2. 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.

Step 3. Next, click  -> Authentication to validate the database instance (via Windows Authentication or SQL Authentication).

☛ How to perform SQL Server 2019 database backup:

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

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

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

Step 4. 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. Then click the Start Backup button.

★Tips:

Backup Cleanup helps you automatically delete older backup versions to save storage space.

Email Notification enables you to receive E-mail notifications when the task is abnormal or successful.

AOMEI Cyber Backup is not only a SQL Server backup program but also an enterprise backup solution for virtual machines.With it, you can easily backup all the Hyper-V or VMware ESXi VMs, and restore data from the created task directly.

After creating a task, you can manage, edit, implement or delete it under Backup Tasks tab. If you want to restore SQL database from it,  click Advance on its top-right corner and choose Restore.

Method 2. Perform SQL Server 2019 backup via SSMS GUI

In SQL Server Management Studio, you could create a basic task via a simple GUI. But please note it only allows you to back up one database at a time.

1. Launch SSMS and connect to the instance. Find the database you want to backup in Object Explorer and right-click it, then choose Tasks > Back Up.

2. Select the Backup type you want to perform. Options include Full, Differential and Transaction Log. Then click Add to specify a target path.

It’s suggested to add an easily accessible path so that you could find the backup file more quickly for recovery.

3. Click OK to execute the SQL Server backup task. When it’s complete, a message will pop up saying backup successfully.

If you want to restrain access of the backup to only authorized people, to ensure the data security, you can enable SQL Server backup encryption, the new feature in SQL Server 2014 and later versions.

Method 2. Auto backup multiple SQL databases via Maintenance Plans

What to do if you have one or multiple databases that you want to back up regularly Maintenance Plans is a viable solution. Here are the steps of how to schedule automatic backup in SQL Server 2019.

Note: This feature is not available in Express editions. If you want to perform SQL Server 2019 Express backup and have the same requirement, please try Method 3/4 in auto backup SQL database guide.

1. Launch SSMS, connect to your instance and make sure the SQL Server Agent Service under Object Explorer is enabled. Otherwise please Start it first.

2. Find Maintenance Plans under Management menu. Right-click it and choose New Maintenance Plan… to create a new job.

3. Name the new plan and the design window will pop out. Here you can click the icon of Schedule, choose Schedule type as Recurring then free to set up the Frequency and Duration. Click OK to confirm the settings.

4. Click Toolbox on the left and drag the Back Up Database Task to the blank area on the right. Then double-click the task to configure it.

5. In General tab, select the Backup type (Full/Differential/Transaction log)，and choose the database to backup. You can select All databases, System databases, All user databases or These databases (manual selection).

6. Switch to Destination tab and specify a target path. You can choose to Back up databases across one or more files, or Create a backup file for every database.

Tip: There is also a small function to View-T-SQL. It can show you the T-SQL commands generated from the task.

7. After setting up and saving the plan, you've created a SQL Server 2019 backup can find it in the list of SQL Server Agent >Jobs. You can right-click it and choose Start Job at Step… to check if it works correctly.

Method 3. Backup SQL 2019 Server database using T-SQL

T-SQL, short for Transact-SQL, is the primary language used to communicate applications with SQL Server. It allows for some more complex and flexible operations than the SSMS GUI, but you need to understand the basic backup commands beforehand.

Connect to your instance, click New Query and enter the appropriate backup commands, then hit Execute to implement it.

The basic command to perform a full backup:

BACKUP DATABASE DatabaseName TO DISK ='FilePath\FileName.bak'

The command to perform a differential backup：

BACKUP DATABASE DatabaseName TO DISK ='FilePath\FileName.bak' WITH DIFFERENTIAL

The command to backup transaction log:

BACKUP LOG DatabaseName TO DISK = 'FilePath\FileName.trn' WITH RECOVERY

*If you want to backup all databases of an instance, you could check MSSQL backup all databases for details.

Method 4. Backup SQL Server 2019 database via Command Line

If you don't want to log into SSMS every time to enter T-SQL commands, then you can also consider using cmd to backup and restore the database.

Press Win + R to invoke the Run window, type “cmd” and then hit OK to open Command Prompt.

Then you can enter the following command to backup a database.

SqlCmd -E -S ServerName -Q "BACKUP DATABASE DatabaseName TO DISK ='FilePath\FileName.bak'"

Tips:

• -E means use a trusted connection, and you can also replace it with the -U -P switch to use username and password for login.
• The instance name can be viewed by right-clicking on the database instance and selecting Properties.
• If you need to execute a backup task frequently, you can also type the commands in a text editor, then save it as a .bat file. Thus you can execute it by double-clicking, or even automate it via Windows Task Scheduler. For more SQL Server 2019 backup scripts, please refer to backup and restore SQL database with command line.

Method 5. Backup SQL 2019 database through PowerShell

Besides Command Prompt, PowerShell also provides cmdlets to backup SQL database without login into SSMS.

1. Click Start > Windows PowerShell > Windows PowerShell ISE (run it as administrator).

2. Click New Script to open a new window. Enter the following command to import corresponding cmdlet.

Import-Module SQLPS -DisableNameChecking

3. Then you can run following command to backup database:

Backup-SqlDatabase -ServerInstance "ServerName" -Database "DatabaseName“-Initialize

Tips：

• Using cmdlet to backup SQL database, the backup files will be saved in the default directory, i.e. the installation path of SQL Server:\SQL Server 2019\MSSQL15.MSSERVER\MSSQL
• -Initialize means to overwrite any existing backup set on the media and make this backup the first backup set on the media.

Can we backup SQL Server 2019 to 2017/2016 or lower version?

It is impossible to restore a backup MS SQL backup to a lower version. You can't backup SQL server 2019 to 2019, 2017, 2016, or lower versions. If you do, you may receive a restore failure error. This is because each version of MS SQL does some changes making the high version not compatible with a lower one.

Conclusion

Among these methods of SQL Server 2019 backup, you can use SSMS GUI to perform some basic jobs intuitively, use Maintenance Plans to back up multiple databases at once, or use T-SQL to reach some more complex and personalized operations.

But if you need both full functionality and simple operation, specialized software such as AOMEI Cyber Backup can provide a better experience. It can help you backup or restore multiple databases in a few clicks, and it's not just for SQL Server, but also for other virtual machines. For example, you can use it to backup VMware ESXi VMs as well.