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
*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'"
- -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
- 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 Centralized Backupper Database. It works on all Windows PCs & Servers, 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 centralized manage console for all desktops, laptops, virtual machines and servers within LAN. With it, you can easily backup all the controlled computers, and restore data from the created task directly.
Download the 30-day free trial:
Install Centralized Backupper Database on a computer as the central management console, and install Agent package on all the clients within LAN. Then you can Request Control for any computers you want to manage and proceed to create centralized task.
Create a SQL Server 2019 backup:
1. Navigate to Tasks > New Task > SQL Server Backup.
2. Click + Add Computers in Step 1 to detect all the controlled computers with SQL Server, then you can select one to back up in the popping out window.
3. Click + Add to detect SQL instances on the select computer, and choose what to backup. You can select the entire instance(s) or specific databases for backup.
The first time you back up an instance, you can connect it through Windows Authentication or SQL Server Authentication.
4. Move to Step 3, and click Add Storage to enter the network path of a share or NAS as the destination. Then you can select a folder as the target directory.
5. Click Start Backup to execute the task. Before that, you can also customize the bakcup with following options:
◉ Settings: Enable email notification, backup encryption and choose compress level.
◉ Schedule: Choose the frequency and time point to perform SQL Server 2019 automatic backup. You can also choose backup mode as full or differential.
◉ Scheme: Set up a rule to auto delete old backups and save storage space.
After creating a task, you can manage, edit, implement or delete it under 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.
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 Centralized Backupper Database Edition 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 computer data. For example, you can use it for centralized system backup as well.