Backups :
" A copy of database is used to recover the data after a system failure "
Advantages :
Backup is safe guard to databases because data may loss due to many failures such as Media Failures , User errors , Hardware Failures and Natural Disasters etc.
With good backups we can recover database from failures .
Types of Backups :
1. Full Backup
2. Differential Backup
3. Transaction Log Backup
4. File / File Group Backup
5. Tail Log Backup
6. Copy Only Backup
7. Partial Backup
1. Full Backup :
Full backups captures all the data that is stored in the database .
Backs up the entire database (.mdf) including part of the transaction log (.ldf) .
2. Differential Backup :
Differential backup captures modifed and new extents since the last full backup .
3. Transaction Log Backup :
Transaction log backup captures active transactions and truncates the inactive transaction from the transaction log .
It can be used to recover the data up to the point of failure (or ) can restore the database to a specific point in time .
4. File / File Group Backup :
File group captures only individual files or filegroups instead of taking whole database .
It is alternative strategy to full backup for large databases .
5. Tail Log Backup :
Tail Log backup is the last backup that is used to recover the database to the point of failure .
To Perform tail log backup database recover model should be Full or Bulk - Logged.
6. Copy - Only Backup :
Copy only backup is introduced in SQL Server 2005 .
It will not effect existing Backup and Restore Procedures .
Copy - Only backup cannot affect on log sequence number .
7. Partial Backup:
Partial Backup is introduced in SQL Server 2005.
We can peroform Partial Backup on Primary File Group / Read - Write file groups / any optional specified files .
Partial backup is the best option if you have Read - Only file group in the database and you don't want to perform backup for this . You can take backup only for Read - Write file group . We can perform both Full and Differential backups and cannot perform tlog backup .
Syntax :
Backup database <database name > Read_Write_Filegroup Name
to disk ='path for .bak'
Backup database <database name > Read_Write_Filegroup Name
to disk ='path for .dif ' with differential
Mirrored Backups :
In order to reduce restoration problems we can take backup of same database in multiple locations so that if any one media set is get damaged we can restore from other .
Maximum we can write 4 mirrors for same media set.
Ex: Create AdventureWorks full backup into two different files .
Backup database AdventureWorks to disk ='d:\backups\Adv_Full.bak'
mirror
to disk ='d:\backups\Adv_Full_Mirror.bak'
Stripped Backups:
Taking backup into multiple drives called as Disk Stripping. or
If the size of backup is large where there is no required disk space in any drive then we can split the backup into multiple files into different disks. This process is called as stirpped backups.
If the server consists of multiple CPU's then it provides better performance.
Ex:
Backup database [AdventureWorks]
to
disk = 'd:\backups\adv1.bak'
disk = 'e:\backups\adv2.bak'
disk = 'f:\backups\adv3.bak' with noformat , stats=10
Points to Remember :
We can't take either differential /log backup without a full backup .
" A copy of database is used to recover the data after a system failure "
Advantages :
Backup is safe guard to databases because data may loss due to many failures such as Media Failures , User errors , Hardware Failures and Natural Disasters etc.
With good backups we can recover database from failures .
Types of Backups :
1. Full Backup
2. Differential Backup
3. Transaction Log Backup
4. File / File Group Backup
5. Tail Log Backup
6. Copy Only Backup
7. Partial Backup
1. Full Backup :
Full backups captures all the data that is stored in the database .
Backs up the entire database (.mdf) including part of the transaction log (.ldf) .
2. Differential Backup :
Differential backup captures modifed and new extents since the last full backup .
3. Transaction Log Backup :
Transaction log backup captures active transactions and truncates the inactive transaction from the transaction log .
It can be used to recover the data up to the point of failure (or ) can restore the database to a specific point in time .
4. File / File Group Backup :
File group captures only individual files or filegroups instead of taking whole database .
It is alternative strategy to full backup for large databases .
5. Tail Log Backup :
Tail Log backup is the last backup that is used to recover the database to the point of failure .
To Perform tail log backup database recover model should be Full or Bulk - Logged.
6. Copy - Only Backup :
Copy only backup is introduced in SQL Server 2005 .
It will not effect existing Backup and Restore Procedures .
Copy - Only backup cannot affect on log sequence number .
7. Partial Backup:
Partial Backup is introduced in SQL Server 2005.
We can peroform Partial Backup on Primary File Group / Read - Write file groups / any optional specified files .
Partial backup is the best option if you have Read - Only file group in the database and you don't want to perform backup for this . You can take backup only for Read - Write file group . We can perform both Full and Differential backups and cannot perform tlog backup .
Syntax :
Backup database <database name > Read_Write_Filegroup Name
to disk ='path for .bak'
Backup database <database name > Read_Write_Filegroup Name
to disk ='path for .dif ' with differential
Mirrored Backups :
In order to reduce restoration problems we can take backup of same database in multiple locations so that if any one media set is get damaged we can restore from other .
Maximum we can write 4 mirrors for same media set.
Ex: Create AdventureWorks full backup into two different files .
Backup database AdventureWorks to disk ='d:\backups\Adv_Full.bak'
mirror
to disk ='d:\backups\Adv_Full_Mirror.bak'
Stripped Backups:
Taking backup into multiple drives called as Disk Stripping. or
If the size of backup is large where there is no required disk space in any drive then we can split the backup into multiple files into different disks. This process is called as stirpped backups.
If the server consists of multiple CPU's then it provides better performance.
Ex:
Backup database [AdventureWorks]
to
disk = 'd:\backups\adv1.bak'
disk = 'e:\backups\adv2.bak'
disk = 'f:\backups\adv3.bak' with noformat , stats=10
Points to Remember :
We can't take either differential /log backup without a full backup .
No comments:
Post a Comment