SQL Server Backup Types
New! Welcome to this new series of columns for working with SQL Server. First, let's talk backups.
- By Eric Johnson
- 10/08/2007
Welcome to this new series -- in this column, I hope to impart some quick, short tips that will help you make quick work of managing and administering your SQL Server databases. First up is an important topic: backups.
While backups are a basic task with little glamour, they remain an important component to any SQL Server installation. All too often, I see administrators who either don’t understand backups or who just don’t take the time to configure them appropriately for their environment.
Keep in mind that when you're backing up your SQL Server databases, there are three types of backups you can utilize:
- Full Backups
- Differential Backups
- Transaction Log Backups
There are some other options for file or filegroup backups, but their use is more advanced and we won’t cover them here. Let’s take a look at each of the backup types and how they work.
Full Backups
When you run a full database backup, everything in your database is backed up. This is the most basic form of backup in SQL Server and is required in order to use the other two types. Full backups are great, if not required, but for large databases can take a lot of time and that makes them an impractical option as an every hour or in some cases even an everyday backup solution.
Differential Backups
Differential backups will backup everything in your database that has changed since the last full backup. The keyword here is full, if you run differential backups everyday and full backups once a week, each differential will backup all the previous days changes back to your full backup. This can cause differential backups to become large, but they allow for quick recovery since you only need to restore the full and the latest differential backup.
SQL Server Help —
Just An E-Mail Away |
Need SQL Server troubleshooting help? Or maybe you want a better
explanation than provided in the manuals? Describe
your dilemma in an e-mail to the MCPmag.com editors
at [email protected];
the best questions get answered in this column and garner
the questioner with a nifty Redmond T-shirt.
When you send your questions, please include your
full first and last name and location with your message. (If you prefer to remain anonymous,
specify this in your message, but submit the requested
information for verification purposes.) |
|
|
Transaction Log Backups
Transaction log or t-log backups will backup any data that exists in your transaction log. In addition to backing up the transaction log, these backups also remove the data from the log. If you are using a simple recovery model on your database, you will not be able to perform t-log backups. However, if you are not using simple, a t-log backup is the only safe way, from a recoverability standpoint, to clear data out of your t-logs and prevent them from filling up and rendering your database useless.
Next time, we'll look at best practices when configuring backups.
About the Author
Eric Johnson, SQL Server MVP, is the owner of Consortio Services in Colorado Springs providing IT systems management and technology
consulting. He is also the President of the Colorado Springs SQL Server
User Group. He can be contacted at www.consortioservices.com.