Backup of SQL server database & its type.

What is backup?

 A copy of SQL Server data that can be used to restore and recover the data after a failure. A backup of SQL Server data is created at the level of a database or one or more of its files or filegroups.  We can take backup either on disk or on Tape.

 Why we need backup??

 To minimize the risk of data loss, you need to back up your databases to preserve modifications to your data on a regular basis.
you can recover your data from many failures, such as:

  • Media failure.
  • User errors, for example, dropping a table by mistake.
  • Hardware failures, for example, a damaged disk drive or permanent loss of a server.
  • Natural disasters. DR setup
Types of backup:

  1. Full Backups.
  2. Differential Backups.
  3. Transactional Log Backups.
  4. Copy-Only Backups.
  5. File Backups.
  6. FileGroup Backups.
  7. Partial Backups.
  8. Mirror Backups.
1. Full Backup:
Full database backups represent the whole database at the time  the backup finished. A data backup that contains all the data in a specific database or set of filegroups or files, and also enough log to allow for recovering that data

BACKUP DATABASE [DB_Name] 
TO DISK = N'J:\backupfolder\DB_Name.bak' WITH NOFORMAT, NOINIT,  
NAME = N'DB_Name-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

2. Differential Backups
Differential database backups contain only changes made to the database since its most recent full database backup and and that contains only the data extents that have changed since the differential base.


BACKUP DATABASE [DB_Name] 
TO DISK = N'J:\backupfolder\DB_Name.bak WITH DIFFERENTIAL , NOFORMAT, NOINIT,  
NAME = N'DBA-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

3. Transactional Log Backups.
A backup of transaction logs that includes all log records that were not backed up in a previous backup. (full recovery model)

BACKUP LOG [DB_Name] 
TO DISK = N'J:\backupfolder\DB_Name.trn' WITH NOFORMAT, NOINIT,  
NAME = N'DB_Name-Transaction Log  Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

4. Copy-Only Backups
A special-use backup that is independent of the regular sequence of SQL Server backups. It will not disturb LSN (log sequence number)


BACKUP DATABASE [DB_Name] 
TO  DISK = N'H:\SQLBackup\dba.bak' WITH  COPY_ONLY, NOFORMAT,NOINIT, NAME = N'DB_Name-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

5. File Backups:
A  backup of one or more database files. This allows you to backup each file independently instead of having to backup the entire database.  This is only relevant when you have created multiple data files for your database.  One reason for this type of backup is if you have a very large files and need to back them up individually.  For the most part you probably only have one data file, so this is option is not relevant.

BACKUP DATABASE [DB_Name] FILE = N'db01',  FILE = N'db02' 
TO  DISK = N'H:\SQLBackup\DB_Name.bak' WITH NOFORMAT, NOINIT,  
NAME = DB_Name-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

6.FileGroup Backups
This type of backups which allows you to backup all files that are in a particular filegroup.  By default each database has a PRIMARY filegroup which is tied to the one data file that is created while creating DB. There is an option of creating additional filegroups and placing new data files in any of the filegroups.

BACKUP DATABASE [DB_Name] FILEGROUP = N'PRIMARY' 
TO  DISK = N'H:\SQLBackup\dba.bak' WITH NOFORMAT, NOINIT,  
NAME = N'DB_Name-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO

7.Partial Backups
All SQL Server recovery models support partial backups, .Partial backups are useful whenever you want to exclude read-only filegroups. A partial backup resembles a full database backup, but a partial backup does not contain all the filegroups. Instead, for a read-write database, a partial backup contains the data in the primary filegroup, every read-write filegroup, and, optionally, one or more read-only files. A partial backup of a read-only database contains only the primary filegroup.
  a. Create a full partial backup

                      BACKUP DATABASE DB_Name READ_WRITE_FILEGROUPS
         TO DISK = 'C:\DB_name_Partial.BAK'
         GO

  b.Create a differential partial backup

         BACKUP DATABASE DB_Name READ_WRITE_FILEGROUPS
         TO DISK = 'C:\DB_name_Partial.bak'
         WITH DIFFERENTIAL
         GO

8.Mirror Backups:
A mirrored backup at a given time will allow you to create 2 - 4 identical copies of a database backup. The biggest advantage of using Mirrored Backup is that in case one of the backup sets or the backup file is damaged, then you can use the backup files which are available in another backup set to restore the database.

   a. Full Backup

   BACKUP DATABASE [DB_Name]
   TO  DISK = N'H:\SQLBackup\dba_Mirror_Set_1.bak' 
   MIRROR TO DISK ='H:\SQLBackup\dba_Mirror_Set_2.bak' WITH NOFORMAT,NOINIT,  
   NAME = N'DB_Name-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
   GO

   b. Differential backup

   BACKUP DATABASE [DB_Name] 
   TO  DISK = N'H:\SQLBackup\dba_Mirror_Set_1.bak' 
   MIRROR TO DISK ='H:\SQLBackup\dba_Mirror_Set_2.bak' WITH DIFFERENTIAL,NOFORMAT, 
   NOINIT,  
   NAME = N'DB_Name-Differential Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
   GO

   c. Transaction log backup:


   BACKUP LOG [DB_Name] 
   TO  DISK = N'H:\SQLBackup\dba_Mirror_Set_1.trn' 
   MIRROR TO DISK ='H:\SQLBackup\dba_Mirror_Set_2.trn' WITH NOFORMAT, NOINIT,   
   NAME = N'DB_Name-Transaction Log Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
   GO

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)