What is backups
in SQL Server? How to take all type of backup, backup syntax with example?
1)
Full Backup: - Full backup,
backs up the entire Database including Transaction Log. With full Backup
Point-in-time recovery is possible because it contains .mdf and .ldf. It offers
complete protection against media failures. It takes more time & requires
more space to store the backup file
--FULL
BACKUP: -
Backup
database dbname
to disk = “path”
--Example
BACKUP
DATABASE AdventureWorks
TO DISK = 'g:\backups\AdventureWorks.bak'
2)
Differential Backup: - Differential backup records only the data that has
change or modified extents since the last full backup. Takes less time and
requires less space. To perform the differential backup, we require at least
one full backup.
--DIFF.
BACKUP: -
Backup
database dbname
to disk = “path” with differential
--Example
BACKUP DATABASE AdventureWorks
TO DISK = 'g:\backups\AdventureWorks.bak'
WITH DIFFERENTIAL
3)
T.Log Backup: - Transaction Log that records all
transactions since last transaction log backup or differential backup. It can
be used for recovering the data to the point of failure (or) specific
point-in-time. These are the incremental backups; it is possible only in Full
and Bulk logged recovery, models only. It is used to truncate the T.log
periodically. To implement log shipping
--T.LOG
BACKUP: -
Backup
log dbname to disk = “path”
--Example
BACKUP
LOG AdventureWorks
TO DISK = 'g:\backups\AdventureWorks.bak'
4)
File/File Group backup: - File and File group backups are a specialized form of
DB backup. In which only certain individual file (or) file groups from a DB are
backup.
--FILE/FILE
GROUP BACKUP: - APPLICABLE ONLY ON ADDITIONAL FILEGROUP, NOT ON PRIMARY
FILEGROUP.
backup
database <dbname> filegroup=’FG name’ to disk=’path’
--Example
BACKUP
DATABASE TestBackup
FILEGROUP = 'ReadOnly' TO DISK = 'C:\TestBackup_ReadOnly.FLG'
5)
Copy only backups: If we want backup without
effecting existing plan we can use copy only backups. SQL server supports
copy-only full backup and copy only t.log backup.
--COPY
ONLY BACKUPS:
Backup
database/Log dbname to disk = “path” with copy_only
--Example
Backup
database [AdventureWorks2012]
to disk = 'D:/copyonlybak.trn'
with copy_only
6)
Striped backups: If the size of backup is
large where there is no required disk space in any drive we can split backup
into multiple files into different disks.
--STRIPED
BACKUPS:
Backup
database dbname
to disk = “path1,to disk=’path2’ WITH NOFORMAT,STATS=10
--Example
BACKUP
DATABASE AdventureWorks
TO
DISK = 'C:\Backup\MultiFile\AdventureWorks1.bak',
DISK
= 'C:\Backup\MultiFile\AdventureWorks2.bak',
DISK
= 'C:\Backup\MultiFile\AdventureWorks3.bak'
7)
Mirrored Backups: In order to reduce
restoration problems we can take backup of same db in multiple locations so
that if any media set is get damaged we can restore from the. Maximum we can
write 4 mirrors for same media set.
--MIRRORED
BACKUPS:
Backup
database dbname
to disk = “path’, MIRROR TO disk=’path2’ WITH FORMAT
--Example
1
BACKUP
DATABASE AdventureWorks
TO
DISK = 'C:\Backup\SingleFile\AdventureWorks.bak'
MIRROR
TO DISK = 'C:\Backup\MirrorFile\AdventureWorks.bak'
WITH
FORMAT
--Example
2
BACKUP
DATABASE AdventureWorks
TO
DISK = 'C:\Backup\MultiFile\AdventureWorks1.bak',
DISK
= 'C:\Backup\MultiFile\AdventureWorks2.bak',
DISK
= 'C:\Backup\MultiFile\AdventureWorks3.bak'
MIRROR
TO DISK = 'C:\Backup\MirrorFile\AdventureWorks1.bak',
DISK
= 'C:\Backup\MirrorFile\AdventureWorks2.bak',
DISK
= 'C:\Backup\MirrorFile\AdventureWorks3.bak'
WITH
FORMAT
8)
Tail-log backup: The most common situations-
To perform a log backup that will skip log truncation and then
take the database into the RESTORING state in an atomic state, use
the NO_TRUNCATE and NORECOVERY options together.
--Example
BACKUP LOG AdventureWorks TO DISK = 'C:\Backup\AdventureWorks.bak' WITH NORECOVERY, NO_TRUNCATE
Note: We recommend using
NO_TRUNCATE only when your database is damaged or corrupted.
If database is offline and you are unable to start but you need to
restore the database, than will have to back up the tail of the log. No
transactions can occur during this time, using the WITH
NORECOVERY option is not mandatory.
If a database is damaged, you should try to make a tail-log
backup by using the WITH CONTINUE_AFTER_ERROR option in the BACKUP
statement.
CHECKSUM: is also an option which will show you the Page Id in result where the database has corrupted.
--Example
BACKUP DATABASE AdventureWorks TO DISK = 'C:\Backup\AdventureWorks.bak' WITH CHECKSUM, CONTINUE_AFTER_ERROR
Option: -
INIT means overwrite
noinit mean append
format mean independent
noformat: both files are required
Note :- To enable checksum by default for all database backups-
SP_CONFIGURE BACKUP CHECKSUM DEFAULT
Please comment below if missing any type of backup- Jainendra Verma
