SQL Server 2008 Backup and Restore

  • Article

Here are some notes on "SQL Server 2008 Backup and Restore" I took while attending an advanced class on SQL Server taught by Paul Randal https://sqlskills.com/AboutPaulSRandal.asp).

Please note that, although these notes were taken during the class, I might have added some of my own (mis)interpretation :-). Always check your facts on Books Online (I try to provide links when applicable). As with anything else you get from a blog, never use any of this in production before you thoroughly validate it a test environment. Please keep in mind that some of those will be hard to follow without the right pre-requisite knowledge and the right context. Reading the post from top to bottom will help.

Overview

  • Full, log and diff backups most used
  • File and File Group backups less used
  • Many different ways to do backups - TSQL, UI, SMO
  • Don't have a backup strategy, have a restore strategy :-)
  • Recovery script created by most senior DBA
  • Recovery script tested by most junior DBA :-)
  • Always use WITH CHECKSUM in your backup

Parallel Striped and Multi-file

  • Round robin between multiple files, spreading the IO load
  • Useful for very large backups, where IO becomes a bottleneck
  • It's not proportional fill, it round robin
  • Backup size on each file is total size / number of files

Positions within the file

  • You can store multiple backups in a single backup file
  • Position 1 is the first backup, then 2, 3, etc.
  • You can have a FULL and multiple LOG in the same backup file
  • If you want to do this, do not use WITH FORMAT or WITH INIT
  • Don't confuse the syntax for file and position
  • On restore, FROM is the file, WITH FILE is the position
  • Can't delete a backup in the beginning of a file

Mirrored backups

  • Up to 4 total backups at once (1 plus 3 mirrors), hit database only once
  • They could go to different places (typically one local, one remote)
  • If one of them fails, it fails the entire backup

RESTORE HEADER_ONLY

  • Shows types, positions, size, dates, first/last log sequence numbers
  • HEADER_ONLY reports the total size of the mirror
  • For a MIRROR with 2 files, size is twice the backup size
  • For a striped with 2 files, size on each file is the full backup size

Full backups

  • Image with everything needed for recovery (or recovery starting point)
  • Actually includes the database and the trasaction log
  • Typically is followed by other types of backups (log or differential)
  • System does a checkpoint, reads data sequentially (no locks), reads log
  • Log included from start of the "read data" to end of the "read data"
  • Log might grow during a long full backup, since you can't truncate it
  • Backup does not change the data, compact, etc... Reads extent by extent.
  • Backup never causes blocking. Can cause contention, but not blocking

Log backups concurrent with full backup

  • Log backups can occur concurrently with full (SQL Server 2005 and later)
  • Log truncation cannot occur while the full backup is running
  • Log truncation will be deffered until the completion of the full backup

Full backup only

  • Can restore only to the full.
  • Cannot restore to point in time (STOPAT)
  • Enterprise customers will typically also do log backups
  • If you're not using log backups, make sure you are in simple recovery model
  • If you're in full recovery model AND do a full backup, you start keeping logs

Transaction log backups

  • Start by going into full recovery model AND doing a full backup
  • Changes since the last transaction log backup - incremental
  • Includes all the logs that are not already backed up (from first full or last log)
  • Case: You had a full at 1AM, log at 3AM, log at 5AM, full at 7AM
  • If you backup the log at 9AM, it will include all logs since 5AM, not 7AM.

Tail of the log backup

  • Disaster case, where data files become unavailable but log files are available
  • Special syntax to backup log without having the data files
  • Syntax is BACKUP LOG ... WITH NO_TRUNCATE
  • Described at https://support.microsoft.com/kb/253817
  • Does not work if you had bulk logged operations since last backup

Do not break the log chain

  • Must have the entire log chain since the last full
  • Full to tape for offsite (WITH COPY_ONLY to avoid breaking the chain)
  • Truncating the log breaks the chain

Log Backup with BULK mode

  • Log backup after minimally logged operations includes changed extents
  • Case: full mode, full1, log1, bulk mode, bulk ops, full mode, log2, log3.
  • Log2 backup will include changed extents
  • Cannot RESTORE ... WITH STOPAT between time of log1 and time of log2
  • Can RESTORE ... WITH STOPAT between full1 and log1, also between log2 and log3
  • Operations that are minimally logged: https://msdn.microsoft.com/en-us/library/ms191244.aspx

Differential

Backup Integrity Demo

Backup Compression

  • Backup Compression included with SQL Server 2008
  • How much compression? Depends on your data
  • Encrypted and already compressed data does not compress well
  • Will it be similar compression as <insert name here>? Probably very similar
  • Typically uses more CPU and results in smaller backups.
  • Typically takes less time to backup and restore. Bottleneck is usually IO.
  • Make sure the compression ratio is worth the CPU cost in the specific case.
  • See https://technet.microsoft.com/en-us/library/bb964719.aspx

Backup compression

  • Off by default on installation (can control by instance or by backup)
  • Backup compression always does backup checksums
  • Cannot mix compressed and uncompressed backups in the same media set
  • RESTORE does whatever is required
  • Only Enterprise Edition can compress. All versions can decompress.

Restore phases

  • 1) File creation and initialization
  • Do not drop the old database prior to restoring - saves time
  • Or use instant initialize
  • 2) Copy data and transaction log
  • 3) Redo (or roll forward)
  • 4) Undo (roll back or recovery

How to restore

  • Always go to the run book first
  • You don't want to learn the RESTORE syntax at this time
  • Having a script is a good thing, especially if lot of backups are involved
  • Trial restore on a Friday afternoon - better than testing during a disaster
  • Many will never test their recovery process until a disaster, which is sad
  • Set a log shipping to other site with a delayed restore (so you look at the past)

Restore options

Completion States

  • NORECOVERY - DB remains offline, additional logs can be restored
  • STANDBY - DB goes online/read only, additional logs can be restored
  • RECOVERY - DB goes online/read write, additional logs cannot be restored
  • Log shipping leverages STANDBY

Point in time restore

Partial database availability

  • Only makes sense if data is manually or automatically partitioned into FGs
  • Restore a subset of the filegroups to create a new, smaller database
  • Primary filegroup must be included
  • First restore uses PARTIAL
  • Be careful with these things... You must really know what you're doing.
  • Case: Partitioned table with filegroups, one FG becomes corrupted
  • Case: Can continue to work with other FGs, online restore of corrupted FG

Other concerns

  • VLDB : partition to minimize restore impact, just restore a FG
  • Media failure : You know exactly what failed, you can restore just that
  • Human failure : Hard to find exactly when the human error happened (like a dropped table)
  • You can try to figure out using the default trace (if in the current trace)
  • You might end up restoring multiple times with STOPAT to investigate
  • Restoring to alternate location: Passwords: https://support.microsoft.com/kb/246133
  • Restoring to non-enterprise: certain features make the restore not work
  • Data encryption: Need to make sure the certificates are there