I work with a database on SQL Server, its size is about 100 Gb. To reduce the risk of data loss, I perform backups on a schedule using a third-party program.

Do you think backups are a good way to protect data or am I missing something and needing to add it?

  • If you are given an exhaustive answer, mark it as correct (a daw opposite the selected answer). - Nicolas Chabanovsky ♦

4 answers 4

If you keep really valuable data, the loss of which between full backups will upset you - then you should reconsider the approach to backups.

What is a "full backup"? This is a complete copy of the database at a particular point in time. If you make only full backups, then in case of which you will lose all data from the moment of full backup. And doing full backups often will not give you the size of your base.

Fortunately, SQL Server works with the data not only at the "write to database" level. It also has a feature called "transaction log" and "full recovery mode".

The transaction log is a .ldf file, which by default lies next to .mdf.

If you look closely, then when you write any changes to the database, the following happens:

  • a request comes from an INSERT что-то там client INSERT что-то там
  • SQL Server changes the data in memory and quickly records the fact of inserting transactions into the log (ldf), simply appending it to the end of the log.
  • SQL Server reports to the client "everything is OK, the record is inserted."
  • Once a minute, SQL Server makes a checkpoint - writes modified data to disk (mdf).
  • SQL Server appends changes for such transactions to the log to disk.

In Full Recovery Mode, the log file stores all changes made since the creation of the database. In the order in which they were made.

The log file is automatically clipped at the moment of the backup of the log - i.e. when SQL Server is sure that you have saved the transaction data. At the same time, the backup of the log contains only changes made since the previous backup of the log. Those. its size is usually much smaller than the size of a full backup.

(By the way, the fact that you are not backing up the log most likely means that your base and your full backup now weighs 2 times more than it should be).

The essence of all this - having a full backup of a week-old database + backup logs, taken 5 minutes ago, you can restore the state of the database at any time during this week.

Your backup scheme should look like this:

  • Full backup database once a week.
  • Backup logs every 5-15 minutes / hour, depending on the allowable loss.

This scheme is easily configured through the standard Maintenance Plan mechanism (if you do not have Express). Or your third-party utility - it certainly knows how to maintain backup logs.

In addition, if you have SQL Server Standard or higher, then it has a built-in Managed Backup , which automatically configures this scheme, selecting intervals based on the amount of data + enables compression of backups + pours backups into Azure Storage. Those. solves the problem of maintaining backups with minimal expenses for their organization :)

    It depends on what you are protecting. If you are afraid of losing data, then yes, backups are enough. If you want to protect against data theft, then you need to think about other security tools. A classic example: storing passwords in a hash.

    UPD. Only backups are best stored on another machine!

    • About "by other machine" - yes. Otherwise, there is no special meaning (taking into account the text of the author’s question). +1 - pegoopik

    Good day.

    I highly recommend using the legendary set of https://ola.hallengren.com/ as a solution for saving and checking backups in SQL Server (spend a couple of hours studying them, the same amount of tuning and the problem of backups in SQL Server can be worried much less ).

    Now about the backup methodology itself. Highlight 3 hours and be sure to read the following 4 articles (English carefully): Help, my database is corrupt. Now what? , What is Do When DBCC Report Corruption , Questions And Questions You Should Ask About The Databases You Manage and the most important in the context of your question The 9 Letters That Get DBAs Fired .

    From myself I will add: be sure to agree in writing with your boss (or the boss's boss) the most important metrics:

    • RPO (Recovery Point Objective) is the minimum time (in minutes or hours) during which data loss in your database is acceptable (i.e., if a disaster happens at 12:59 pm, and you do backups every half hour, then you lose all the data that was changed from 12:30)
    • RTO (Recovery Time Objective) - how much time is needed to restore a backup

    And, of course, in addition to creating the backups themselves (with the approved policy of their creation), they (backups) need to be checked for integrity on a regular basis, unfortunately, on a regular basis, many people forget about it.

    PS Not quite on the topic of backups, but in my opinion extremely useful tools for monitoring problems with SQL Server absolutely free of charge can be found here: Github Brent , and additional materials to dive into the world of SQL Server here: Github sqlserver-kit

      Backup does not save you from the loss of already committed transactions. Suppose you have a backup once a day, during the day you have bought goods for one billion rubles. A lightning bolt hit your server :) How can this backup help you? Better than nothing, of course. You need to think about risks and consider options for minimizing them. In addition to backups, there are database save points (by the moment the end of transactions) that weigh much less than a full backup. Total backup + a number of save points will be able to return to your consistent state of the database before a lightning strike.