How to Create a SQL Server Database backup

How to Create a SQL Server Database backup

In this article, we will see 'How to Create a SQL Server Database backup'.

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... 
DotNetKida: Open Your SSMS (SQL Server Management Studio) and select the desired database and right click on the database name.

  • 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.
DotNetKida: 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
DotNetKida: Select the destination for the backup

 DotNetKida: 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.

DotNetKida: Select Differential

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:




Protected by Copyscape

Post a Comment

4 Comments

  1. Really helpful content. Thanks for sharing.

    ReplyDelete
    Replies
    1. Thanks!! for the appreciation, Keep visiting for more useful stuff...

      Delete
  2. very niice artical ...please add some example code about web API..

    ReplyDelete
    Replies
    1. Thanks!! Ambuj Pathak
      Yeah, sure we will update some interesting topics about Web API..

      Delete