I know that in the past the recommendation for SQLA was, that backups should be created using the internal mechanisms. Anyway I am wondering if with the arrival of new storage technologies this rule might have changed.
So my question is, would it be possible and if yes, what disadvantages would I encounter if the underlying storage system will perform snapshot copies of the database every now and then. Will these snapshots be usable and if yes, how much data might I loose.
asked 06 Mar '14, 04:36
This should be a comment because that's where rhetorical questions go, but I wanted to share a video...
Let's talk about a specific "snapshot" implementation: Windows Shadow Copy, also known as Volume Snapshot Service (VSS), and in particular, that version of VSS delivered with all editions of Windows 7: It allows "any file to be retrieved as it existed at the time any of the snapshots was made".
Presumably, that means snapshots of the SQL Anywhere database.db and database.log are made at the same instant in time, and (again presumably) the snapshot is perfect as far as the bytes on disk that Windows knows about are concerned.
The question arises: From a freshly-started SQL Anywhere engine's perspective, during restore/recovery, are "the bytes on disk that Windows knew about" sufficient to launch a new executing instance of the database?
What about all those "dirty" and "cleanable" pages that were in the RAM cache and nowhere else, at the time the snapshot was taken? (with the difference between "dirty" and "cleanable" discussed recently).
FWIW SQL Anywhere dbbackup.exe takes a checkpoint before the backup starts. Plus, if the database is large enough, even snapshot copies of both files at the same time, every time, might be too slow, and multiple incremental log backups in between full copies of the database.db might be necessary... how does that fit with shadow copy behavior?
That's as far as I can go down the rabbit hole, I had to give back the red key :)...
Here is my understanding of things in a nutshell: