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 backups is the full backup, in this type of backup, it creates a full backup of your database and also includes the transaction logs into backup. 
We can create it by using SSMS (SQL Server Management Studio),

Let's Consider this example :

T-SQL :

• This will create a full (Complete) backup into .bak file of the given database you can try this

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

SQL Server Management Studio(SSMS) : 

• Open Your SSMS and select the desired database and right click on the database name SELECT 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.

DIFFERENTIAL BACKUP:

Now the question is what is the differential backup?

Another option to create a backup is Differential backup. Differential Backup is the backup of the changes has made after a recent full/ Complete database backup.

Let's Consider this example :

T-SQL :

• This will create a differential backup into .DIF file of the given database you can try this

BACKUP DATABASE DotNetKida 

TO DISK = 'D:\DotNetKida\Backup\DotNetKida.DIF' 

WITH DIFFERENTIAL

SQL Server Management Studio(SSMS) : 

To create a differential backup just follow the same step as above, there is one difference only select Backup Type Differential instead of Full as shown below.

I hope it will helpful for some DBA's or developers for taking the backup of their Database.

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.

References:

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/create-a-full-database-backup-sql-server?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/create-a-differential-database-backup-sql-server?view=sql-server-2017