SQL Tutorials

Different Types Of Backups In SQL Server

Different Types Of Backups In SQL Server

In SQL server there are different types of backups are available.Using Option we can create the backups in SQL.Here is the backup types in SQL,

1.Full Backup
2.Differential Backup
3.File Backup
4.Filegroup Backup
5.Partial Backup
6.Copy-Only Backup
7.Mirror Backup
8.Transaction Log Backup

Different Types Of Backups In SQL Server

Full Backup:

Widely using backup type is Full backup.In this method it will take the complete backup of the database which consist of including transaction log also.It is very easy process and even restoration also simple.

Steps:

1.Select the database and right click on it.
2.In tasks click on Backup.
3.Backup type choose Full.
4.Now select the destination in local or remote drive.
5.Then click on Ok button it will take the backup and pop with successfully backup completed message.

Differential Backup

Some times it is not possible to take the complete or full backup.In this case differential backup will be the solution.It will take the backup of extent after Full backup was taken.By this process time consuming for entire database will reduce and size of the regular Full backup’s will also reduce.

After the Full backup differentials backup will store the extent in new backup file.If you will take another differential backup it will also store the extent data after the full backup.

Steps:

– Choose the database and right click on it.
– Now Taks> Backup.
– Select the backup type as Differentials backup.
– Choose the Disk and destination.Click on Ok.

File Backup

File backups are very rare usage for the large size files backup.Instead of taking full backup file backup support to take the large files interms of backup.Individual backup files is possible in this backup type.

Step:

– Right click on the database and choose Tasks>Backup.
– Backup type either Full or Differential.
– Select Files and File groups and choose and click on ok.
– Give the disk and mention the backup path, click on ok.

Filegroup Backup

Similar to File backup filegroup back option is available in SQL backup.In filegroup it will accept the option to take the all files in the particular filegroup.Default all the database have a PRIMARY file group.Here is the option to create additional filegroups that can possible to place the file in the particular group.

Steps:

– Similar steps which followed above for file backup.
– After Files and Filegroups option select the appropriate file group.
– Remain all steps are same as did in the previous file backup.

Partial Backup

partial backup are introduced from SQL server 2005.This type of backup allows to take backup PRIMARY filegroup and all Read-Write file groups with selected files.But this option is not widely using database backup.

Steps:

Run the following script for Partial backup,

BACKUP DATABASE dbname READ_WRITE_FILEGROUPS
TO DISK = ‘C:\dbname_Partial.BAK’
GO

Transaction Log Backup

Transaction logs are best model to restore and backup from Full backup.Using Transaction log backup database will restore on point to point,it will help any accident database loss to retrive backups from the Log backup.

Step:

– Similar steps which followed for Full backup.
– Select the backup type as Transaction Log.

Tags:SQL,SQL backup,SQL database,SQL backup,SQL tutorials,SQL logship