0Database Backup in normal terms can be said as a copy of data which can be used in a situation when the things go wrong with your system/server. If your role is IT/Database Administrator, then it is your responsibility to safeguard the data from any disaster apart from critical security.
MS SQL Server, unlike another database system, has the ability to take the backup which can be used during disaster recovery. SQL Server allows the database user to decide and take backup based on the business strategy (RPO) Recovery point objective and (RTO) Recovery Time Objective.
Based on the fact to recover the database till the point of recovery SQL Server has a different type of database recovery model and backup type.
- Full or Database
- Differential or Incremental
- Transactional Log or Log.
Full database backup – Backs up all the data and objects in the data file(s) for a given database.
Differential database backup – Backs up any data and objects in the data file(s) for a given database that have changed since the last full backup. Remember that a differential backup backs up data since the last full backup, even if there have been intervening differential backups.
Transaction log backups – Copy into a backup file all the log records inserted into the transaction log LDF file since the last transaction log backup.
Apart from above type of backup. SQL Server has below backups
- File and filegroup backups - File and filegroup backups back up individual database files and filegroups rather than performing a full database backup. This method backs up very large databases. You must back up the transaction log when performing a file and filegroup backup. You cannot use this method if the Truncate Log On Checkpoint option is enabled.
- Copy-only backups - Copy-only backups are functionally the same as a full database or transaction log backups but do not affect the backup sequence. For example, if you took a full backup, a copy backup, and then a transaction log backup, all the transaction logs since the full backup would be backed up, and the existence of the copy backup would be ignored. Copy backups cannot be used as the basis for a differential backup or transaction log backup.
Happy Learning
Meetopus
