SQL Server Backup and Recovery Challenges

SQL Server Backup and Recovery Challenges

• 1.
  SQL Server Backupand Recovery Challenges SQL Server DBA Professionals
• 2.
  Consider this… There aremany important things to consider when planning out your SQL Server backup and SQL Server recovery environments. We will introduce some of the common challenges here. 2 Understanding Query Execution Plans Global Marketing
• 3.
  Backup capabilities Microsoft providesout-of-the-box backup capabilities through the BACKUP command. Integrated backups features are capable – but limitations start to show up as SQL Server environments grow in: • • • • 3 Database size The number of databases The number of instances Critical business dependency on SQL Server Understanding Query Execution Plans Global Marketing
• 4.
  Differential backup We tendto introduce SQL Server differential backups as a form of deduplication. Differential backups only back up the data that has changed since the last full backup. Isn’t this a no-brainer? If you have a 100 GB database that you backed up on Sunday and only 1 GB has changed by Monday, why would you back up the 99 GBs of data that have not changed? 4 Understanding Query Execution Plans Global Marketing
• 5.
  Differential backup Unfortunately, thosewho have worked with differential backups have probably realized that they come with some fairly significant challenges: • • • Restores are always reliant on having access to the full backup. Restore time objectives are affected by the amount of data change. File retention is much more difficult. Dell’s LiteSpeed for SQL Server offers rapid, effective database backup and recovery. 5 Understanding Query Execution Plans Global Marketing
• 6.
  Full backup Let’s reviewthe “full backup on Sunday” example – You do a full backup on Sunday and perform differential backups throughout the rest of the week. If it’s Thursday and something happened to your full backup, all of the SQL Server differential backups you’ve created since are no good to you. 6 Understanding Query Execution Plans Global Marketing
• 7.
  Full backup There isnothing that you can do with a SQL Server differential backup without its associated full. This can arise because: • • • 7 Someone has deleted the full backup, either by mistake or by misunderstanding the importance of the data. Backup files were moved to hard-to-access servers or other media offsite – meaning it can take days before you can access it. The file has been overwritten with another using the same name. Understanding Query Execution Plans Global Marketing
• 8.
  Full backup It iseasy to accidentally mess up your SQL Server recovery by removing a full backup from the disk without considering the interdependencies with other backups. Then there are the situations where the full SQL Server backup is still available – you have been careful about not deleting files – but corruption strikes. 8 Understanding Query Execution Plans Global Marketing
• 9.
  Data change challenge Theeffectiveness of differential backups is dependent on the amount of data that’s happened inside the database. Let’s consider our full 100 GB backup on Sunday – Your database changes at about 1 GB per day, so your differentials will be 1 GB Monday, 1 GB Tuesday, etc. So differential backups make sense, right? 9 Understanding Query Execution Plans Global Marketing
• 10.
  Data change challenge Butwhat happens if you have a lot of data change or an index of defrag/rebuild on Wednesday? You may find that 80% or more of the backup has changed, meaning 80+ GBs need to be backed up. Now you have a differential backup that will negatively affect, and nearly double, your SQL Server recovery time. Dell’s LiteSpeed for SQL Server offers rapid, effective database backup and recovery. 10 Understanding Query Execution Plans Global Marketing
• 11.
  File retention challenge Fileretention is the automated process of deleting backups from disk that are no longer needed while also making sure that needed backups stay around. Most retention routines simply remove backups older than the specified retention period. 11 Understanding Query Execution Plans Global Marketing
• 12.
  File retention challenge Let’sgo back to our full backup on Sunday example: Suppose a DBA sets up their file retention to be five day’s worth of backups, and a full backup is done on Sunday, this could cause the Friday retention routine to delete the full backup from 6 days ago, leaving you with 5 days of worthless differentials. 12 Understanding Query Execution Plans Global Marketing
• 13.
  File retention challenge Afailed backup also fits into this category – a second full backup fails for some reason. Ideally, you would be notified of the failure and possibly generate another full backup. But what if you don’t catch it in time and Monday’s differential backup now points all the way back to a full backup from a previous Sunday? If your retention doesn’t take this into account, you run the risk of deleting a full backup that leaves you in a unrecoverable situation. 13 Understanding Query Execution Plans Global Marketing
• 14.
  Learn More To preventdowntime, click and download a Free Trial of Litespeed. Click to learn more about SQL Server Backup and Recovery. To read the original post Click Here.