Choosing the Right Backup in SQL Server

hen I started using SQL Server in version 4.2, backups were relatively simple. There were only two possibilities: a full backup or a transaction log backup. Starting with SQL Server 7, additional choices have proliferated. You now have four ways to back up your database.

If you are responsible for maintaining SQL Server databases, you need to be familiar with these various options and know when to apply each backup type. In this 10-Minute Solution, I show you the commands to execute each different kind of backup and discuss how to choose among the various options you have.



How many different ways can you back up your SQL Server database?



SQL Server provides four different methods for backing up your database:

• A full backup makes a complete backup of your database. You will almost always need to start your backup strategy with a full backup of your database.
  
  
• A file backup is useful when your database is so large that a full backup would take too long.
  
  
• A transaction log backup creates a copy of all changes made to the database that are currently stored in the transaction log.
  
  
• A differential backup stores all changes that have occurred to the database since the last full backup.

It all starts with learning the basics of the BACKUP command.