Housekeeping
• Please be sure to answer survey (above video window)
Viewing Tip – Enlarge Slides Now…
• You can enlarge the window with the slides – just click “Enlarge Slides” below.
Agenda
• Overview of Backup/Recovery Planning • Wizards Available
• Quick Overview of Jobs and Backups • Restores – how they work, how they’re
done
A Note About Versions
• Very similar
• Will try to outline differences
• “Look and feel” more than “Functionality”
Using Azure or AWS/Cloud Services?
• Still need backup.
• MUST understand differences and approaches • Trust, but verify, as with all backups
Backup and Restore Purposes
• Broad catastrophic • “Oops” corrections • Targeted restores
• Business Intelligence
Backup…Plan…Test…
Food for Thought
• Reliance on Wizards • Incomplete strategy
Key Questions
• How often do I need to backup?
• How many copies do I need to keep?
• How will they be restored/used?
• These determine your strategy and what you
Understanding Backup Models
• Full
• Simple
Recovery Model: Full
• Full transaction log restore
– Restore the baseline db
– Restore the transaction logs in order – Restored up to last tran. log backup – “Tail” backups
• Watch the transaction log physical devices • Beware total reliance on tran. log
Recovery Model: Simple
• Backup/restore at the database level
• Good for development environments
• Disaster recovery, but not
transaction recovery since the last backup
• “All or nothing” restore
Recovery Model: Bulk_logged
• Better for bulk operations
– Select into, etc.
• Good for a migrating database
– If the data exists elsewhere
– Can be rebuilt from other source
– Good for DTS move operations – control log size build-up
Setting Recovery Models
Best Practices – Development to Production
• Development – use a SIMPLE model
– Backup nightly/weekly depending on changes and
your needs
• Production – use a FULL model
– Backup nightly
– Determine interval for transaction log backups
• Be sure to remember to change when your
Planning Your System
• Disk space
• CPU utilization
• “Split” backup plans – Database-by-database – Filegroups
Overview of SQL Server Backups – How do they work?
• Define a destination (“Backup Device”)
– Disk, tape, URL/Azure (SQL 2014)
• Decide what you want to accomplish
– Backup only
– Reorganize/defragment, etc.
• Setup a schedule
Using the Wizards
• Wizard benefits – Scheduling
– Other maintenance options (defrag, etc.) – Backup vs. Maintenance Plan Wizards
– Great learning tool
• SQL Statements • Scheduling
Database Maintenance Plan – Wizard-Based
• Management, Maintenance Plans, Maint. Plan
Wizard
• Suggestion:
All user db’s
• Make sure you
setup system DBs
• Demo
Backup Considerations
• Keep in mind that verify integrity option = CPU stress
• Watch the retention values you provide – Disk space usage
Notification and Reporting Options
Understanding the Job System
• SQL Server Agent, Jobs
• You’ll see different jobs for each plan you set up. Descriptive titles are helpful.
• DB Backup, Tran log backup,
optimization, integrity checks • Double-click any to review
• Scheduling and other options
Setting Up Error Handling
• Don’t ignore this step – your backups are at stake • During the wizard, or after
the fact, set up operators to receive notifications
• Consider success AND
failure notifications to start, then move to exceptions
Establishing a Reporting Operator
• Set up operator • Reference in job
• Properties for the job…
• Consider writing “bread crumbs” to the event log as well
Backups – Manual Process
• If you backup the db directly (manually), you’ll be shown the options differently
Restores – The Process
• Restore the database • Restore the logs
– In order, oldest first
Restores – How-To
• Right-click db, tasks, restore
– Or
– Right-click Databases, Restore option
• Select the db to restore • Select the set to use for the
restore
• Consider restoring to a new database rather than the source if you have ANY question…
• Script button…
• Tail-log backups – full or bulk- logged only
Restore Notes
• Check the generated SQL if you’re having trouble
• Note that you can change the destination
Managed Backups
• SQL Server determines frequency
• You set up retention and it manages the rest
The Cloud
• CAN be very big differences in recoverability
• Remember the types of objectives (next slide)
• Some recovery options don’t make sense in the
cloud (log shipping)
– Know the system recovery process, e.g. AWS
Stop/Start
• Know how a hybrid (cloud/on-premise) situation
Backup and Restore Purposes
• Broad catastrophic
• “Oops” corrections • Targeted restores
• Business Intelligence
You may want/need a plan for each of these.
Disaster Planning…and Testing
• Test your restores on a development instance • Learn what happens to your applications after
a “cold” restore
Final Thoughts, Best Practices
• Document your plan, media passwords, recovery steps (and test them)
– Don’t be the single point of failure just because it’s all in your head
• T-SQL for your systems (script it out)
• Encryption of backups
• Key management
• User accounts, jobs, other system-level items
• Test your restores, test the process of getting back online • Have multiple fall-back positions
Questions…
?
?
?
?
?
?
?
?
?
?
?
?
?
?
?
Please be sure to fill out the survey – and feel free to ask questions in the Q&A tool.
