This topic is the second part for lesson Manage Data which covers 20% of the examination.
Understanding Backup Types
SQL Server 2012 supports the following backup types.
Full database backups – this type of backup includes all database objects, system tables and data. Transactions that occur during the backup are also recorded. Full database backup allows you to restore the database as it was during the time of the backup.
Differential backups – this type of backup backs up data that has since changed from the last full backup. You use this type of backup in conjunction with the full backup. Differential backups back up data that changed since the last full backup, even if there has been a subsequent differential backup.
Transaction log backups – this type of backup records the changes that have occurred since the previous transaction log backup and truncates the transaction log. Transaction log backups function incrementally rather than differentially, hence during the restore process, you must do the restoration in sequence.
File and filegroup backups – For large database with multiple files and filegroups, you can perform a file and filegroup backups if you need to backup individual database files and filegroups. You need to perform a transaction log backup after you have done a file and filegroup backup.
Copy-only backups – this type of backup is similar to a full database backup or transaction log backup but do not affect the backup sequence.
In your backup strategy, you can plan to combine more than one backup types. In my case, I implemented a strategy of full backup daily, morning and midday differential backup and transaction log backup every 15 minutes. When implementing a backup plan, you must also take into consideration the method you will use to restore your database.
Using Backup Compression
Backup compression reduces the amount of the space required to store a backup, but the CPU cost during compression is high. You can reduce the load in the CPU via the Resource Governor and limit the CPU usage.
Backup compression is disabled by default. You can configure the backup compression at the instance level. In the Server Properties, go to Database Settings and check the checkbox for Compress backup.
Understanding Recovery Models
Database recovery models determine what your backup plan is and the strategy you implement to meet your Recovery Point Objective (RPO). RPO determines how much data loss your organisation can afford in the event of failure. The Recovery Time Objective (RTO) specifies a maximum time however it can take to restore data to the RPO.
SQL Server 2012 supports the following recovery model.
Simple – No transaction log backups are taken. You can restore data from recent database backup only.
Full – Full recover requires you to take transaction log backups and full backups. Depending on the type of failure, you can recover to the point of the full backup or the last point of transaction log backup.
Bulk-logged – This is a special type of recovery model that minimizes transaction log activity during bulk operations. You cannot perform point-in-time recovery when using this model.
To configure the recovery model for your database, go to Options page on the database properties and select the appropriate recover model for you. See screenshot below.
Alternatively, you can run the ALTER DATABASE statement with SET RECOVERY option.
All system database uses the simple recovery model by default. You can plan to backup your system databases on regular basis but not required to be as frequent as your full backup for your user database. It is important to backup the master and msdb system database regularly.
Backup for Mirrored Database
If you have mirroring session in place, you can back up only the principal database. When you are performing a backup of the principal database you cannot use the option BACKUP LOG WITH NORECOVERY.
Backup for Replicated Database
You should include the following in your backup strategy
- master, msdb and publication database at the publisher instance
- master, msdb and distribution database at the distributor instance
- master, msdb and subscription database at the subscriber instance
For your snapshot and transactional replication, you should set the sync with the Backup option on the publication and distribution database. This will ensure that the transactions in the publication database transaction log are not truncated until they have been backed up at the distribution database.
Viewing Backup History
Backup events are written to the SQL Server log from the backup source. You can view this by going to the SQL Server log file viewer. Alternatively you can run the query below to check if your backup jobs were successful.
SELECT bup.name, bup.type, bup.database_name, bup.backup_start_date, bup.backup_end_date
FROM msdb.dbo.backupset bup
ORDER BY bup.backup_start_date DESC
I will cover lesson for restoring SQL Server Database in my next post. Happy weekend!
Credits to Peter Kudlacz for featured image.