TechTutorials - Free Computer Tutorials  

SQL 7.0 Backup & Restore Types 

Added: 07/31/2001, Hits: 3,708, Rating: 0, Comments: 0, Votes: 0
Add To Favorites | Comment on this article
By Brian Talbert

Unlike the SQL 6.5 exams, there is little concentration on the use of Transact-SQL syntax to perform routine operations such as backup and restore. However, the exam does still concentrate on these processes. The focus tends to be on recognizing different backup strategies and determining the appropriate restore procedures based on those strategies. What follows is a summary of SQL 7.0 backup and restore capabilities.

Full Backup
As the name implies, the full database backup does a complete backup of the entire contents of your database. In SQL 7.0 it will even capture changes that occur to the database during the backup operation. It does this by backing up a portion of the transaction log as well. Note, however, that you must still backup your transaction logs separately. Full Database backups do not provide for transaction log management (truncation).

Prior to being able to use other backup methods, you must first perform a full database backup. A full backup acts as a baseline for the other methods.

Differential Backup
Each page in a database contains a Log Sequence Number (LSN). Each transaction entered into the transaction log gets a LSN. When a differential backup starts, SQL Server looks at the highest LSN number of the last full backup. It then proceeds to go through the database. When it encounters a page with an LSN higher than that from the last full backup it backups up the entire extent that contains the modified page. In this way, the differential backup captures just the changes to the database since the last time a full backup was completed.

If you think about how this works, you will realize that as successive differential backups are performed they continue to get larger and larger, until the next full backup is completed. In other words, if you do a full backup on Monday and a differential on every other day, Tuesday's differential will contain only changed data from Tuesday, Wednesday's will contain data from Tuesday and Wednesday, and so on.

The benefit of Differential backups is that the length of time necessary to both backup and restore your data is reduced. In comparison to full backups, differentials take less time since you are only backing up changes. When it comes to restores, differential reduce time by allowing you to eliminate many log backups; it is only necessary to restore the most recent differential backup and an logs since then.

Transaction Log Backup
A transaction log backup backs up just the transaction log and also truncates the inactive portion of the log. Because the transaction log gets truncated each time, a transaction log backup only contains changes since the last transaction log backup, effectively making it operate like an incremental backup.

An important note here is that when restoring transaction logs you must restore all transaction logs and they must be restored in order.

Another important note is an option to backup the transaction log without actually truncating the inactive portion of the log (NO_TRUNCATE). This should be considered a part of any recovery procedure. When a failure occurs to the primary database file, you can possible recover up to the minute of failure, but only if you have a backup of the current, non truncated, transaction log.

Conversely, there may be a time when a transaction log becomes full. When this occurs, your database can not accept updates. You therefore need to clear the transaction log using the TRUNCATE_ONLY option. Make sure you backup your database afterwards. Also note that in SQL 7.0 there is no difference between TRUNCATE_ONLY and NO_LOG, both are non-logged operations now.

Database File/File Group Backup
With SQL 7.0 it is possible to backup individual database files or database file groups separately from the rest of the database. This is an extremely beneficial feature for very large databases (VLDBs ... an acronym for everything!). If you database is 400 gigs in size, yet your backup device is only capable of backing up 100 gigs at a time, or if it is too slow to complete a full backup, you can instead backup just individual database files. So, say your database consists of 4 100 gig files. In this scenario, you could backup file 1 on Monday, file 2 on Tuesday, file 3 on Wednesday, and file 4 on Thursday. Then start the rotation over again. >

It is very important to realize that you absolutely must have good transaction log backups for this to work well. Consider what happens when you must restore just 1 of the 4 files. That 1 file is now out of synch with the others. In order to preserve integrity you must be able to restore all transaction logs so that all files are in synch.

Also note that if you have indexes in a file or filegroup separate from the table they reference, and a change occurs that affects both the table and index, you must backup all affected files or filegroups together.

Comments (0)

Be the first to comment on this article

Related Items

7 Seconds Resources, Inc.

IT Showcase