Today Paul talked the whole day about HA DR Strategies and Backup/Restore operations.

I've already known a lot about Backup/Restores, but Paul showed some cool things that

were even new to me J. Here are my study

notes for the 3 modules of today:

Module 1: HA DR Strategies

• Zero downtime is not possible
  • Even not mit Live Migration
• Failover Clustering
  • Server is protected
  • Data is not protected
• RPO - Recovery Point Objective
  • Maximum allowable data-loss
  • Zero data-loss is possible through synchronous Database Mirroring or synchronous SAN

    replication

• What is the transaction log generation rate of your workload?
  • Impacts
    • Database Mirroring
    • Log Shipping
    • Replication
    • Log File Size
• Recovery Model
  • Database Mirroring needs FULL
  • Log shipping works with FULL and BULKLOGGED

Module 2: Backups

• Don't plan a backup strategy, plan a restore strategy!!!
• When a Full Database Backup starts, a CHECKPOINT occurs
  • CHECKPOINT generates log records
• Concurrent Full Backups and Tx Log Backups are possible
  • Log Clearing from the Tx Log Backup is deferred until the concurrent Full Backup is

    completed

• DBCC SQLPERF (LOGSPACE)
• Only a log backup clears the transaction log in the FULL recovery model
• Reverting from a database snapshot breaks the backup chain
• While a log backup is running you can't do a minimally logged operation in the

  bulk logged recover model

  • The ML bitmap mask can't be changed in the mean time
• Tail Log backup in the bulk logged recovery model also needs the data files (needs

  to backup the changed extents)

  • They must be accessible, not only the transaction log
  • Tail Log backup can be performed, but it leads to a corrupt database
• Backup chain is not broken, when you switch between full <> bulk logged <>

  full

• Backup chain is only broken when you switch to the SIMPLE recovery model
• Database Snapshot can't be created on a database in the RESTORING state because Backup/Restores

  aren't going through the Buffer Pool

• SQL Server can't rebuild a partition, when they are spread across filegroups,

  where some filegroups are readonly

  • Therefore you can use Partitioned Views to union the Read/Write and the ReadOnly filegroups
• When you do a Full backup of a readonly filegroup, nothing else (diff, log backup)

  has to be done

  • Just back it up once, and you're done
• COPY_ONLY
  • Also possible with transaction log backups - will not clear the transaction log
• For each backup device a writer thread will be created
  • For the restore a seperate reader thread will be created for each backup device
• Mirrored Backups
  • When a mirror disappears, the entire operation fails
• CONTINUE_AFTER_ERROR
  • Forces Backups & Restores of corrupt databases
• Backup Compression new on SQL Server 2008 Enterprise Edition
  • SQL Server 2008 R2 also includes it in Standard Edition

Module 3: Restores

• Files can be only set OFFLINE, but you have to restore from backup to get it ONLINE
• Prevent that Backup Compression preallocates space
  • Trace Flag 3042
  • kb2001026
• STOPAT does nothing on a full or diff backup restore
  • It's just syntetical reason
• After STOPAT a full database backup should be taken to create a new well-known

  recovery starting point

  • Otherwise the restore sequence begins at the initial full backup and all subsequent

    restores must use STOPAT, which is a way more complicated

• fn_dump_dblog
  • Look into a backup
  • 64 parameters!!!
• STOPBEFOREMARK
  • Stops the restore process on a specific LSN
• Piecemeal Restore
  • PRIMARY filegroup must be restored with the PARTIAL option
  • E.g. Single Page Restore when a page is corrupt
  • Only possible in the FULL or BULK LOGGED recovery model
  • Must start with the PRIMARY file group
• sp_delete_backuphistory
  • Backup history tables in msdb must be cleaned up manually
• Startup parameter -Q
  • Starts SQL Server without creating TempDb
  • Can be used when the model database is damaged, and SQL Server can't create a copy

    of it for TempDb

• xp_readerrorlog
• Option RESTART
  • Skips steps during restore, when they are already done
  • Writes a .CKP file that stores the different phases
  • Stores in the default specified Backup folder
  • TF 3004: Instant File Initialization
  • TF 3014: What's going during restores
  • TF 3605: Print out what caused on TF 3004 and TF 3014

Thanks for reading

-Klaus