0

Microsoft says the following about using Simple Recovery Model in your SQL Server database:

Changes since the most recent backup are unprotected. In the event of a disaster, those changes must be redone.

In the real world, what are the most common examples of such a disaster?

1
  • Is there more info you're needing on this? If so, please clarify, otherwise take the time to flag one of the answers.
    – shiitake
    Jul 6 '15 at 23:51
2

Hard disc crash, corruption of data on the disc - anything that requires you to go to backups.

6
  • Thanks. It's that 'anything' I hope to define more specifically. How about if the SQL Server process itself is suddenly closed? Or if someone pulls the plug on the server. Apr 9 '13 at 8:43
  • Hardly - that rarely happens. But any kind of corruption basically leads to total loss bak to last update as you do not ahve a transaction log to replay the transactions.
    – TomTom
    Apr 9 '13 at 8:54
  • I don't understand "Hardly - that rarely happens". Do you mean that corruption rarely happens when the plug is pulled or that it rarely happens that the plug is pulled? Apr 9 '13 at 9:11
  • THe service rarely crashes without another thing going wrong first. Memory corruption for example.
    – TomTom
    Apr 9 '13 at 9:16
  • 1
    THe answer is no. Not LIKELY - which is subjective. But then, UNLIKELY is enough in my world to have replicated servers with log file shipping on top.
    – TomTom
    Apr 9 '13 at 9:24
0

SQL Server databases consist of at least 1 data file and at least 1 log file. These files contain the data and a record of the transactions that are in the process of being committed or rolled back. All transactions are recorded to the transactions log and periodically a checkpoint occurs which makes sure that the log records are flushed to disk. This also happens when an orderly shutdown occurs: the SQL Server performs a checkpoint of all databases, closes out all internal database tracking structures, and exits the SQL Server process.

There are many things that can cause corruption but in general corruption occurs if/when anything interrupts data from being written to either of those files. This can include the following:

  • Power outage - this would prevent the checkpoint from performing a checkpoint as indicated above. It can also cause problems if the SQL Server was in the middle of writing data to the disk.
  • I/O Subsystem problems - this includes any of the software or hardware that is involved in reading and writing to your disks (the HBA card or drivers, fiber, SAN storage).

More info: SQL Server 2000 I/O Basics

0

Don't forget the most likely cause...human error. Dropping a table, etc. Even if it is in full recovery mode, you still have to actually perform a backup or you'll effectively be running in simple recovery mode. Not doing that initial backup may easily be overlooked in a test environment.

1
  • You have no idea how currently relevant that answer is :) Apr 25 '13 at 19:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.