Backup SQLServer Database and different types of Backup

You should always have proper backup plan in place to protect your database from failures. SQLServer have four types of backups

1. Full Database backup – Complete database backup at the time of backup.
2. Transaction Log backup – Backups up the transaction log file
3. Differential backup–  Backs up the parts of the database that is changed since the last Full Database backup
4. File and file group backup – Backs up the database files that you specify in the FILE or FILEGROUP option.
In SQLServer either you can mention the backup path or you can create a backup device use the device name during the backup. You can either use T-SQL or SQLServer Management Studio to backup SQLServer Database.

Full Database backup: You can backup whole database, this includes part of transaction log which is needed to recover the database using full backup.

BACKUP DATABASE DB_Name
    TO Backup_Device
    WITH FORMAT;
GO

or
BACKUP DATABASE DB_Name
   TO DISK = ‘D:\SQLBackup\FullBackup\DB_Name.bak’
GO

Transaction Log backup:  You must backup the transaction log, if SQLServer database uses either FULL or BULK-LOGGED recovery model otherwise transaction log is going to full. Backing up the transaction log truncates the log and user should be able to restore the database to a specific point in time.

BACKUP LOG DB_Name
   TO Backup_Device
GO

or

BACKUP LOG DB_Name
   TO DISK = ‘D:\SQLBackup\LogBackup\DB_Name.trn’
GO

If transaction log is full users will receive error “Log files are running out of space”

Differential backup:- The database must have full back up in order to take a differential backup, it only backups the changes since last full backup.

BACKUP DATABASE DB_Name
  TO Backup_Device WITH DIFFERENTIAL
GO

or

BACKUP DATABASE DB_Name
  TO DISK = ‘D:\SQLBackup\Differ\DB_Name.bak’ WITH DIFFERENTIAL
GO

Full/Differential/Transaction Log  Backup using SQL Server Management Studio
• Right click on the database name
• Select Tasks > Backup
• Select backup type either "Full" or "Differential" or” Transaction Logl”
• Select the appropriate Backup Destination and click "OK"

File and file group backup:- By default each database has PRIMARY file group which is tied to one data file. You can create additional filegroups and add data files to filegroup. You can perform both FILE and FILEGROUP backups.

BACKUP DATABASE DB_Name
FILEGROUP = ‘Filegroup_Name’ TO Backup_Device
GO

or
BACKUP DATABASE DB_Name
FILEGROUP = ‘Filegroup_Name’ TO DISK = ‘D:\SQLBackup\Filegroup\DB_Name.bak’
GO

File or File Groups Backup using SQL Server Management Studio• Right click on the database name
• Select Tasks > Backup
• Select backup type either "Full" or "Differential"
• Select Backup component as "Files and filegroups"
• Select the appropriate filegroup
• Select the appropriate Backup Destination and click "OK"

Note that from SQLServer 2008 Enterprise edition and later supports backup compression.
Restrictions on Backup Operation1. You cannot backup  offline Database
2. You cannot run two backups concurrently

Regards
Satishbabu Gunukula
http://www.sqlserver-expert.com