Full And Differential Backup Using SSMS

In this article, we will see 'how to create a SQL Server Database backup'. Database backup using SQL Server Management Studio, SQL Server provides a large number of options to create backups. We will discuss here FULL BACKUP and DIFFERENTIAL BACKUP.

 

FULL BACKUP

 

The most common type of backup is the full backup. In this type of backup, it creates a full backup of your database and also includes the transaction logs into the backup.

 

We can create it by using SSMS (SQL Server Management Studio).

 

Let's see how.

 

Full Backup using T-SQL (Transact-SQL)

This will create a full (Complete) backup into the .bak file of the given database,

 

BACKUP DATABASE DotNetKida TO DISK='D:\DotNetKida\Backup\DotNetKida.BAK'

 

Full Backup using SSMS

 

Open your SSMS and select the desired database and right-click on the database name click on Tasks > Back Up,

 

 

Select Full as Backup Type, select Disk as the destination, and then click on Add button to add the directory where the backup will be stored on your disk.

 

 

Select the destination for the backup,

 

 

Click on ok and again ok on the next screen and the backup progress will be started.

 

 

This process will take some time that depends upon your database size. 

 

DIFFERENTIAL BACKUP

 

Another option to create a backup is Differential backup. Differential Backup is the backup of the changes that have been made after a recent full/ complete database backup.

 

Let's see how.

 

Differential Backup using T-SQL (Transact-SQL)

 

This will create a differential backup with .DIF file extension of the given database,

 

BACKUP DATABASE DotNetKida TO DISK='D:\DotNetKida\Backup\DotNetKida.DIF' WITH DIFFERENTIAL

 

Differential Backup using SSMS

 

Open your SSMS and select the desired database and right-click on the database name click on Tasks > Back Up,

 

 

Select Differential as Backup Type, select Disk as the destination, and then click on Add button to add the directory where the backup will be stored on your disk.

 

 

Select the destination for the backup,

 

 

Click on ok and again ok on the next screen and the backup progress will be started.

 

 

This process will take some time and will depend upon your database size.

 

Some Notable Points

• The Backup statement is not allowed in an implicit or explicit transaction.
• Backups created by the recent version or higher of SQL Server cannot be restored in earlier or lower version of SQL Server.
• Check the size of a full database backup by using the sp_spaceused.
• For a large database, the process may take more time and more disk space consider a full database backup with a series of differential database backups.
• Creating a new differential backup always requires a recently full database backup. If the database has never been backed up, then first you have to run a full database backup before creating any differential backups.

I hope this will help you to create a backup of the SQL Server database.