Browse DevX
Sign up for e-mail newsletters from DevX


Choosing the Right Backup in SQL Server-3 : Page 3




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Backing Up the Database One File at a Time
A complete database backup is certainly a powerful and simple option. In the case of a very large database, however, a complete backup might simply take too long to be feasible.

Consider a database used in a data warehousing application. The database might easily approach several hundred gigabytes in size and, as such, take too long to back up in one operation. In such a case, SQL Server provides the option of backing up individual files or file groups.

For instance, this command will back up an individual file:

BACKUP DATABASE database_name FILE = file_name TO DISK = 'file_for_backup'

This command will back up all files in a particular group:

BACKUP DATABASE database_name FILEGROUP = file_group_name TO DISK = 'file_for_backup'

With these commands, you can set up a schedule where a different file or file group is backed up every evening, completing a full cycle in a week.

Besides shortening the length of time it takes to create a backup, a file backup can also make recovery quicker. If only a single file in your database has been lost, a file backup will allow you to simply restore that file. However, it is important to note that a file backup cannot be used by itself. In order for the database to be recovered to a consistent state (where all the files in the database represent the same moment in time), all transaction logs (see next section) made since the file backup was created must also be applied.

Listing 1 creates a test database with two file groups.

Backing Up Only the Changes to the Database
In addition to full backups, SQL Server provides two methods for backing up only the changes that have occurred to the database.

Transaction Log Backup: This command creates a copy of all the transactions in the transaction log at the time the backup was started:

BACKUP TRAN [database_name] to disk = [file_name]

Once the backup is completed, all committed transactions are removed from the log. A typical backup plan utilizing transaction backups might be to make a full backup of the database once a night (or even once a week on larger databases) and back up the transaction logs as often as every ten minutes. To recover the database under this scheme, you would start from the last full backup and apply all the transaction log backups since.

This type of scheme suffers from one problem, however. You need the full backup and every transaction log backup made up to the point when you have to recover. For example, suppose your backup scheme consists of a full database backup every Saturday at midnight and transaction log backups every hour. If your database has a problem on Thursday at midnight, you would need to restore the full database backup, as well as the 120 transaction log backups made from Saturday through Thursday, to get up to date.

For this reason, SQL Server provides yet another method of backing up changes to the database that is used in conjunction with full database and transaction log backups.

Differential Backup: A differential backup creates a copy of all the changes that have taken place in the database since the last full backup. The command is the same for a full database backup, with the addition of one clause:

BACKUP DATABASE [database_name] to disk = 'file_name' with differential

Now you can implement the following backup scheme: a full backup every Saturday, a differential backup every night, and a transaction log backup every hour. Once this scheme is in place, if you go down on Thursday evening, all you need to do is apply the full backup, the last differential backup (which would have been Wednesday evening), and all the transaction logs since—for a total of only 26 files.

This 10-Minute Solution covers the basics of the BACKUP command and the four types of backups that you can create. However, I haven't discussed some other additional features that SQL Server provides with this command, such as creating a backup device or password protection. Before implementing a backup plan, I suggest you perform additional research on these features by reading up on the BACKUP command in the Books Online manual. Additionally, you should remember to test your backup plan thoroughly by performing test restores on a regular basis.

Joe Lax has spent the last 10 years working in various database environments and has been a practicing DBA on all versions of SQL Server from version 4.2 onward. Joe is also a MCSE and an MCT. Recently, he has started to learn Oracle, which affords him no end of fun.
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date