This article provides 6 methods for SQL server 2019 backup and restore. You can use a professional and robust backup solution - AOMEI Cyber Backup, SSMS GUI, Maintennce Plan, T-SQL script, Command Line, PowerShell to protect your SQL database.
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.
*After making a backup, you may want to learn how to restore SQL database from bak file.
Some users choose to 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.
And it comes with various advantages and features making it fulfill nearly all SQL backup conditions:
• Easy complete backup: Easily add Microsoft SQL to back up. And make sure all critical Microsft SQL database are fully protected.
• Incremental & Differential Backup: It supports full backup for protecting all data. You can schedule SQL differential backup or incremental backup to only backup newly added or changed data.
• Automatic & Scheduled Backup: Set a backup schedule to automatically run backup tasks daily, weekly, monthly, etc. for varied demands.
• Restore to New Location: 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.
You can click the download button to have a 30-day free trial.
If you need to perform SQL Server 2019 backup to Amazon S3, AOMEI Cyber Backup will support archive backup to S3 bucket.
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
☛ 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.
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.
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.
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.
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.
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:
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：
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.