10 Backups

 

It’s hard to overstate just how important backups are to data professionals. Not only can backups help businesses recover from data loss, they can even stop a business from going under in the event of a disaster.

Data is always at risk of loss, whether through accidental deletion, intentional sabotage, natural disasters, malware, or other threats. That is why well-developed backup and backup-testing plans are arguably one of the most important aspects of business continuity.

Backups can also help us evolve and accelerate our day-to-day operations. They can be used to provide a way for developers to easily reset databases to a known state and can be integrated into DevOps practices as part of automated testing pipelines. They also help us when we perform side-by-side migrations or need to rebuild an entire server.

dbatools can help simplify managing all three types of SQL Server backups, defined in table 10.1.

Table 10.1 Backup types (view table figure)

Backup type

Description

Example schedule

Full

Backs up the entire database.

Weekly

Differential

Backs up database changes since the last full backup was performed.

Daily

Log

Backs up the transaction log; in addition to enabling point-in-time restores, transaction log backups keep your log files reasonable in size.

At least hourly

10.1 Creating backups

10.1.1 Azure

10.1.2 Docker

10.2 Reading backup files

10.3 Backup history

10.4 Pruning old backup files

10.5 Testing your backups

10.6 Hands-on lab