Sql Server Backup And Recovery

SQL Server Backup and Recovery

Presented by Scott Whigham

• Backup Strategies • Backing Up Your Databases • Backing Up Your SQL Server

Restoring Databases

• Overview of Chapter • Scenario Setup • Types of SQL Server Backups • Recovery Models

Backup Your Databases

Overview

What We’re Going to Cover

• Restoring User Databases • Restoring System Databases

Chapter Overview

• Lack of backups can break a company • As the DBA, it is your responsibility to create backup and recovery plans and test those plans – Perform these tests early and often for any critical project

Chapter Overview

• This chapter will teach you how to: – Develop backup plans – Understand the risks associated with any single plan – Take and restore backups

Chapter Overview

• This chapter will not teach you how to: – Implement Database Mirroring, Log Shipping, Replication, or Snapshots – Discuss creating or managing jobs and logs

• Backup Strategies • Backing Up Your Databases • Backing Up Your SQL Server

Restoring Databases

• Overview of Chapter • Scenario Setup • Types of SQL Server Backups • Recovery Models

Backup Your Databases

Overview

Next up…

• Restoring User Databases • Restoring System Databases

Scenario Setup

• Before we get too deep, we need a real world example to use • For the scenarios in this chapter, you are the database administrator for LearnItFirst.com

Scenario Setup

• LIFWeb: Manages online website – Small-to-medium size (100GB) – Real-time data and transactions – Few bulk transactions (indexing only) – Backup and restore goals: • ___________________________ • ___________________________

Scenario Setup

• LIFWeb: Manages online website – Real-time data and transactions – Few bulk transactions (indexing only) – Backup and restore goals: • Be able to restore quickly • Ensure no lost transactions • Be able to recover to “point of failure” and “point in time”

Scenario Setup

• LIFUtility: Internal support – Very small (100MB) – Static data (numbers tables, functions, procs) – No bulk transactions – Backup and restore goals: • ___________________________ • ___________________________

Scenario Setup

• LIFUtility: Internal support – Very small (100MB) – Static data (numbers tables, functions, procs) – No bulk transactions – Backup and restore goals: • Backup/restore full database only • Point in time recovery is not important

Scenario Setup

• LIFReporting: OLAP database to feed SQL Server Analysis Services – Large (400GB) – Mainly bulk transactions loaded on weekends • indexing, text files, XML

– Not Mission Critical – Backup and restore goals: • ___________________________ • ___________________________

Scenario Setup

• LIFReporting: OLAP database to feed SQL Server Analysis Services – Large (400GB) – Mainly bulk transactions loaded on weekends • indexing, text files, XML

– Not Mission Critical – Backup and restore goals: • Minimize backup size • No need for point in time or point of failure recovery

• Backup Strategies • Backing Up Your Databases • Backing Up Your SQL Server

Restoring Databases

• Overview of Chapter • Scenario Setup • Types of SQL Server Backups • Recovery Models

Backup Your Databases

Overview

Coming up…

• Restoring User Databases • Restoring System Databases

SQL Server Backup and Recovery

Presented by Scott Whigham

• Backup Strategies • Backing Up Your Databases • Backing Up Your SQL Server

Restoring Databases

• Overview of Chapter • Scenario Setup • Types of SQL Server Backups • Recovery Models

Backup Your Databases

Overview

What We’re Going to Cover

• Restoring User Databases • Restoring System Databases

Types of SQL Server Backups

• The first thing to know is what you can back up – Entire database (all files - data and log) • Called a “Full” backup

– Log only • Called a “Transaction Log” backup

– Only the data that has changed since the last Full backup • Called a “Differential” backup

– Filegroups

Dispelling Myths

• Dispelling myths – You do not need to take a database offline to back it up – Backups will include open transactions – All backups include the transaction log – You cannot just backup a table by default • “All or nothing” • One exception: if that table is on a separate filegroup

• Backup Strategies • Backing Up Your Databases • Backing Up Your SQL Server

Restoring Databases

• Overview of Chapter • Scenario Setup • Types of SQL Server Backups • Recovery Models

Backup Your Databases

Overview

What We’re Going to Cover

• Restoring User Databases • Restoring System Databases

Recovery Models

• The next thing to know is what Recovery Models are • A database’s recovery model determines – What you can backup (and subsequently restore) – How the backup process works – How often the transaction log is cleared out

Recovery Models

• There are three types of recovery models – Simple – Full – Bulk Logged

• Each has specific scenarios for use • Can switch between them instantly

Recovery Models

Simple Can backup transaction log? Transaction log is “cleared out” automatically? Transaction log grows until manually cleared out? Backs up in-progress transactions? Backs up bulk transactions? Completely logs bulk transactions? Supports point in time recovery?

Bulk

Full

Simple Recovery Model

• The Simple recovery model: – Automatically clears committed transactions from the log • By default, this happens about 1x per minute • Prevents the log from filling up and auto-growing

– Cannot backup the transaction log directly

Simple Recovery Model

• The Simple recovery model is appropriate when: – You do not need “Point in time” recovery – You want to minimize disk space – The default for the master, msdb, and tempdb – Perfect for static, test and development databases

Scenario

• Which of our databases is appropriate for using the Simple recovery model?

Scenario

• Which of our databases is appropriate for using the Simple recovery model? – LIFUtility because it is static and we do not need point in time recovery

Full Recovery Model

• The Full recovery model: – Allows you to restore to a specific point in time or to the point of failure • “I need to restore everything up until 12:15:35 - that’s when we know that Scott ran that nasty UPDATE statement that messed everything up!”

– For this reason, most production databases are Full recovery databases • Least risk of losing work!

Full Recovery Model

• The reason that you can restore to a point in time is that you can restore the log – You can only restore to point in time or point of failure from a transaction log backup, not from a Full or Differential

Scenario

• Which of our databases is appropriate for using the Full recovery model?

Scenario

• Which of our databases is appropriate for using the Full recovery model? – LIFWeb because it changes constantly and we do not want to lose any work

• Backup Strategies • Backing Up Your Databases • Backing Up Your SQL Server

Restoring Databases

• Overview of Chapter • Scenario Setup • Types of SQL Server Backups • Recovery Models

Backup Your Databases

Overview

In the next video…

• Restoring User Databases • Restoring System Databases

SQL Server Backup and Recovery

Presented by Scott Whigham

• Backup Strategies • Backing Up Your Databases • Backing Up Your SQL Server

Restoring Databases

• Overview of Chapter • Scenario Setup • Types of SQL Server Backups • Recovery Models

Backup Your Databases

Overview

In this video…

• Restoring User Databases • Restoring System Databases

Bulk Logged Recovery Model

• The Bulk Logged recovery model: – Is identical to Full recovery model except when dealing with bulk operations

Recovery Models

Simple Can backup transaction log? Transaction log is “cleared out” automatically? Transaction log grows until manually cleared out? Backs up in-progress transactions? Backs up bulk transactions? Completely logs bulk transactions? Supports point in time recovery?

Bulk

Full

Bulk Operations in SQL Server 2008

• Certain statements and tools are considered bulk operations – CREATE INDEX and ALTER INDEX – SELECT INTO – BULK INSERT – OPENROWSET() – bcp

Bulk Operations in SQL Server 2008

• Bulk operations can be – Fully logged - transaction is logged first and then written to the data file – Minimally logged - minimal information is written to the log first and then entire transaction is written directly to data file

Bulk Operations in SQL Server 2008

• In the Full recovery model, ALL transactions are fully logged – This means that your bulk imports/loads/operations are incurring potentially unnecessary overhead

Bulk Operations in SQL Server 2008

• In the Bulk Logged recovery model, bulk operations are minimally logged – No performance overhead associated with logging means faster performance

• So if Bulk Logged is faster for bulk operations, why don’t we always use it?

Bulk Logged Recovery Model

• The Bulk Logged recovery model, while optimized for bulk operations, has a serious “gotcha” Simple

Bulk

Backs up in-progress transactions? Backs up bulk transactions? Completely logs bulk transactions? Supports point in time recovery?

* *

Full

The “Gotcha”

• You cannot backup the log if: 1. You are using Bulk Logged recovery model 2. … and the data file is missing 3. … and bulk operations have occurred since the last backup

Bulk Logged Recovery Model

• Let me recap: – Bulk Logged and Full recovery models are identical provided no bulk operations have occurred since the last backup – Bulk Logged recovery model may prevent you from backing up your log

Bulk Logged Recovery Model

• Why? You need to know the architecture of the transaction log! – Let’s do that in the next video…

• Backup Strategies • Backing Up Your Databases • Backing Up Your SQL Server

Restoring Databases

• Overview of Chapter • Scenario Setup • Types of SQL Server Backups • Recovery Models

Backup Your Databases

Overview

In the next video…

• Restoring User Databases • Restoring System Databases

SQL Server Backup and Recovery

Presented by Scott Whigham

• Backup Strategies • Backing Up Your Databases • Backing Up Your SQL Server

Restoring Databases

• Overview of Chapter • Scenario Setup • Types of SQL Server Backups • Recovery Models

Backup Your Databases

Overview

In this video…

• Restoring User Databases • Restoring System Databases

The Transaction Log, Part 1

• SQL Server uses “write ahead logging”

The Transaction Log, Part 1

• SQL Server allocates space an extent at a time – An extent is a collection of eight 8kb pages

The Transaction Log, Part 1

• When you have minimally-logged transactions, the only thing written to the log is the extents that were affected – Detailed row information is not written

The Transaction Log, Part 1

• Demo of a minimally logged transaction

Bulk Logged Recovery Model

• Name two advantages of using the Bulk Logged recovery model – _________________________ – _________________________

Bulk Logged Recovery Model

• Name two advantages of using the Bulk Logged recovery model – Faster bulk operations/loads – Minimize log space – Same protection as Full recovery model unless bulk operations have occurred

The Transaction Log, Part 1

• However… you do have to pay the price for the upload at some point – Detailed row information is not written during writes to the log thus minimizing overhead and log space – Detailed row information is written at log backup! • Expect your transaction log backups after a bulk operation to be large and time-consuming

Scenario

• Which of our databases is appropriate for using the Bulk Logged recovery model?

Scenario

• Which of our databases is appropriate for using the Bulk Logged recovery model? – LIFReporting because is loaded primarily using bulk operations – However, we likely will use Full recovery model most of the time and switch to Bulk Logged when using bulk operations • Remember that we can switch between them as needed

Switching Between Recovery Models

• You can switch between recovery models as needed – It is common to use FULL as your production model and switch to BULK LOGGED only during bulk operations – If you switch to SIMPLE, backup the log first – If you switch from SIMPLE, requires FULL or DIFF to actually take effect

• Backup Strategies • Backing Up Your Databases • Backing Up Your SQL Server

Restoring Databases

• Overview of Chapter • Scenario Setup • Types of SQL Server Backups • Recovery Models

Backup Your Databases

Overview

In the next video… Demo!

• Restoring User Databases • Restoring System Databases

SQL Server Backup and Recovery

Presented by Scott Whigham

• Backup Strategies • Backing Up Your Databases • Backing Up Your SQL Server

Restoring Databases

• Overview of Chapter • Scenario Setup • Types of SQL Server Backups • Recovery Models

Backup Your Databases

Overview

In this video…

• Restoring User Databases • Restoring System Databases

Scenarios

• Let’s walk through a few backup strategies using each recovery model • One caveat: we are not discussing the “how to” or the “why” of the restore yet

The Basics of Restoring Databases

• However you need to know a few basics: – You must always start with a FULL backup – You may only restore a single DIFFERENTIAL • Remember that a DIFFERENTIAL contains all changes since the last FULL backup • You do not need to restore any log backups taken before your DIFFERENTIAL

The Basics of Restoring Databases

• Basics of restoring LOG backups: – Logs must be restored in sequence – You cannot skip logs during the restore – You cannot skip transactions during a restore – It is possible to restore only part of the last log file you restore • Achieves “point in time” and/or “point of failure” recovery

The Basics of Restoring Databases

• Basics of restores: – The final backup you restore will, by default, take the database through the RECOVERY process and bring the database ONLINE • You decide which is the final backup file in your Transact-SQL code

– Any open transactions in the database will be rolled back during the RECOVERY process

The Basics of Restoring Databases

• One final wrinkle: – In the FULL and BULK LOGGED recovery model, you can back up the transaction log even if the data file is corrupt/suspect/missing • The only exception is when using BULK LOGGED recovery model and a bulk operation has occurred since the last backup

– This is how to do “point of failure” recovery!

The Basics of Restoring Databases

• Reminder: we will discuss all of this in detail later in the chapter

The Basics of Restoring Databases

• Example restore: – FULL backup: from Monday: 0800 – LOG backups: Monday: 0900, 1000, 1100 – DIFF backup: Monday 1200 – LOG backups: Monday: 1300, 1400, 1500

The Basics of Restoring Databases

• Example restore (cont.): – At 1430, user “Steve” notifies you that a recently applied application hotfix has “messed up the whole database” • You: “Okay, Steve - when was the hotfix applied?” • Steve: “We’re not exactly sure - sometime around 12:30”

– You and your team decide to restore up through 1220 (i.e. “point in time” restore)

The Basics of Restoring Databases

• Example restore (cont.): – What is your strategy to restore up through 1220? • Step 1: _____________________________ • Step 2: _____________________________ • Step 3: _____________________________

The Basics of Restoring Databases

• Example restore (cont.): – What is your strategy to restore up through 1220? • Step 1: Restore the most recent FULL • Step 2: Since the DIFF occurred before the stop time of 1220, restore the most recent DIFFERENTIAL • Step 3: Restore the 1300 LOG backup but tell SQL Server to STOP AT 1220

The Basics of Restoring Databases

• Example restore (cont.): – Since your last transaction in the restored database occurred at 1220, what happened to all transactions after 1220? ___________________ – What happened to any open transactions that were open as of 1220? _____________________

The Basics of Restoring Databases

• Example restore (cont.): – Since your last transaction in the restored database occurred at 1220, what happened to all transactions after 1220? Never occurred so “lost” – What happened to any open transactions that were open as of 1220? Rolled back in RECOVERY

Confused?

• It’s okay if you did not fully understand the scenario • We will thoroughly review restores including how to STOP AT a specific time later in this chapter

SQL Server Backup and Recovery

Presented by Scott Whigham

73

• Backup Strategies • Backing Up Your Databases • Backing Up Your SQL Server

Restoring Databases

• Overview of Chapter • Scenario Setup • Types of SQL Server Backups • Recovery Models

Backup Your Databases

Overview

In this video…

• Restoring User Databases • Restoring System Databases

Determining your backup strategy

• Let’s walk through backup strategies and restore scenarios • How do we determine the optimal backup strategy? What factors do we have to consider? ________________________________ ________________________________ ________________________________ ________________________________ ________________________________ ________________________________

Determining your backup strategy

• Factors to consider: 1. 2. 3. 4. 5. 6. 7.

Size of database Time it takes for each backup type to complete How much “drag” backups place on the system How much disk space our backups consume Where to put the backups? How much “loss” is acceptable How much downtime is acceptable

Factors to consider

1. Size of database – Large databases are problematic: • Backup time can be hours and hours – Therefore the restore time can be hours and hours

• Backup file sizes are huge – In SQL Server 2008 Enterprise Edition, backup files can be compressed (more on this later)

• Since the backups are so massive, they have an impact on system performance

– Smaller and mid-size databases do not have as much to worry about

Factors to consider

2. Time it takes for each backup type to complete – Some sites have downtime - they work business hours and therefore full system backups can occur during “off” hours with no impact – Many sites require maintenance windows to perform full system backups • Large database backups often do not “fit” your window!

Factors to consider

3. How much “drag” backups place on the system – A rule of thumb for most scenarios/databases: • FULL backups often have the most performance impact • DIFFERENTIAL backups have the next-most performance impact • LOG files, if taken often, will have the least impact

– This does not cover all situations

Factors to consider

4. How much disk space our backups consume – By default, SQL Server will do no compression on backups • Large databases means large backups • A SQL Server backup only contains the data, not the “empty” pages – If you have a 100GB database with only 10GB of data, your FULL backup will be ~10GB

– SQL Server 2008 Enterprise has the option to compress your backups (more on this later)

Factors to consider

5. Where to put the backups – You can backup to: • Disk - local and remote • Tape - local only

– Disks are fast and tapes are slow – Often the strategy is: • • • •

Backup to local or network disk Copy disk copy to tape Move/rotate tape offsite Delete disk backup according to a schedule

Factors to consider

5. Where to put the backups (cont.) – Backing up across a network often adds overhead • If time is a problem, backup local and do a network file copy later

– Do not store backups on same disks as data or log files • For performance reasons • For recovery reasons (more on this later)

Factors to consider

6. How much “loss” is acceptable – Strange factor, eh? – All backup strategies want to minimize data loss • Impossible to completely prevent the possibility that data loss can occur • Saying that “Data loss is impossible” is akin to saying, “My network is so secure no could ever hack into it”

– One technique for minimizing data loss is taking frequent backups • Some sites take LOG backups every 10 minutes

Factors to consider

6. How much “loss” is acceptable (cont.) – Backups are not the only strategy in play – High Availability solutions such as Database Mirroring, Log Shipping, and Replication are options we will discuss in a future chapter

Factors to consider

7. How much downtime is acceptable – All backup strategies want to minimize downtime • Just like with data loss, it is impossible to completely prevent the possibility that downtime can occur

– High availability solutions (including clustering) offer protection against downtime

Factors to consider

7.

How much downtime is acceptable (cont.) – If you do have to completely restore a database, “It takes as long as it takes” • A 1TB FULL backup is going to take a long time to restore…

– There are strategies that minimize restore time: • Frequent DIFFERENTIAL backups minimize the number of LOG backups that have to be restored, for example • Certain software packages minimize backup and restore times • SQL Server 2008 Enterprise Edition includes a feature called Fast Recovery

Factors to consider

7. How much downtime is acceptable (cont.) – SQL Server 2008 Enterprise Edition includes a feature called Fast Recovery • This allows users to access the database before the restore has rolled back uncommitted transactions (i.e. before the Undo phase of the restore has completed)

Determining your backup strategy

• The challenge is: “How do we create a backup strategy that protects us from data loss while also ensuring minimal downtime during a restore?”

Determining your backup strategy

• For a great article on specifically how the restore process works, visit http://msdn.microsoft.com/library/ms191455.aspx

• Article: “Understanding How Restore and Backups Work in SQL Server”

SQL Server Backup and Recovery

Presented by Scott Whigham

90

• Backup Strategies • Backing Up Your Databases • Backing Up Your SQL Server

Restoring Databases

• Overview of Chapter • Scenario Setup • Types of SQL Server Backups • Recovery Models

Backup Your Databases

Overview

In this video…

• Restoring User Databases • Restoring System Databases

Scenario Reminder

• Remember our three databases: – LIFWeb: Manages online website • Small-to-medium size (100GB) • Full recovery model

– LIFUtility: Internal support • Very small (100MB) • Simple recovery model

– LIFReporting: OLAP database to feed SQL Server Analysis Services • Large (400GB) • Full recovery model and switch to Bulk Logged when using bulk operations

Example #1

Backup Strategy: • Full backups on Saturdays at 2300 Database: LIFUtility Recovery model: SIMPLE

• If a problem occurs on Friday at 1200, can you back up the log so that you can achieve “point of failure” recovery? Explain your answer – ______________________________________________

Example #1

Backup Strategy: • Full backups on Saturdays at 2300 Database: LIFUtility Recovery model: SIMPLE

• If a problem occurs on Friday at 1200, can you back up the log so that you can achieve “point of failure” recovery? Explain your answer – No - Simple recovery model does not allow log backups

Example #2

Backup Strategy: • Full backups on Saturdays at 2300 Database: LIFUtility Recovery model: SIMPLE

• Since you cannot backup the log, what is your restore sequence? – ______________________________________________

Example #2

Backup Strategy: • Full backups on Saturdays at 2300 Database: LIFUtility Recovery model: SIMPLE

• Since you cannot backup the log, what is your restore sequence? – Restore the FULL backup from Saturday – Any data added or modified after Saturday night’s backup will be lost

Backup Strategy

• Are there any situations in which this is acceptable? – Read-only databases – Databases whose updates occur on Saturday mornings only – Test databases – “Junk” databases

Backup Strategy

• What are some strategies to employ if that much potential data loss is not acceptable? – More frequent backups – Changing the recovery model – Adding high availability options

Backup Strategy

• What are some other strategies to employ if that much potential data loss is not acceptable? (cont.) – More frequent backups – Changing the recovery model – Adding high availability options

Example #3

Backup Strategy:

Database: LIFUtility Recovery model: SIMPLE

• Full backups on Saturdays at 2300 • Differential backup Wednesday at 2300

• How does adding in differential backups each night change your restore sequence? – ______________________________________________

Example #3

Backup Strategy:

Database: LIFUtility Recovery model: SIMPLE

• Full backups on Saturdays at 2300 • Differential backup Wednesday at 2300

• How does adding in differential backups each night change your restore sequence? – Step 1: Restore the FULL from Saturday – Step 2: Restore Wednesday night’s DIFFERENTIAL – Any data added or modified after Wednesday night’s backup will be lost

In the next video…

• What are some other strategies to employ if that much potential data loss is not acceptable? (cont.) – More frequent backups – Changing the recovery model – Adding high availability options

SQL Server Backup and Recovery

Presented by Scott Whigham

103

• Backup Strategies • Backing Up Your Databases • Backing Up Your SQL Server

Restoring Databases

• Overview of Chapter • Scenario Setup • Types of SQL Server Backups • Recovery Models

Backup Your Databases

Overview

In this video…

• Restoring User Databases • Restoring System Databases

Backup Strategy

• What are some other strategies to employ if that much potential data loss is not acceptable? (cont.) – More frequent backups – Changing the recovery model – Adding high availability options

Simple Recovery Model

• Simple recovery model has a huge “hole”: – You cannot perform a “point of failure” restore – Why? • You cannot backup the transaction log • You cannot restore only part of a FULL or DIFFERENTIAL • You can restore only part of a LOG

Crash Procedure for Simple Recovery Model

• Your restore sequence in Simple recovery model is: • Step 1: Restore your most recent FULL backup • Step 2: Restore your most recent DIFFERENTIAL (if available) • Any changes after this will be lost

Simple Recovery Model

• Let’s take a look at what the Pros and Cons are when we change from Simple to Full recovery model

Example #1

Backup Strategy:

Database: LIFUtility Recovery model: FULL

• Full backups on Saturdays at 2300 • Differential backups each day at 2300

• In this scenario, a “mistake” is identified at 1200 on Friday and tracked back to a hotfix installation at 1130 on Friday. How does changing the recovery model change your restore sequence? – ______________________________________________

Full Recovery Model

• Remember that the full recovery model allows you to backup your transaction log – This is critical to remember during a crisis – Do not make the mistake of “forging ahead” with a restore before taking a backup!

Full Recovery Model

• Also remember that you can restore only part of your last transaction log backup – We can stop at 1129 to ensure minimal data loss!

Example #1

Backup Strategy:

Database: LIFUtility Recovery model: FULL

• • • • • •

• Full backups on Saturdays at 2300 • Differential backups each day at 2300

Step 1: Backup the current log Step 2: Restore Saturday’s FULL Step 3: Restore Thursday’s DIFF Step 4: Restore the log but tell it to stop at 1129 All changes after 1129 will be lost Any open transactions as of 1129 will be rolled back

Backup Strategy

• Question #1: – What would happen if we accidently forgot to take the log backup in Step 1? • We would lose all changes since the DIFF

Backup Strategy

• Question #2: – What would happen if the disk(s) holding the data file crashed during the backup of the log? • Remember that we can still backup the log because changes are written to the log first, and then to the data file – This is a primary function of the transaction log

Backup Strategy

• Question #3: – What would happen if the disk(s) holding the transaction log crashed during the backup of the log? • We would lose all changes since the DIFF

Be Careful!

• Question 3 highlights a real problem: – If you do not backup the log frequently, you are at risk of losing all data since the last backup

• How do we mitigate this problem? – More frequent backups – Redundant disks

In the next video…

• We’ll cover more strategies and issues for the FULL recovery model

SQL Server Backup and Recovery

Presented by Scott Whigham

118

• Backup Strategies • Backing Up Your Databases • Backing Up Your SQL Server

Restoring Databases

• Overview of Chapter • Scenario Setup • Types of SQL Server Backups • Recovery Models

Backup Your Databases

Overview

In this video…

• Restoring User Databases • Restoring System Databases

Full Recovery Model

• Let’s take a look at a final example – This is a more likely scenario for a database with active changes (like LIFWeb)

Example #1

Backup Strategy:

Database: LIFWeb Recovery model: FULL

• Full backups on Saturdays at 2300 • Differentials each day at 2300 • Log backups each hour

• Same scenario: a “mistake” is identified at 1200 on Friday and tracked back to a hotfix installation at 1130 on Friday. What is your restore sequence? – ______________________________________________

Example #1

Backup Strategy:

Database: LIFWeb Recovery model: FULL

• • • •

• Full backups on Saturdays at 2300 • Differentials each day at 2300 • Log backups each hour

Step 1: Backup the log Step 2: Restore Saturday’s FULL Step 3: Restore Thursday’s DIFF Step 4: Restore all log backups that have occurred since Thursday’s DIFF and stop at 1129 in the log backup from Step 1

Reminders

• You only restore the most recent DIFFERENTIAL – This is critical to understand for performance reasons • Restoring 300+ log files takes a long time whereas restore a single DIFF backup can be quite fast

Reminders (cont.)

• You will likely have a large number of log files – Do not lose them - you cannot skip logs during a restore! – Be cautious about who (or what) you allow to take log backups and manage the files – Keep redundant copies • Backup to disk and then copy to tape

Remember our strategy:

Backup Strategy: • Full backups on Saturdays at 2300 • Differentials each day at 2300 • Log backups each hour

Database: LIFWeb Recovery model: FULL

Day

Type

Filename

Saturday

FULL

LIFWeb_Full_20100306.bak

Sunday

- LOG

LIFWeb_Log_20100307_0000.bak

Sunday

- LOG

LIFWeb_Log_20100307_0100.bak

Sunday

- LOG

LIFWeb_Log_20100307_0200.bak

(log backups each hour = 23 log backup files per day)

Sunday

- DIFF

LIFWeb_Diff_20100307.bak

Differentials

• It is possible to only use FULL and LOG backups – If you lose your DIFF backups, you can simply use the LOGs instead

Differentials

• Each DIFF contains all changes since the last FULL backup – Prepare that your DIFFs will take longer and longer (and be larger and larger) as you get farther away from the FULL backup • Saturday: FULL • Sunday: Diff (100MB) • Monday: Diff (200MB)

Differentials

• Example of a 1GB/day delta: Day

Type

Size in in GB

Saturday

FULL

300

Sunday

DIFF

1

Monday

DIFF

2

Tuesday

DIFF

3

Wednesday

FULL

304

Thursday

DIFF

1

Friday

DIFF

2

Crash Procedure for Full Recovery Model

• Restore sequence for Full recovery model: • • • •

Step 1: Backup your current transaction log Step 2: Restore your most recent FULL backup Step 3: Restore your most recent DIFFERENTIAL (if available) Step 4: Restore all transaction log backups taken since the last FULL or DIFFERENTIAL in sequence

– If you follow these steps, you will restore to “point of failure”

In the next video…

• How changing to BULK LOGGED recovery model affects your risk

SQL Server Backup and Recovery

Presented by Scott Whigham

131

• Backup Strategies • Backing Up Your Databases • Backing Up Your SQL Server

Restoring Databases

• Overview of Chapter • Scenario Setup • Types of SQL Server Backups • Recovery Models

Backup Your Databases

Overview

In this video…

• Restoring User Databases • Restoring System Databases

Bulk Logged Recovery Model

• Okay - the final one: Bulk Logged – This is a more likely scenario for a database with bulk operations like LIFReporting – Remember that BULK LOGGED is identical to FULL except when bulk operations occur

Example #1

Backup Strategy: • Full backups on Saturdays at 2300 • Differentials each day at 2300 Database: LIFReporting Recovery model: BULK LOGGED

• Same scenario: a “mistake” is identified at 1200 on Friday and tracked back to a hotfix installation at 1130 on Friday. What is your restore sequence? – ______________________________________________

Example #1

Backup Strategy: • Full backups on Saturdays at 2300 • Differentials each day at 2300 Database: LIFReporting Recovery model: BULK LOGGED

• Same as with the FULL: – – – –

Step 1: Backup the log Step 2: Restore Saturday’s FULL Step 3: Restore Thursday’s DIFF Step 4: Restore log backup from Step 1 but stop at 1129 – “Point in time” restore

Example #2

Backup Strategy: • Full backups on Saturdays at 2300 • Differentials each day at 2300 Database: LIFReporting Recovery model: BULK LOGGED

• New scenario: At 1200 on Friday, the disk(s) storing your transaction log crash. What is your recovery sequence? – ______________________________________________

Example #2

Backup Strategy: • Full backups on Saturdays at 2300 • Differentials each day at 2300 Database: LIFReporting Recovery model: BULK LOGGED

• Step 1: Restore Saturday’s FULL • Step 2: Restore Thursday’s DIFF • Step 3: All changes since the last backup will be lost

Example #2

Backup Strategy: • Full backups on Saturdays at 2300 • Differentials each day at 2300 Database: LIFReporting Recovery model: BULK LOGGED

• New scenario: At 1200 on Friday, the disk(s) storing your data file crash. What is your recovery sequence? – ______________________________________________

Aha!

• You actually do not have enough information to answer! – What question do you need to know the answer to before you can come up with a sequence? • “Have any bulk operations taken place since the last backup?”

Example #3

Backup Strategy: • Full backups on Saturdays at 2300 • Differentials each day at 2300 Database: LIFReporting Recovery model: BULK LOGGED

• Let’s be more specific: At 1200 on Friday, the disk(s) storing your data file crash. There have been no bulk operations since the last DIFFERENTIAL backup. What is your recovery sequence? – ______________________________________________

Example #3

Backup Strategy: • Full backups on Saturdays at 2300 • Differentials each day at 2300 Database: LIFReporting Recovery model: BULK LOGGED

• Same as with the FULL: – – – – –

Step 1: Backup the log Step 2: Restore Saturday’s FULL Step 3: Restore Thursday’s DIFF Step 4: Restore all log backups in sequence “Point of failure” restore

Example #3

Backup Strategy: • Full backups on Saturdays at 2300 • Differentials each day at 2300 Database: LIFReporting Recovery model: BULK LOGGED

• Let’s be more specific: At 1200 on Friday, the disk(s) storing your data file crash. There have been bulk operations since the last DIFFERENTIAL backup. What is your recovery sequence? – ______________________________________________

Example #3

Backup Strategy: • Full backups on Saturdays at 2300 • Differentials each day at 2300 Database: LIFReporting Recovery model: BULK LOGGED

• We cannot back up the log – – – –

Step 1: Restore Saturday’s FULL Step 2: Restore Thursday’s DIFF All changes since the last backup are lost “Point of last backup” restore

Example #4

Backup Strategy:

Database: LIFReporting Recovery model: BULK LOGGED

• Full backups on Saturdays at 2300 • Differentials each day at 2300 • Hourly log backups

• Same as before: At 1200 on Friday, the disk(s) storing your data file crash. There have been bulk operations since the last DIFFERENTIAL backup. What is your recovery sequence? – ______________________________________________

Example #4

Backup Strategy:

Database: LIFReporting Recovery model: BULK LOGGED

• Full backups on Saturdays at 2300 • Differentials each day at 2300 • Hourly log backups

• We still cannot back up the log: – Step 1: Restore Saturday’s FULL – Step 2: Restore Thursday’s DIFF – Step 3: Restore all log backups that have occurred since last DIFF backup – All changes since the last backup are lost – “Point of last backup” restore • However, we’ve “lost less”

In the next video…

• We’ll cover a practical way to manage bulk operations that will minimize your risk

SQL Server Backup and Recovery

Presented by Scott Whigham

147

• Backup Strategies • Backing Up Your Databases • Backing Up Your SQL Server

Restoring Databases

• Overview of Chapter • Scenario Setup • Types of SQL Server Backups • Recovery Models

Backup Your Databases

Overview

In this video…

• Restoring User Databases • Restoring System Databases

Bulk Import How-to

• When you want to perform long-running bulk imports, you want to maximize performance – Example: you have a text file with rows of data that previously were stored in DB2 – You can use BULK INSERT, OPENROWSET(), SSIS, or bcp to load these rows into a SQL Server table

Bulk Import How-to

• There are two techniques to maximize performance: – Drop nonclustered indexes when doing bulk imports • Speeds up the import • Must rebuild after import

– Use BULK LOGGED recovery model

Bulk Import How-to

• Field Guide to Performing Bulk Imports: Assumes your database is normally in FULL recovery model

1. 2. 3. 4. 5. 6. 7. 8.

Backup the log Switch to BULK LOGGED recovery model Drop your nonclustered indexes Perform the import Backup the log Add your indexes back in Switch back to FULL recovery model Backup the log (optional)

Bulk Import How-to

• Field Guide to Performing Bulk Imports: Assumes your database is normally in FULL recovery model

1. 2. 3. 4. 5. 6. 7. 8.

Backup the log Switch to BULK LOGGED recovery model Drop your nonclustered indexes Perform the import Backup the log Add your indexes back in Switch back to FULL recovery model Backup the log (optional)

Bulk Import How-to

• We backup the log first so that we have a “safe” restore point – Assuming we are loading data from a text file, if the import fails, we can always restore back to this log and re-do the import with no data loss

Bulk Import How-to

• If desired, we could switch Steps 1 and 2 around with no negative effects – Remember that we can switch between FULL and BULK LOGGED as necessary

Bulk Import How-to

• Field Guide to Performing Bulk Imports: Assumes your database is normally in FULL recovery model

1. 2. 3. 4. 5. 6. 7. 8.

Backup the log Switch to BULK LOGGED recovery model Drop your nonclustered indexes Perform the import Backup the log Add your indexes back in Switch back to FULL recovery model Backup the log (optional)

Bulk Import How-to

• Use the DROP INDEX statement – Don’t forget to script out your index creation statements before you drop your indexes…

• Review BULK INSERT, OPENROWS, Integration Services, and, as a last resort, bcp.exe for options on importing rows

Bulk Import How-to

• Field Guide to Performing Bulk Imports: Assumes your database is normally in FULL recovery model

1. 2. 3. 4. 5. 6. 7. 8.

Backup your log Switch to BULK LOGGED recovery model Drop your nonclustered indexes Perform the import Backup the log Add your indexes back in Switch back to FULL recovery model Backup the log (optional)

Bulk Import How-to

• These two steps could also be switched around if you prefer • It is recommended that perform your log backup prior to adding indexes – Minimizes the amount of restore time in the event a catastrophe occurs during index creation

Bulk Import How-to

• Field Guide to Performing Bulk Imports: Assumes your database is normally in FULL recovery model

1. 2. 3. 4. 5. 6. 7. 8.

Backup the log Switch to BULK LOGGED recovery model Drop your nonclustered indexes Perform the import Backup the log Add your indexes back in Switch back to FULL recovery model Backup the log (optional)

Bulk Import How-to

• Now that you have completed your bulk ops, switch back to FULL for normal, everyday operations – FULL recovery model provides the most complete coverage against data loss

Bulk Import How-to

• Question: if you forget to switch over to BULK LOGGED recovery model, can you still perform bulk operations? – Somewhat of a trick question… – Let’s break it into two parts…

Bulk Import How-to

• If you load a text file using BULK INSERT into a SQL Server database that uses the FULL recovery model, will the operation be fully logged or minimally logged? – Fully logged! – SQL 2000/7.0 allowed DBAs to prevent these operations but not SQL 2005/2008

Bulk Import How-to

• So if this is a fully logged operation, is it technically correct to still call it a “bulk operation”? – Yes, these are still bulk operations yet they are not minimally logged

Bulk Import How-to

• Question: What tool(s) will you use to perform the steps in this guide? – A SQL Server job is perfect – Integration Services is ideal as well – You can even schedule a SQL Server job to execute your Integration Services package!

SQL Server Backup and Recovery

Presented by Scott Whigham

165

• Backup Strategies • Backing Up Your Databases • Backing Up Your SQL Server

Restoring Databases

• Overview of Chapter • Scenario Setup • Types of SQL Server Backups • Recovery Models

Backup Your Databases

Overview

In this video…

• Restoring User Databases • Restoring System Databases

Checkpoints

• To understand the transaction log, you need to understand what a checkpoint is – Writes all dirty pages for the current database to disk. – Writes “maintenance” information in the event a recovery needs to occur – Checkpoints happen about once per minute • Configured via sp_configure and “recovery interval”

The Transaction Log

• We talked earlier about how the Simple recovery model “clears out the log” – The transaction log is “cleared out” at each checkpoint • This is why we cannot take “meaningful” log backups

– But what exactly gets removed from the log? How does SQL Server know what to keep and what to remove?

The Transaction Log

• The log has three basic parts: – Active log – Inactive log – Free space

The Transaction Log

• The active log (or “active portion of the transaction log”) is: – From the beginning of the oldest “open” transaction – From the beginning of any transactions marked for replication that have yet to be delivered to the distribution database

The Transaction Log

• The inactive log (or “inactive portion of the transaction log”) is: – From the beginning of the logical log up until the beginning of the active log

Visualizing the Transaction Log

• Visualization:

In the next video…

• We’ll learn more about the architecture of the transaction log

The Transaction Log

• Let’s take this backup strategy and work through it – FULL: Saturdays at 2300 – DIFF: Weekdays at 2300

SQL Server Backup and Recovery

Presented by Scott Whigham

175

• Backup Strategies • Backing Up Your Databases • Backing Up Your SQL Server

Restoring Databases

• Overview of Chapter • Scenario Setup • Types of SQL Server Backups • Recovery Models

Backup Your Databases

Overview

In this video…

• Restoring User Databases • Restoring System Databases

The Transaction Log

• You can remove the inactive portion of the log – This is “clearing out the log” – Technically called truncating the log

• You cannot remove any part of the active portion

The Transaction Log

• The Simple recovery model automatically truncates the log on checkpoint • Why do we take log backups? – So that we can “replay” the events on the server in the exact order as they happened – About 1x/minute the inactive portion of the log is cleared out – Makes the log useless for recovery

The Transaction Log

• FULL and BULK LOGGED do not truncate the log – Do not become one of my examples to talk about – Your transaction log can grow to fill up the disk!

The Transaction Log

• By default, when you perform a transaction log backup, the inactive portion of the log becomes free space • FULL and DIFFERENTIAL backups do not clear out the log

The Transaction Log

• When you take a log backup, SQL Server: – Copies the entire log to the backup file – Marks the inactive portion of the log as “free space” (i.e. it can/will be overwritten)

• This is why your logs must be restored “in sequence”

The Transaction Log

• It is possible to backup the log without removing the inactive portion • Used in two instances: 1. When you want to take a log backup without affecting your “normal” backup routines •

Remember that you must restore “all logs since the last DIFFERENTIAL in sequence”

The Transaction Log

• Used in two instances: 2. When the data file is unavailable • •

What if the disk(s) holding the data file (.mdf) crash? We can still backup the log provided we do not truncate the log

In the next video…

• Let’s demo what happens when you never backup your log!

SQL Server Backup and Recovery

Presented by Scott Whigham

185

• Backup Strategies • Backing Up Your Databases • Backing Up Your SQL Server

Restoring Databases

• Overview of Chapter • Scenario Setup • Types of SQL Server Backups • Recovery Models

Backup Your Databases

Overview

In this video…

• Restoring User Databases • Restoring System Databases

Misc Bits

• And now for the random bits here and there about backups – Securing your backups – Backup Compression – NO_WAIT, ROLLBACK AFTER, and ROLLBACK IMMEDIATE – “Emergency! My transaction log has filled up!” – Taking a backup of the log without emptying the log

Misc Bits

• And now for the random bits here and there about backups – Securing your backups – Backup Compression – NO_WAIT, ROLLBACK AFTER, and ROLLBACK IMMEDIATE – “Emergency! My transaction log has filled up!” – Taking a backup of the log without emptying the log

Securing Your Backups

• You need to secure your backup files and tapes • Anyone can take a backup file and restore it on another server

Securing Your Backups

• Previous versions of SQL Server allowed passwords on backups – Deprecated in SQL Server 2005 due to being a weak protection mechanism BACKUP DATABASE master … WITH PASSWORD = ‘$hjg#907563’ -- required to restore

Securing Your Backups

• Best practice: backup your databases to disk and secure those files with proper Windows file permissions – Technically called “Access Control Lists” (ACLs) – Set the permissions on the root folder the backups are stored in

Securing Your Backups

• Best practice: secure your tapes – Again, if someone can get to a backup file, they can restore it…

Misc Bits

• And now for the random bits here and there about backups – Securing your backups – Backup Compression – NO_WAIT, ROLLBACK AFTER, and ROLLBACK IMMEDIATE – “Emergency! My transaction log has filled up!” – Taking a backup of the log without emptying the log

Securing Your Backups

• Remember that SQL Server does no compression on backups by default • For years, we’ve relied on 3rd party tools – WinZip, WinRAR, 7-Zip are unsupported

Securing Your Backups

• SQL Server 2008 debuts a feature called Backup Compression – Can only create compressed backups in Developer and Enterprise Edition – All editions can restore a compressed database

Securing Your Backups

• Compression actually increases backup speed – CPU intensive – If too many resources are consumed by backup, consider using Resource Governor to limit CPU usage by Backup Compression

In the next video…

• And now for the random bits here and there about backups – Securing your backups – Backup Compression – NO_WAIT, ROLLBACK AFTER, and ROLLBACK IMMEDIATE – “Emergency! My transaction log has filled up!” – Taking a backup of the log without emptying the log

SQL Server Backup and Recovery

Presented by Scott Whigham

198

• Backup Strategies • Backing Up Your Databases • Backing Up Your SQL Server

Restoring Databases

• Overview of Chapter • Scenario Setup • Types of SQL Server Backups • Recovery Models

Backup Your Databases

Overview

In this video…

• Restoring User Databases • Restoring System Databases

Reminders about the restore process

• Remember the basics: – You must always start with a FULL backup – You may only restore a single DIFFERENTIAL • Remember that a DIFFERENTIAL contains all changes since the last FULL backup • You do not need to restore any log backups taken before your DIFFERENTIAL • You can, however, skip the DIFFERENTIAL and restore the logs instead

The Basics of Restoring Databases

• Basics of restoring LOG backups: – Logs must be restored in sequence – You cannot skip logs during the restore – You cannot skip transactions during a restore – It is possible to restore only part of the last log file you restore • Achieves “point in time” and/or “point of failure” recovery

The Basics of Restoring Databases

• Basics of restores: – The final backup you restore will, by default, take the database through the RECOVERY process and bring the database ONLINE • You decide which is the final backup file in your Transact-SQL code

– Any open transactions in the database will be rolled back during the RECOVERY process

If you are lucky…

• Hopefully your backups are in the same place that SQL Server created them – When you take a backup, that information is stored in that instance’s msdb database • Database, time, name, user, and location • Used by SSMS to suggest restore sequence!

msdb

• msdb contains several tables for backups: – dbo.backupset - one row for each backup set • A backup set contains the backup from a single backup operation

– dbo.backupfile - one row for each backup file • A backup set contains the backup from a single backup operation

– There are others as well

SSMS is your friend

• If, during a restore, all backup files are in the same location they were when they were created: – You can breathe easy - SSMS will automatically create the restore sequence (correctly!) – You then can choose “point in time”, “point of failure”, or to restore up to a specific backup

SSMS is your friend

• It is highly recommended that you at least use SSMS to generate your restore sequence – SSMS reads the msdb tables to come up with: • Most recent FULL • Most recent DIFF (if available) • Each LOG in sequence

SSMS is your friend

• If you do use SSMS to recover, SSMS defaults to: – “Point of last backup”

• You can change this with ease

Transact-SQL is a pain…

• Now… If you want to restore using Transact-SQL, prepare for a lot of typing – Transact-SQL requires a separate RESTORE statement for each backup – Let’s learn more in the next video!

SQL Server Backup and Recovery

Presented by Scott Whigham

209

• Backup Strategies • Backing Up Your Databases • Backing Up Your SQL Server

Restoring Databases

• Overview of Chapter • Scenario Setup • Types of SQL Server Backups • Recovery Models

Backup Your Databases

Overview

In this video…

• Restoring User Databases • Restoring System Databases

Deeper into the restore

• Now… If you want to restore using Transact-SQL, prepare for a lot of typing – Transact-SQL requires a separate RESTORE statement for each backup – To get it “right the first time” requires a deeper understanding of the restore options and process

The RECOVERY process

• Remember the concept of “RECOVERY” – When a database goes through RECOVERY: • • • •

Committed transactions are rolled forward Open transactions are rolled back Database is brought ONLINE Only done on the last backup restored

The RECOVERY process

• When you have to restore multiple backups, you cannot go through RECOVERY until the last backup – Example restore sequence: • FULL: from Saturday • DIFF: From 2300 night before • LOG: 12 log backups to restore

The RECOVERY process •

Example restore sequence: – – –

FULL: from Saturday DIFF: From 2300 night before LOG: 12 log backups to restore

• Step 1: Restore the FULL – Do you want to go through RECOVERY now? • No, otherwise open transactions that might be completed in a later backup to be restored would be rolled back

The RECOVERY process •

Example restore sequence: – – –

FULL: from Saturday DIFF: From 2300 night before LOG: 12 log backups to restore

• Step 2: Restore the DIFF – Do you want to go through RECOVERY now? • No, for the same reason…

The RECOVERY process •

Example restore sequence: – – –

FULL: from Saturday DIFF: From 2300 night before LOG: 12 log backups to restore

• Step 3: Restore 11 logs (but not the last log) – Do you want to go through RECOVERY during any of these logs? • No, for the same reason…

The RECOVERY process •

Example restore sequence: – – –

FULL: from Saturday DIFF: From 2300 night before LOG: 12 log backups to restore

• Step 4: Restore the last log – Do you want to go through RECOVERY? • Yes! There are no more backups to be applied so we want any uncommitted transactions rolled back!

Restore options

• When restoring a backup file, you have three options: – RECOVERY (the default) – NORECOVERY • Don’t rollback or roll forward

– STANDBY • • • •

Allows read-only access to committed transactions Still able to continue with backup Requires an external file to store rolled back transactions This file must be available for subsequent restores

In the next video…

• We’ll go through restoring databases in SSMS and exploring the options…

SQL Server Backup and Recovery

Presented by Scott Whigham

220

• Backup Strategies • Backing Up Your Databases • Backing Up Your SQL Server

Restoring Databases

• Overview of Chapter • Scenario Setup • Types of SQL Server Backups • Recovery Models

Backup Your Databases

Overview

In this video…

• Restoring User Databases • Restoring System Databases

Point-in-time Restores are tricky

• Scenario: – “Mike” calls you and explains that, sometime around 11:30AM, a “bad” transaction was issued by a developer • Mission critical - must undo this transaction or restore to point prior to the transaction • Gotta do it now!

Point-in-time Restores are tricky

• Question: how can you read the transaction log to view the transactions? – Short answer: you can’t, by default – Well, you can but in a generally not-easy-to-reador-remember way

Point-in-time Restores are tricky

• There are third party applications that allow you to view the SQL Server log easily – Not cheap – Can be massive time savers – Lumigent Log Explorer, ApexSQL Recover among others

Point-in-time Restores are tricky

• If you don’t have access to the third party application, what are your options? – If you had Profiler running at the time, review! – Can review default traces – Can restore then “look and see if ‘bad’ transactions were gone

Enough lecture - let’s demo!

• Let’s go see this in action…

SQL Server Backup and Recovery

Presented by Scott Whigham

227

• Backup Strategies • Backing Up Your Databases • Backing Up Your SQL Server

Restoring Databases

• Overview of Chapter • Scenario Setup • Types of SQL Server Backups • Recovery Models

Backup Your Databases

Overview

In this video…

• Restoring User Databases • Restoring System Databases

Restoring Your Entire Server

• The Scenario: – The disk system that held all of your data files (.mdf) has just crashed and cannot be recovered – The sysadmins have just completed installing new drives and building the array – Now it’s your turn to get that SQL Server back up and running

Restoring Your Entire Server

• You are right in the middle of a catastrophe – You can’t make up for the past now • So you’d better have been backing things up!

– Right now, you need a plan…

Restoring Your Entire Server

• Since you cannot backup, your only option for a restore is “point of last backup” – Or is it??? We’ll just have to see about that later…

Restoring Your Entire Server

• You are awesome – Thankfully you have recent backups of all the right things • • • •

System Databases User Databases Service Master Key Certificates

Restoring Your Entire Server

• You are double awesome – You also have documentation: • • • •

Server collation Install locations of files Service pack/hotfix level of server Output of sqldiag.exe – sqldiag.exe is a general purpose diagnostic utility that makes keeping track of many servers easy

Restoring Your Entire Server

• We use the SQL Server setup.exe to perform a rebuild • The process is: 1. If there are system database files already in the target folder, delete them 2. Create new system database files from /Binn/Templates/ files •

Located in the folder that your program files are located

Restoring Your Entire Server

• The rebuild process is done via setup.exe: – NOTE: INSTANCENAME=MSSQLSERVER for default instance

setup.exe /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=instance_name /SQLSYSADMINACCOUNTS= accounts [/SAPWD=password] [/SQLCOLLATION=collation_name]

Restoring Your Entire Server

• Once you rebuild master, you will have an “empty” server – Remember: you are creating new master, model, msdb, and tempdb databases from the templates – The template master database does not know about your databases

Restoring Your Entire Server

• You will not see your: – Databases – Logins – Jobs – Server Objects (i.e. linked servers, backup devices)

• Server settings will not be your settings • model database is back to the default

In the next video…

• We’ll take a look at how to rebuild master

SQL Server Backup and Recovery

Presented by Scott Whigham

239

• Backup Strategies • Backing Up Your Databases • Backing Up Your SQL Server

Restoring Databases

• Overview of Chapter • Scenario Setup • Types of SQL Server Backups • Recovery Models

Backup Your Databases

Overview

In this video…

• Restoring User Databases • Restoring System Databases

Restoring Your Entire Server

• The Scenario: – The disk system that held all of your data files (.mdf) has just crashed and cannot be recovered – The sysadmins have just completed installing new drives and building the array – Now it’s your turn to get that SQL Server back up and running

Restoring Your Entire Server

• To get your SQL Server back: 1. Reinstall service pack and any hotfixes 2. Restore master •

Once complete, you databases, logins, server objects and server settings will be restored

3. Restore msdb •

Restores your jobs, database mail, and more

4. Restore model (optional) 5. Backup the tail of the log for any user databases affected 6. Restore any user databases affected

Restoring Your Entire Server

• Reminders: – No one can be in your database when you restore – You are overwriting a database during the restore • Conceptually think of it in three phases: 1. 2. 3.

Delete original database files Create new versions of database files Restore the backup data into the new files

Restoring Your Entire Server

• To restore master, your server must be in single user mode • Cannot restore other databases while in single user mode

Restoring Your Entire Server

• A word of caution: – Remember that, if you configure SSMS to open both Object Explorer and a query window, that is two connections

Restoring Your Entire Server

• Another word of caution: – Remember that, if your SQL Server Agent is configured to auto start with SQL Server, it logs in before you • When in doubt, stop the SQL Server Agent service

Restoring Your Entire Server

• To put your server in single user mode: 1. Stop the SQL Server Agent 2. Add the -m switch to your SQL Server service’s Startup Parameters via Configuration Manager 3. Restart the service 4. Restore master 1. SQL Server will automatically shut down

5. Remove the -m switch 6. Restart your SQL Server

Restoring Your Entire Server

Restoring Your Entire Server

• About restoring msdb: – SQL Server Agent logs in before you do – SQL Server Agent loves msdb – Make sure you stop the SQL Server Agent before trying to restore msdb

In the next video…

• We’ll take a look at how to rebuild master