Chapter 5. Backup and recovery
Backups are perhaps the most common task people think of when you say “server maintenance.” Without a doubt, they’re important. I have to be honest: many organizations don’t use SQL Server’s native backup facilities. Instead, they rely on other backup software, like Backup Exec, BrightStor, or even Microsoft System Center Data Protection Manager. Those solutions let a company manage all of its backups—including SQL Server—in a single place, whereas SQL Server’s native backup abilities only apply to SQL Server. This chapter won’t cover those other solutions. What it will cover is what’s built into SQL Server—and plenty of organizations do use that, even if they only use SQL Server to create a backup file that is subsequently picked up by another backup solution.
Before we can talk about backups, we have to talk about how SQL Server works, internally, and about its transaction log. This process is crucial to how backups work, along with a dozen other maintenance and performance-related tasks.
SQL Server stores all data in 8 KB chunks called pages. A page typically contains all of the data for a single row in a single table, and it can contain multiple rows, if they’re small. There are some exceptions: SQL Server supports certain data types that can span several pages, but for right now think of each 8 KB page as being the basic unit of management.