Backup database

---

Backing up a SQL database is an important task and is the process of creating a copy of data to ensure the availability and recoverability of data in case of system failure and to protect from data loss or corruption.

There are several methods to backup SQL database, including using the graphical user interface (GUI) or Transact-SQL (T-SQL).

Here's an example of how to backup database using T-SQL :

1. Run below SQL command in SSMS :

The INIT option specifies that any existing backup files should be overwritten, the COMPRESSION option enables backup compression, and the STATS option specifies the level of detail for backup progress reporting.

2. Above command will backup the [AdventureWorks2019] database to a file named "AdventureWorks2019.bak" located in the "C:\Users\Public\Documents" folder.

That's it! You have successfully backup up [AdventureWorks2019] database using T-SQL.

Here's a step-by-step guide on how to backup SQL database using GUI :

1. In Object explorer, expand the "Databases folder" >> Right-click on the database you want to backup and select tasks >> Select Back Up option.

2. In the Back Up Database window, select the type of backup you want to perform : Full, Differential. >> Click on Add button to add backup destination folder location.

Full backup type :

A full backup is that copies all the data and objects in a database to a backup file.

Differential backup type :

A differential backup is a backup type that captures changes made to a database since the last full backup. Which includes all changes made to the database since the last full backup.

3. Next, click on ellipsis ( three dots ).

4. Select backup destination folder location and give a file name with extension ".bak" >> Click "OK".

5. Click on the "OK" button to start the backup process.

That's it! You have successfully backup up [AdventureWorks2019] database using GUI.

To verify the backup file, Please open backup destination file location as give in below image.

Frequently Asked Questions :