“Let’s say you have a database that is storing very important business data (e.g., sales data), and it’s being stored on a daily basis, and somehow, it’s got corrupted, or some wrong data entered and affected all the calculated data of the database. It is not easily possible to roll back the calculation. In such a scenario, the best and easiest way to restore the database is with the previous day’s data. To restore, we just need to take a backup. Today we will discuss the backup process of the Microsoft SQL server.”

Backing up the MSSQL Database

Full Backup: Full backup is to take when you need a fresh backup of the whole database. One should take full backup for the first time.

Differential Backup: It will take the backup of the changes or modifications done in the database since the full backup is taken on the same database. One who takes DB backup daily should use this feature.

To do the operation, you need to open the SQL Server Management Studio (SSMS) and connect to the instance where your database is available, as per the below screenshot.

Here are the methods to take SQL database backup.

1. Using SQL Script.

There is a SQL statement to take a backup of the MSSQL database. Simply we must run the script in any database, and automatically the desired database will be backed up in the desired location with .bak file format. The SQL Script is as below. Once you type it in SSMS, Press “F5” or press the Execute button (highlighted in the screenshot) in the SSMS.

The above script will take the full backup of the AdventureWork2017 database.

Please refer to the below screenshot. Please find the below script for the differential backup of the AdventureWork2017 database.

Upon executing the script using the “F5” Button or Execute button, the backup process will be started.

This backup time varies depending on your database size. For this example, our DB size is 206 MB, and it has taken 28 seconds to take the full backup of the database. Once the backup is done, the below message will be seen as output.

And you can navigate to the folder path which you have chosen for backup to see the .bak (backup) file. Please see the below screen capture for your reference.

You can even create a full database backup in multiple locations using a mirror copy. It will create two or more identical .bak files in a different locations. We can create up to 3 mirror files in a backup using the below script.

Also, we can create a DB backup in multiple files in case the database is huge in size by using the below script.

2. Using the graphical interface of SQL Server.

Another easy way to take database backup is MS Sql Server is to take backup using the UI of Microsoft SQL server.

First, expand the server to get the database list present in the server. Now Right, click on the database and find the Backup option as per the below screenshot.

Now you will get one backup window open.

In the general tab of that window, there is an option to select the database in which you want to take a backup. You can select “FULL” as a backup type for a full database backup. And if you want to take the superset of the last full backup, which can carry all the changes that are made after the last full backup is taken. You can select “Disk” as Back up to and click Add to choose the desired path and file name for the backup file. Refer to the below screenshots for more clarification.

After selecting the path and file names, when you click ok to back up process will be started. Backup time will depend upon the size of the database. After the process is finished, you can get the .bak file inside the folder you selected.

Conclusion

From the above article, we have understood the importance and the different ways to take SQL server database backup. We can write a SQL script (full/ differential) to the backup database inside a SQL agent job and schedule it to be run on a particular frequency to take SQL database backup automatically on a daily basis.

Also, when we are taking backups, we should look for the security of the backup files from unauthorized access. To achieve this, we should use encryption during backing up any database. It can be done in SQL server 2014 and later versions (Enterprise or Standard version).