Post
Ask Question

Database Backup Using SQL Queries And Its Type

Introduction

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

Prerequisites

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

For the above prerequisites, you can follow the article that is How to Install SQL Server and SQL Server Management Studio (SSMS)

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

Backup of Database in a compressed condition

Use the below query to take the database backup in a compressed condition.

BACKUP DATABASE Database_Name TO
DISK = 'BackupPath\BackupFileName.bak'
WITH COMPRESSION
GO

Backup of Database in an uncompressed condition

Use the below query to take the database backup in an uncompressed condition.

BACKUP DATABASE Database_Name TO
DISK = 'BackupPath\BackupFileName.bak'
WITH NO_COMPRESSION
GO

Restore the database using a backup file with an 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 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 "Company," now I am taking a backup of this database in a 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 will delete the Database using the Drop query shown in figure-4.

Figure-4

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

Figure-5

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

Figure-6

Now follow the same step shown in figure-4 to delete/drop the database, then follow the steps shown in figure-5 to restore the database using the 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 an uncompressed backup. It is large as compared to compressed backup.
2 It takes time to create the backup file compared to an uncompressed backup. It takes an exceedingly small amount of time to create the backup file.
3 Its restoring time is very less. It takes time to restore the backup file compared to a 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 database backup using SQL queries in compressed and uncompressed states or restore the database from backup using SQL queries. We also learned the difference between Compressed backup and Uncompressed backup.

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

Thanks for reading, and I hope you like it.


Similar Articles