Voice of the DBA

Tag: backup

  • Inside SQL Server Backup and Restore History Pruning with sp_delete_backuphistory

    I had a customer that was looking to document a restore that had occurred on one of their systems and didn’t see it. They had concerns about SQL Server accurately tracking history across time and noted they hadn’t cleaned any history.

    We dug through some of their instance jobs and found one that ran sp_delete_backuphistory. The person didn’t realize this removes restore history as well. This post talks a bit about how this works.

    The important thing to understand here is that this removes backup and restore history. Not just backups. I don’t know I like this, but it is what is documented (emphasis mine).

    2024-06-23 11_09_00-Zoomit Zoom Window

    In this case, the sysadmin didn’t realize this removed restore entries. Once they did, they stopped worrying about things. We could have potentially restored an old backup of msdb and found this data, but they elected not to do this.

    How The Procedure Works

    We can actually see the code for this proc. I have expanded the msdb programmability section under system stored procedures.

    2024-06-23 11_02_10-SQLQuery1.sql - ARISTOTLE.msdb (ARISTOTLE_Steve (82)) - Microsoft SQL Server Man

    I won’t show it, but this works in the following way:

    1. create three table variables with a single ID column
    2. insert data into these two tables from backupset where the date is older than the parameter passed in.
      1. backup_set_id from backupset
      2. media_set_id from backupset
    3. insert data into the third table that matches the backup_set_id from the table in A
    4. start a transaction
      1. delete from backupfile the matching backup_set_id values
      2. delete from backupfilegroup the matching backup_set_id values
      3. delete from restorefile the matching backup_set_id values
      4. delete from restorefilegroup the matching backup_set_id values
      5. delete from restorehistory the matching backup_set_id values
      6. delete from backupset the matching backup_set_id values
      7. delete from backupmediafamily where the media_set_id values match
      8. delete from backupmediaset where the media_set_id values match
    5. commit the transaction (or rollback if errors).

    This is a pretty simple flow, and it works well. The tricky part is that the is joins data in a way that makes sense, but might not be what you expect. This doesn’t remove restores based on the date, but based on the backup rows being removed.

    Know Your Tools

    This is a poorly named procedure, but that’s not an excuse for anyone. If you use this, and likely should, you need to ensure that you understand how it works. The phrasing in the documentation makes sense, but it can be a little misleading as many of us might assume the date is applied to backup and restore history tables.

    It is not.

  • Default Backups and Feature Awareness

    Have you ever been asked a question about your software and thought that the person was asking about an obvious feature? Perhaps someone has suggested a feature to you and you answered that this was already in the product. Did any of you think your users were stupid or not very observant, or something else?

    I’ve had that situation appear a few times, with me on different sides of the discussion. I watched someone ask a question that I knew the answer to, and thought, huh, why didn’t they know that? I asked a question about something in the Microsoft Azure world and got a quick answer, wondering why I hadn’t been able to find the solution myself, and that I shouldn’t have been cursing the Microsoft developers so much. I’ve had a few people do strange things at SQLServerCentral after the migration and wondered, why in the world…….? Then I remember that lots of people use the site differently than I do and I should expect that.

    I thought about this while reading Joey D’Antoni’s piece on backups. It’s not hard to make backups. It’s not hard to set up a maintenance plan to run backups. It’s not hard to set up a history task to trim old ones. Heck, it’s not hard to implement Ola’s scripts.

    The problem is all of these solutions require some knowledge.

    I think plenty of people assume either the system backs itself up or their nightly file sweep by a central backup system will grab the database. Hint: it won’t. I think plenty of people think backups are someone else’s job, or they are in a hurry with an installation and assume they, or someone, else will return and set up backups later. Plenty of third party software may create a database, but never set up a backup process. We often don’t realize no backups are running.

    That’s not good. While I like Joey’s suggestion, I’d go further. I’d have instance defaults set on install and an automatic, nightly full backup process set for all new databases with a week’s retention. I’d embed this in the DDL so the default is backups will be set on CREATE DATABASE, and you can use a switch to turn this off if you want to use some other backup process. I bet backup software, like SQL Backup, would even do this for you, turning off native backups when you turn on their scheduler.

    I think backup is the most critical thing a DBA does, or an accidental DBA, or the accountant that installed Dynamics, or anyone working with a database. Next would be verifying things with a restore. Then comes security and the list goes on from that point. If you don’t have the data, because of ransomware, virus, bad hardware, or anything else, you are in a bad situation.

    Please. Set up backups. If you find a server without them, inform someone in writing of the dangers and give them resources for creating backups. If you create databases as part of your software deployment process, set up backups somehow. Use a maintenance plan. Those aren’t great, but they’re better than nothing. There’s a reason why SQL Monitor has an alert for databases with no backup. It’s a critical one to have running.

    Steve Jones

    Listen to the podcast at Libsyn.

  • You Need Offline Backups

    If you hadn’t heard about it, VFEMail may be dead. At least, that’s what the founder was thinking in this article. A malicious hacking incident took place last week, and though they’re back up and running, who knows if customers will stick by them, or maybe sue them out of existence. I wouldn’t be surprised as a large number of their infrastructure servers were wiped out by reformatting servers. These included mail servers, backup servers, and SQL Servers.

    That’s quite an attack, and whether this was directed at the company or some individuals, a large number of people might have lost their mailboxes and previously stored mail that was in IMAP storage. This is the type of issue that is most likely an annoyance for individuals, but it would be potentially catastrophic for businesses. Imagine your small business hosted with them and all your mailboxes were lost with customer communications and who knows what else. Perhaps you could recover data or keep the business going, but it might be an issue.

    Could this happen with a cloud provider like Azure O365, Google Apps or AWS? Possibly, and while I’m sure they have backups, I’m not sure how reliable those might be for the average individual or small business. This makes me worry slightly as I depend on GMail and wouldn’t even try to backup to few 100GBs of mail I have. I’m not even sure how to do it, though I don’t really keep anything in there that’s really important. In any case, I’d suspect that connecting and somehow wiping out Gmail servers, along with backups, would be very difficult.

    This does make me think about a few customers I know that use online storage for backups. They assume that they will always have either a primary server or the online backup server/share/bucket/container and can download data. The problem is that online systems that connect to the primary can be accessed. If an attacker were to access one, they potentially could access the second.

    The world seems to be moving towards more online storage, or in the case of cloud vendors, a reliance on snapshots. That might be good enough for cloud vendors, but it’s certainly not for any on-premise system. It’s likely that an attacker, possibly with insider help, would wipe out backups first, then primary systems. I’d always want some sort of disconnected offline backup of data, especially database servers. I have seen Murphy’s Law strike two systems at once, so an air gap between copies of data just feels prudent.

    Steve Jones

  • Cloud Backup

    I think that backup and restore are the most critical things for any database professional to master. Whether you’re a professional DBA, a developer setting up a system, or a seasoned DBA, I would argue having your data safe is the primary task. Security comes next, then performance, availability, and a number of other tasks that could go in any order, but if we don’t have a way to restore our data when hardware fails, we’re in trouble. And given enough time, or enough difference pieces and parts, something will fail. If you can’t restore a system when a problem occurs, that’s what Grant would call an RGE.

    Over the years companies have moved to many different technologies to handle backups. Tape was common early in my career, but all disk systems, with de-duplication capabilities have become popular. I really don’t think about anything other than getting a second (or third) copy of data these days, so I can’t speak to any particular way of managing backups. However, for SQL Server, I do want the option to set full, differential, and log backups based on my RPO and RTO requirements.

    There seems to be a new trend for companies that I ran across: they’re moving to the cloud. Here’s a short slideshow of some stats that show cloud use is increasing. This is a survey, so it’s not all companies, but the trends are clear. More data is being backed up, and it’s likely easier (and cheaper) to use cold storage in the cloud. That makes sense since it’s data that you expect you’ll very rarely need to use in a restore.

    There are a couple of other interesting items I saw in the survey. The number of companies that are backing up more than 100TB grew quite a bit, even as the number of companies backing up < 25TB fell. That’s a sign that we’re capturing more data. Whether we need to, or whether legislation like the GDPR will get companies to trim some of that data, remains to be seen.

    Another interesting item is that more companies are testing their DR plans. Fewer never test them, but the frequency is increasing with more companies testing quarterly or monthly. That’s smart as we become more dependent on computer systems. I know some organizations can’t roll back to paper, as we’ve seen in a number of airline IT issues. If you never test your DR plan, I hope that you don’t have an issue when you can ill afford to find another job, becuase you might suffer those consequences. Really, I hope that you actually know this isn’t professional and you start working on ways to test a restore of service.

    More companies are moving to the cloud, and the resistance for security, cost, privacy, etc. reasons is going down. I think many of the concerns that both executives and IT professionals have had in the past are proving to be non-issues. This is especially true as more vendors institute government rated or more secure data centers as a part of their product offering.

    If you don’t like the cloud, that’s fine. If you don’t know anything about it except rumor, guesses, or hearsay, I’d suggest you learn more. The cloud is likely coming into your career, so learn a bit about it. At least enough to give reasons why you don’t want to move your data there. If you do, you might be surprised that the cloud is not that bad a place to be, at least for some workloads.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 5.3MB) podcast or subscribe to the feed at iTunes and Libsyn.