Best Practices for Data Backup and Recovery in Database Management
Learn the importance of backups, restores, and responsible handling in database management. Understand the significance of continuity, flexibility, and cost savings, and discover the collaboration between junior and senior DBAs for effective data protection strategies.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
This is a Header Data Backup and Recovery Best Practices THIS IS A SUBTITLE Michael Wall
Who am I?? Senior Database Administrator with years of experience supporting a variety of database engines (Oracle, SQL Server, Ingres) on multiple OS platforms (Windows, UNIX, VMS, Azure). Local SQL Server User Group Leader and Regional Supporter SQL Saturday Presenter Big on Community This is a Header My first backup discussion with the business. THIS IS A SUBTITLE
Backups What are they? Definition of a Backup What does it mean to the business? Continuity!!! What does it mean to the IT staff? Time and Responsibility!!! This is a Header What does it mean to the bottom line? Cost and Savings THIS IS A SUBTITLE
Restores What are they? Definition of a Restore What does it mean to the business? Continuity and Flexibility!!! What does it mean to the IT staff? Time and Responsibility!!! This is a Header What does it mean to the bottom line? Cost and Savings THIS IS A SUBTITLE
Then who is responsible? If it s so important, why is it one of the tasks that is usually given to junior staff? This is a Header THIS IS A SUBTITLE
Then who is responsible? Why shouldn t the junior staff be given This is a Header the task? THIS IS A SUBTITLE
Then who is responsible? The business is an equal partner in all of This is a Header this. THIS IS A SUBTITLE
Then who is responsible? RTO/RPO what are you talking about? This is a Header THIS IS A SUBTITLE
Working together. The junior and senior DBAs and the business can all work and grow together. This is a Header THIS IS A SUBTITLE
Types of Backups System System Level Database Backups Data Backups VM Backups Pros and Cons Full/Differential/Transactional Table level backups This is a Header Snapshots versus Full Backups Full versus Simple mode Exports and Imports Hot versus Cold THIS IS A SUBTITLE
Types of Backups - Database System Level Database Backups Data Backups VM Backups Pros and Cons Full/Differential/Transactional Table level backups This is a Header Snapshots versus Full Backups Full versus Simple Recovery Mode Exports and Imports Hot versus Cold THIS IS A SUBTITLE
Azure Backups?? Azure SQL Database you re covered mostly. Guaranteed Number of Backups SQL Server on Azure VM it s all up to you. It s up to space and network bandwidth This is a Header THIS IS A SUBTITLE
Defense in Depth why not? Basics work with your SysAdmin and Storage team. They re going to be the ones that provide that infrastructure for you and your backups! Full Backups It doesn t matter whether your SQL Server instance is in Full or Simple mode, do your full backups, even for a dev instance! Keep at least a few days worth so you have more than one chance. This is a Header Log Backups Being able to provide a to the minute recovery is a nice thing for the business. Exports Export key tables, set them aside. They have a multitude of uses. THIS IS A SUBTITLE
Recovery Practices Talk to the business what is their RTO/RPO? This is your opportunity to help them decide what they need and when they need it. Creating test systems best possible way to automate and test your backups at the same time! Your users will love you having fresh data on a daily basis! This is a Header THIS IS A SUBTITLE
Offsite Backups care and feeding Personal experience it s best to backup the database directly to the server and then copy it offsite. If you backup directly to a SAN/NAS, you ll get some nasty waits like the unholy trinity of BACKUPBUFFER ASYNC_IO_COMPLETION BACKUPIO. Offsite backups should be close, but not too close! This is a Header Part of the discussion with the business about RTO/RPO What is your network like? Will it support moving backups back and forth? Again, make sure you re working with your SysAdmin and Network team to properly support the business. THIS IS A SUBTITLE
Scheduling and Alerts Basic Schedule Fulls 1/day, Logs every hour. If you can decrease the interval of the fulls and logs, why not? Who doesn t want to be able to recover to the minute? All of these backups are useless without alerts letting you know if they failed! This is a Header THIS IS A SUBTITLE
Data Backups uses and scenarios SQL Server syntax select * into <new table name> from <old table name>. Don t use NOLOCK you want a GOOD copy. These backups are perfect before doing some work on a table. Schedule these selects to have another place from which to recover data. This is a Header Schedule exports to a shared location (via excel) so users can grab them for ingesting into another application like PowerBI. THIS IS A SUBTITLE
Errata other items Always set the default in the SQL Server properties to Compress Backup why not? Verification another great idea and typically only adds an extra few minutes to the backups. Why wouldn t you want to have valid backups? This is a Header Tail-log backups used to capture all log entries that have not yet been backed up especially a good idea if the database in question is damaged. Backup those system databases, Master, MSDB, Model, ReportServer Script out the user and role objects in case the restore of Master fails. THIS IS A SUBTITLE
Checklist Work with the business and your team backing up in a vacuum won t help anyone! Automate your restores to reduce stress and maintain continuity Full backups everyday and logs as often as you can make them happen. This is a Header Don t forget the System Backups! Additional data backups are a good idea and can prevent a lot of grief for you and the business! Don t forget: Alerts, Compress Backup default, Verify Backup default. Finally, it s a RESTORE strategy because your backups are ONLY as good as YOUR LAST SUCCESSFUL RESTORE! THIS IS A SUBTITLE
Have Any Questions? This is a Header THIS IS A SUBTITLE
