Different types of SQL Server Database Backups
There are different types of SQL Server Database Backups available. The method which the Database Administrator choose to backup databases depends up the kind of data that they are planning to backup and how fast they want the recovery process to be.
Full Backup
A full backup internally backs up the entire database, which includes part of the transaction log, so that the entire database can be recovered to the point in time at which the backup was completed. Formerly it was also known as full database backup. Full backups consume lot of system resources and the users connected to the database will feel that the processing is happening very slowly in the database, DBA needs to make sure that full backups are performed whenever the server is very less utilized during the day. The backup files which are generated using the full backup are very large when compared to differential and transaction log backups. It’s very important for the DBA to analyze and understands the amount of free space required for full backups on the server. During a full backup, the backup operation basically copies only the data that is available in the database to the backup file. The free or unused space which was available in the database is completely discarded. DBA can estimate the size of full backup by using the sp_spaceused system stored procedure.
Differential Backups
Differential backups basically contain changes which were made to the database since the last full backup. A differential backup usually takes lesser time to complete as it contains only the changes (delta) which has happened to the database since the very last full backup. It’s much faster when compared to full database backups. When you have very large databases then you should be using this backup strategy to reduce the work loss and it should be schedules at lesser time intervals when compared to full backups.
Transactional Log Backups
When the database is in the full or bulk logged recovery model, DBA can take the transactional log backups. Transactional log backup basically takes the backup of all the active transactions within the transaction logs and removes the inactive transactions from the transaction logs. The correct sequence of transaction log backups helps DBA to restore the database to any point in time during disaster recovery.
Full File Backups
A full file backup basically backs up all the data stored in one or more files or filegroups. The main advantage of using Full File backups is that you can restore only the damaged file which incredible improves the recovery time. They are much similar to normal file backups.
Copy Only Backups
When Copy Only backup option is used it doesn’t break the sequence of SQL Server backups. This is very useful when DBA has to take the backup of the database without affecting the backup and restore sequence of the SQL Server database.
Conclusion
Database Administrators need to effectively plan the database backups for all the user and system databases including the resource database which was introduced in SQL Server 2005. For mission critical databases one needs to plan a combination of Full, Differential and Transaction Log Backups keeping in mind the point in time recovery for the database.
]]>