One of the foundational responsibilities of a DBA is making sure that backups are available. SQL Server has several foundational types of backups that can be made. Before I dive into that though, I want to ask a question that seems to have a fairly obvious answer.
Why take backups?
- You want to be able to recover from data corruption or mistakes made when people make changes directly to the data - like when they write and run a script in SSMS, outside of an application that’s using a set of tested stored procedures.
- You want to be able to refresh you non-prod environments so developers have data that looks like prod to develop against.
- You want to have backups so you can test whether you can restore and how long it will take
- You want backups as they are often an essential part of the migration process from one SQL Server to another - usually newer SQL Server, or to Azure.
SQL Server Full Backups
What does a full backup accomplish? A full backup, creates a backup of the entire database. It is all-inclusive, containing the entire database structure and all the data. When a full backup starts, the Log Sequence Number is recorded and when the full backup completes, the Log Sequence Number is recorded. This Log Sequence Number is the mechanism used by SQL Server to know what order INSERTS, UPDATES or DELETES occurred in. As such, having the beginning and ending Log Sequence number recorded as part of the full backup allows for a transactionally consistent backup because the full backup is aware of the changes that took place while the backup was being made. This allows for recovery of those transactions during the restore process. This full backup serves as the base backup, or starting point for all differential and log backups after it. Each time a new full backup is taken, a new base backup point is created.
Continue reading
