MSSQL Database Backup

Every hour the database is down can add up to millions of money of business opportunities lost. To keep their business activities going, organizations deploy high-availability solutions, such as failover clustering, data mirroring, and log shipping, so that when a database server fails, they can continue to run their business on a standby database server.
-Types of Failure: Hardware Failure, User Error, Application Failure, Software Failure, Too Much Privilege, Local Disasters and many more.
-Making Plan: You should have a plan for high-availability, backup/recovery, and disaster recovery.
-Backup and Restore: SQL Server 2008 has several different backup and recovery processes that you can use, depending on the needs of your organization. Database backup is a procedure that safeguards your organization’s investment to reduce the amount of data loss. A database backup is the process of making a point-in-time copy of the data and transaction log into an image on either disks or tapes. Additionally, SQL Server 2008 R2 supports up to 64 concurrent backup devices. The following types of backup are available:
• Full backup: This is a copy of all data in the database, including the transaction log.
• Partial backup: This is a way to back up only those parts of the database that are changing. This reduces the size of the backup and the time it takes to backup and restore.
• File/filegroup backup: This is a copy of files or filegroups of a database. This method is typically used for very large databases for which it is not feasible to do a full database backup. A transaction-log backup is needed with this backup type if the backup includes read/write files or filegroups.
• Differential backup: This is a copy of all the data that has changed since the last full backup. Differentials are cumulative: If you do an entire backup on Sunday night, the differential taken on Monday night includes all of the changes since Sunday night.
• Partial differential backup: This works the same as a differential backup but is matched to data from a partial backup. It is a copy of all extents modified since the last partial backup. To restore requires the partial backup.
• File differential backup: This is a copy of the file or filegroup of all extents modified since the last file or filegroup backup.
• Copy-only backup: This can be made for the database or transaction log. The copy-only backup does not interfere with the normal backup restore procedures.
-Backup compression: A new feature in SQL 2008 enables the backup files to be stored in compressed form. This may achieve both shorter backup times and smaller files containing the backup. The trade-off you are making is increased CPU usage to do the compression, in exchange for less I/O due to the smaller files.
– Restore versus Recovery: The terms restore and recovery are often confused and misused. Restore is what occurs when you use the RESTORE T-SQL command to get a database back. Recovery is a process that brings a database into a consistent state. This means that
committed transactions are applied to disk (redo phase) and transactions that are begun but not yet committed are rolled off (undo phase). The result is a database that contains only committed transactions — a consistent state.

Leave a Reply

Your email address will not be published. Required fields are marked *