In this article, you will learn how to take backup of database using SQL queries in a compressed condition and in uncompressed condition.

Prerequisites
    Install SQL Server
    Install SQL Server Management Studio (SSMS)

For above prerequisites, you can follow the article that is How to Install SQL Server and SQL Server Management Studio (SSMS)
Backup of Database in compressed condition

Use the below query to take the backup of database in compressed condition.
BACKUP DATABASE Database_Name TO
DISK = 'BackupPath\BackupFileName.bak'
WITH COMPRESSION
GO

Backup of Database in uncompressed condition
Use the below query to take the backup of database in uncompressed condition.
BACKUP DATABASE Database_Name TO
DISK = 'BackupPath\BackupFileName.bak'
WITH NO_COMPRESSION
GO

Restore the database using backup file with SQL query
RESTORE DATABASE Database_Name
FROM DISK = 'BackupPath\BackupFileName.bak'
WITH RECOVERY
GO

RESTORE DATABASE Database_Name
FROM DISK = 'BackupPath\BackupFileName.bak'
WITH RECOVERY
GO

Now I will demonstrate the same with the help of an example.
Before we start we need to know where do we define the following items in the SQL query as shown in figure-1.
    Database name
    Backup folder location
    Backup file name
    Backup file extension

As I have a database named as "Company" now I am taking backup of this database in compressed condition as shown in figure-2.

Now I can see the Backup file is created successfully and saved at the defined location as shown in picture below

now I am going to delete the Database using Drop query as shown in picture below

Now restore the database using backup file as shown in a picture below:

Now I am going to create a backup of the same database "Company" in uncompressed condition as shown in a picture below:

Now I am going to create a backup of the same database "Company" in uncompressed condition

Now follow the same step as shown in figure-4 to delete/drop the database, then follow the steps as shown in figure-5 to restore the database using compressed backup file.

 

Difference between Compressed Backup and Uncompressed Backup

S. No.            Compressed Backup                       Uncompressed Backup           
1 It is very small in size as compared to uncompressed backup.            It is large in size as compared to compressed backup.           
2 It takes time to create backup file as compared to uncompressed backup.            It takes an exceedingly small time to create backup file.           
3 Its restoring time is very less.            It takes time to restore the backup file as compared to compressed backup.           
4 It saves about 75% of disk space as compared to uncompressed backup.            Its size is approx 4 times of compressed backup.           

HostForLIFEASP.NET SQL Server 2021 Hosting