One of the most important roles of a DBA is performing regular backups of SQL Server data. Creating backups of critical business data should be one of the foremost concerns of a database administrator (DBA) because backups provide a way to restore data that otherwise could be completely lost. Now let us discuss the types of backups available in SQL Server 2005.
The following are the types of backups available in SQL Server 2005
- Full Backup
- Differential Backup
- Transaction Log Backup
- Copy-Only Backup
- Partial Backup
- File or Filegroup Backup
A full backup is a backup of the entire database that contains all the data and log file records needed to recover the database to the point in time when backup completed. Full backup should be a part of backup strategy for all the business-critical databases.
Full database backup contains the complete set of data needed to restore and recover a database to a consistent state. It serves as a baseline for all other databases.
You cannot restore a differential, transaction log or partial backups directly by themselves. You need to restore a full backup first before restoring differential, transaction log or partial backups and hence the full backup serves as a baseline for all other backups.
Using T-SQL for full backup:
USE DEEPAK GO BACKUP DATABASE DEEPAK TO DISK='D:\TESTDEEPAK_FULLBACKUP.BAK'
Refer the below screenshot for the same,
Using SSMS for full backup:
Right click the db you want to backup and go to task and select backup. Now you can see the window as shown in the screenshot below. Proceed filling the necessary options and click ok the backup will get completed.
A differential backup backups up only the data that has changed since the last full backup and hence the size will be small when compared to full backup. A differential backup is not a stand-alone backup it needs a full backup to act as a baseline. For larger databases differential backups is common in order to save space and reduce the backup time.
In addition to being smaller and faster than full backup, a differential backup makes the restore process simpler. When you restore using differentials you must first restore the full backup followed by the most recent differential backup that was taken.
Using T-SQL for Differential backup:
USE DEEPAK GO BACKUP DATABASE DEEPAK TO DISK='D:\TESTDEEPAK_FULLBACKUP.BAK' WITH DIFFERENTIAL
Using SSMS for Differential backup:
Refer the below screenshot for the same. Only a minor change you need to change the backup type option as differential.
Transaction Log Backup:
Log backups can be taken only if the recovery model of the database is Full recovery or Bulk-logged recovery. Simple recovery model does not allow transaction log backup because the log file is truncated automatically upon database checkpoints.
The transactional log contains the modifications made to the database. A backup of the log is required to recover transactions between full backups. Log backups are taken between full backups to allow point-in-time recovery with the exception of log backups containing bulk-logged records. Without Transaction log backups you can restore data only till the time when the full or differential backup was taken.
Using T-SQL for log backup:
USE DEEPAK GO BACKUP LOG DEEPAK TO DISK='D:\TESTDEEPAK_log.trn'
Using SSMS for log backup:
Refer the below screenshot for the same. Only a minor change you need to change the backup type option as Transaction log.
Generally each full backup serves as a base backup for any differential or log backups taken later, so by default backups affect other backups and how they will be restored. There may be a situation where you want to take a full database backup without affecting the current backup and restore procedures. Consider the below situation where you have scheduled as a job,
Daily @07:00 Full backup
Daily @10:00 Differential backup (Contains all the changes made after 07:00 till 10:00)
Daily @13:00 Differential backup (Contains all the changes made after 07:00 till 13:00)
Daily @16:00 Differential backup (Contains all the changes made after 07:00 till 16:00)
If you take a full backup of the database manually @11:00 then the subsequent
Differential backup @13:00 (Will contain only the changes made after 11:00 till 13:00). Hence the normal backup sequence will get affected because of this full backup.
In SQL 2005 you can make use of COPY_ONLY option to avoid this. Hence if you take a full backup of your database @11:00 using Copy_Only option the next differential backup @13:00 (Will contain only the changes made after 07:00 till 13:00). Hence by using this option the backup sequence will not get affected.
You can create a copy-only backup for any type of backup:
1.Copy-only data backups (all recovery models) to create a copy-only data or differential backup, use the COPY_ONLY option in your BACKUP DATABASE statement. A data backup taken with the COPY_ONLY option cannot be used as a base backup and does not affect any existing differential backups.
BACKUP DATABASE DEEPAK TO DISK='D:\TESTDEEPAK.BAK' WITH COPY_ONLY
2.Copy-only differential backups These are identical to regular differential backups.
BACKUP DATABASE DEEPAK TO DISK='D:\TESTDEEPAK.BAK' WITH DIFFERENTIAL,COPY_ONLY
3.Copy-only log backups to create a copy-only log backup use the COPY_ONLY option in your BACKUP LOG statement. The transaction log is not truncated by a log backup taken using the COPY_ONLY option.
BACKUP LOG DEEPAK TO DISK='D:\TESTDEEPAK.BAK' WITH COPY_ONLY
Copy-Only Backups can be created using only T-SQL statements. SQL Server management studio does not support these backups.
Partial backup is similar to a full backup, but a partial backup does not contain all of the filegroups. Partial backups contain the all the data in the primary filegroup, every read-write filegroup, and any specified read-only files. A partial backup of a read-only database contains only the primary filegroup.
To create a partial backup, include the READ_WRITE_FILEGROUPS option within the list of files/filegroups to be backed up in your BACKUP statement. To create a partial backup, use the READ_WRITE_FILEGROUPS option in a BACKUP statement.
BACKUP DATABASE DEEPAK READ_WRITE_FILEGROUPS TO DISK='D:\TESTDEEPAK_FULL.BAK'
In order to backup the read-only filegroup specify the syntax as below,
BACKUP DATABASE DEEPAK FILEGROUP = 'Secondary' TO DISK ='D:\TESTDEEPAK_FULL.BAK'
Once you have a full database backup you can use the partial backup to backup only those filegroups that have modified or changed. When you restore you need to restore the full backup first followed by the partial backup.
File or Filegroup Backup:
Using the file or filegroup backup you can backup an entire filegroup or files within the filegroup. These backups are essential when the database size is so large that backups must be done in parts because it takes too long to backup the entire database. Another potential benefit of having filegroup backups is that if the disk on which a particular file resides fails and is replaced, just the file can be restored instead of the entire database.
In addition, when performing file or filegroup backups with full or bulk-logged recovery models, you need to backup the transaction log as well. While restoring the logs must be applied after restoring a file or filegroup backup to roll forward transactions and to maintain data consistency.
If a database is using the simple recovery model only read-only filegroups are supported for filegroup backup because there is no log backups with simple recovery model.
Refer the below syntax to backup the filegroups,
BACKUP DATABASE DEEPAK FILEGROUP ='PRIMARY',FILEGROUP ='Secondary' TO DISK ='D:\TESTDEEPAK_FULL.BAK'
Refer the below syntax to backup the files,
BACKUP DATABASE DEEPAK FILE='DEEPAK_DATA',FILE='DEEPAK' TO DISK='D:\TESTDEEPAK_FILE.BAK'