Module 6 Backup of SQL Server 2008 R2 Databases

1. Module 6 Backup of SQL Server 2008 R2 Databases

2. Module Overview • Backing up Databases and Transaction Logs • Managing Database Backups • Working with Backup Options

3. Lesson 1: Backing up Databases and Transaction Logs • Performing a Full Database Backup • Working with Backup Sets • Using Backup Compression • Performing Differential Backups • Performing Transaction Log Backups • Demonstration 1A: Backing up Databases

4. Performing a Full Database Backup • Backup entire database • Backup active portion of log file BACKUPDATABASE AdventureWorks2008R2 TODISK= 'L:\SQLBackups\AW.bak' WITHINIT;

5. Working with Backup Sets • A Backup Set represents one backup of any type • Backup Sets are written to Media Sets • Consists of one or more tape or disk Backup Devices • Backups are striped over the devices • Tape and disk devices cannot be mixed • Backup devices and Media Sets are created the first time they are used • Every backup device has a header with meta data of the backup sets • Media Sets can be mirrored in Enterprise and Datacenter edition

6. Using Backup Compression Backup Compression: Restrictions: Introduced in SQL Server 2008 ü Compresses backup size on device ü Reduces I/O requirements, increases CPU usage ü Faster backups but importantly, also faster restores ü Cannot co-exist on media with uncompressed backups ü Cannot co-exist on tapes containing NT Backups ü

7. Performing Differential Backups • Backup the extents changed since the last full database backup • Store active part of the transaction log to be able to recover the database • Independent of other differential backups • Note: You cannot create a differential database backup if no full backup has ever been created BACKUPDATABASE AdventureWorks2008R2 TODISK='L:\SQLBackups\AW_Diff.bak' WITHDIFFERENTIAL, INIT;

8. Performing Transaction Log Backups • Backup the transaction log only • Backs up log from the last successfully executed log backup to the current end of the log • Truncates inactive log records unless options specified • Note: Database must be in full or bulk-logged recovery model BACKUPLOG AdventureWorks2008R2 TODISK='L:\SQLBackups\AW_Log.bak' WITHNOINIT;

9. Demonstration 1A: Backing up Databases In this demonstration you will see: • How to backup a database • How to use backup compression

10. Lesson 2: Managing Database Backups • Options for Ensuring Backup Integrity • Viewing Backup Information • Demonstration 2A: Viewing Backup History

11. Options for Ensuring Backup Integrity

12. Viewing Backup Information • SQL Server 2008 R2 tracks all backup activity in a set of tables in the msdb database • History can be accessed through T-SQL or SSMS • Information can be retrieved from backup media • RESTORE LABELONLY returns information about the backup media on a specified backup device • RESTORE HEADERONLY returns all the backup header information for all backup sets on a particular backup device • RESTORE FILELISTONLY returns a list of data and log files contained in a backup set

13. Demonstration 2A: Viewing Backup History • In this demonstration you will see: • How to view backup history using SSMS • How to query the backup history tables using T-SQL • How to use the RESTORE HEADERONLY command to retrieve backup set information

14. Lesson 3: Working with Backup Options • Backup Considerations • Copy-only Backups • Tail-log Backups • Demonstration 3A: Tail-log Backup

15. Backup Considerations • Backups are performed online • Do not prevent user access • Might slow down other operations due to I/O load • Database must be online for normal backup operations • Transaction log backups are still possible on a damaged database • Log file must be intact • Integration with operating system options • SQL Writer service provides backup functionality through the Volume Shadow Copy Service (VSS) framework • VDI interface enables ISVs to integrate backup and restore functionality into their products (commonly used for 3rd party backup tools)

16. Copy-only Backups • Backup the database without changing the restore order • Copy-only transaction log backups do not truncate the log • Copy-only full database backups do not affect the differential base • Note: Use COPY_ONLY for out of sequence backups BACKUPDATABASE AdventureWorks2008R2 TODISK='L:\SQLBackups\AW_Copy.bak' WITHCOPY_ONLY, INIT;

17. Tail-log Backups • Used to capture the tail of the log before starting a restore sequence • Performs a regular log backup • Options: • NORECOVERY when restore operations will follow (database set to RECOVERING state) • CONTINUE_AFTER_ERROR when data files are missing or damaged but log files are intact • Performs a regular log backup

18. Demonstration 3A: Tail-log Backup • In this demonstration, you will see how to backup the tail of the log of a damaged database

19. Lab 6: Backup of SQL Server Databases • Exercise 1: Investigate backup compression • Exercise 2: Transaction log backup • Exercise 3: Differential backup • Exercise 4: Copy only backup • Challenge Exercise 5: Partial backup (Only if time permits) Logon information Estimated time: 45minutes

20. Lab Scenario You have reviewed and updated the recovery models. As the database administrator, you need to implement a database backup strategy. You have been provided with details of the required backup strategy for a number of databases on a SQL Server instance. You need to complete the required backups.

21. Lab Review • What must be performed before you can create a differential backup of a database? • How does a copy only backup differ from a full database backup?

22. Module Review and Takeaways • Review Questions • Best Practices