Database Backup Using SQL Queries And Its Type

Introduction

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)

Currently i have installed SQL Server 2019 and SQL Server Management Studio 18.10 (SSMS) on my windows 11 machine.

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


Figure-1

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


Figure-2

Now I can see the Backup file is created successfully and saved at the defined location as shown in figure-3.


Figure-3

now I am going to delete the Database using Drop query as shown in figure-4.


Figure-4

Now restore the database using backup file as shown in figure-5.


Figure-5

Now I am going to create a backup of the same database "Company" in uncompressed condition as shown in figure-6.


Figure-6

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.           


Summary

In this article, we learned how to make a backup of database using SQL queries in compressed and uncompressed state or restoring database from backup using SQL queries. We also learned the difference between Compressed backup and Uncompressed backup.

If you have any query/suggestion on article, please leave your questions and thoughts in the comment section below.

Thanks for reading and I hope you like it.