How to Backup Database in SQL Server 2019? (5 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 5 commonly used methods to backup SQL Server 2019 database. Of course, you can turn to professional SQL Server backup software for easier operation.

5 Methods to backup SQL Server 2019

• Method 1. Perform SQL Server 2019 backup via SSMS GUI
• Method 2. Auto backup multiple SQL databases via Maintenance Plans
• Method 3. Backup SQL 2019 Server database using T-SQL
• Method 4. Backup SQL Server 2019 database via Command Line
• Method 5. Backup SQL 2019 database through PowerShell
• Easier way to auto backup multiple SQL 2019 databases without using T-SQL

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

Method 1. 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.

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.

Auto backup multiple SQL 2019 databases easily without T-SQL

Compared with SSMS GUI, using backup scripts can achieve some smarter operations, but it requires you to learn some basic commands, and some minor errors may cause the execution to fail.

Therefore, I’d like to recommend a tool that can avoid these troubles - AOMEI Cyber Backup. It works on all SQL Servers on the network, and enables you to auto backup multiple databases without using T-SQL commands.

It is not only a SQL Server backup program, but also a 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.

Download the 30-day free trial:

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

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

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.

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

☛ How to perform SQL Server 2019 database backup:

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.

✍More useful features:

• Backup Cleanup helps you to delete older backup version automatically and therefore save storage space.
• Email Notification enables you to receive email notifications when the task is abnormal or successful.

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, just click Advance on its top-right corner and choose Restore.

It enables you to restore all databases/specific databases from the backup to original location. You can also restore SQL database to another Server if necessary.

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.