1

I've been reading article after article about the difference between a Database Backup and a Snapshot and they just don't make any sense, so please help me undersand

"A snapshot is an image of your system/volume at a specific point in time."

"A backup is a copy of your entire system/volume at the time the backup was performed."

  1. Everything is made in a point in time. That's just how physics work. Unless you live in a black hole where time does not exist or something. That phrase just makes no sense to me. The back up is made at the time it was made. Yes. Duh. What does this even mean.
  2. I have no idea what an "image" of a database is supposed to be. This analogy means nothing to me. Do they just mean a "copy"? A backup is also a copy of your database. So again, they're both the same to me.

"A snapshot only keeps the changes of your database since the last snapshot"

  1. But I have never made a snapshot yet. This will be the first one. So what will the snapshot be? There's no changes since last snapshot since there's no last snapshot. Will just be... exactly the same thing as a backup?

"A snapshot is fast to make and has a small size"

  1. Again, this is my first snapshot. How will it magically be faster and smaller than a backup? Or is that just a lie?

Edit: Sources: (All top google results from Backup vs Snapshot)

https://phoenixnap.com/kb/snapshot-vs-backup

A snapshot is an image of your system/volume at a specific point in time.

A backup is a copy of your entire system/volume at the time the backup was performed.

https://www.sqlshack.com/understanding-database-snapshots-vs-database-backups-in-sql-server/

Database snapshots are like a view of a database as it was at a certain point in time.

Another use for snapshot backups is that multiple snapshots can be created for a database, and these can be taken at different points in time

Backups can be created at any time. A snapshot is a “point-in-time” copy of a database.

https://simplebackups.com/blog/backups-vs-snapshots-with-differences-and-examples/#what-is-a-server-or-a-file-backup

Snapshots are small and can be made quickly and easily without too much of an effect on the server.

0

4 Answers 4

5

You have to be very specific about the terminology used.

SQL Server Database Backups

Backing up your SQL Server database is essential for protecting your data. This discussion covers backup types, and backup restrictions. The topic also introduces SQL Server backup devices and backup media.
[...]
back up [verb]: Copies the data or log records from a SQL Server database or its transaction log to a backup device, such as a disk, to create a data backup or log backup.

backup [noun]: A copy of SQL Server data that can be used to restore and recover the data after a failure. A backup of SQL Server data is created at the level of a database or one or more of its files or filegroups. Table-level backups cannot be created. In addition to data backups, the full recovery model requires creating backups of the transaction log. [...]

Reference: Backup overview (SQL Server) (Microsoft Learn | SQL)

SQL Server Database Snapshots

A database snapshot is a read-only, static view of a SQL Server database (the source database). The database snapshot is transactionally consistent with the source database as of the moment of the snapshot's creation. A database snapshot always resides on the same server instance as its source database. While database snapshots provide a read-only view of the data in the same state as when the snapshot was created, the size of the snapshot file grows as changes are made to the source database.
[...]

Reference: Database snapshots (SQL Server) (Microsoft Learn | SQL)

Windows Snapshots

Windows Snapshots which are performed with the help of Volume Shadow Copy Service (VSS) and SQL Writer are actually snapshots of the disk drives (HDD/SSD/...) at a given point in time.

SQL Server provides support for Volume Shadow Copy Service (VSS) by providing a writer (the SQL writer) so that a third-party backup application can use the VSS framework to back up database files. This paper describes the SQL writer component and its role in the VSS snapshot creation and restores process for SQL Server databases. It also captures details on how to configure and use the SQL writer to work with backup applications in the VSS framework.
[...]

Reference: SQL Server Backup Applications - Volume Shadow Copy Service (VSS) and SQL Writer (Microsoft Learn | SQL)

There is a nice picture provide in the documentation that points out what happens during a Windows Snapshot which will perform a snapshot of the database:

Snapshot Creation Workflow

Answering Your Question(s)

Difference between Backup and Snapshot

  1. A SQL Server Database Backup will allow you to restore the database to the time the (FULL) backup was created. If additional differential backups (DIFF) and transaction log backups (TLOG) of the database were performed , then you will be able to go forward in time to reach a database state at a specific time.
Monday     Tuesday   Tuesday   Tuesday
20:00      20:00     21:00     22:00
 FULL ---> DIFF ---> TLOG ---> TLOG 
  1. A SQL Server Database Snapshot will create read-only copy of an existing database as explained in the excerpt above.

  2. A Windows Server Snapshot will allow you to revert the whole system (OS & SQL Server Databases) to an exact point-in-time, when the snapshot was created.

The links in your question about snapshots are relating to two different technologies: Database Snapshots (see 2.) and Windows Server Snapshots (see 3.).

This is why you might have been confused.

3
  1. Everything is made in a point in time. That's just how physics work. Unless you live in a black hole where time does not exist or something. That phrase just makes no sense to me. The back up is made at the time it was made. Yes. Duh. What does this even mean.

Regular database backups can be setup to be scheduled recurringly with a particular strategy such that you can restore your database to any of multiple points-in-time in the past. It is a one-time setup that then allows backups to continue to occur as if there are constant restore points.

The verbiage you quote is somewhat the opposite of that and stating that these kinds of backups are only one-time as of that single point-in-time.

  1. I have no idea what an "image" of a database is supposed to be. This analogy means nothing to me. Do they just mean a "copy"?

Yes. This is common terminology in the infrastructure field regarding backups.

A backup is also a copy of your database. So again, they're both the same to me.

Yes, they are both two different ways to make some sort of copy. Again, having the source(s) of your quoted verbiage could be helpful for us to explain their differences further.

^ I think you dropped this.

  1. But I have never made a snapshot yet. This will be the first one. So what will the snapshot be? There's no changes since last snapshot since there's no last snapshot. Will just be... exactly the same thing as a backup?

  2. Again, this is my first snapshot. How will it magically be faster and smaller than a backup? Or is that just a lie?

See my last sentence to #2.

The reason this is important is because regular database backups don't occur at the "system/volume" level. So it sounds like you're reading about different kinds of backups than what's typical at the database level.

0
2

Just want to point something for snapshot usage. Both snapshot and backup will let you recover database when needed but the ways that they are stored and used are quite different.

Backup, I mean full backup here, will store everything you select and then you can keep it in other places. It will recover the database regardless of whether the source database exist or not. You can even use the .bak file to create new database.

SQL Server snapshot, which is a sparse file, is often much smaller than backup because it doesn't need to store everything immediately. Indeed, it is only a file to record the delta of the database.

When you recover the database with snapshot, everything will be reversed according to the records; namely, if the source database can't be found for some reason, relying solely one snapshot won't help recover the data.

One more thing, the size of backup is often fixed but the size of snapshot will grow because more and more records are stored so make a good snapshot management plan for that.

It is recommended to use snapshot for senarios like database upgrading and testing and use backup for real disaster recovery.

https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-snapshots-sql-server?view=sql-server-ver16

1

Since this can concern filesystems, databases, version control systems, and other things, I'll just use the term "data" for whatever it is you're backing up or snapshotting, and "system" for the database or filesystem driver.

"Backup" is the easiest to define: it's a dataset that contains everything needed to restore the data to the state it was when the backup was done. Hopefully, the backup is consistent, that is, it will restore the data to exactly the point in time the backup was made. If some changes were done to the data during the backup process, they should not be included in the backup, since that would break consistency.

For example, zipping a directory into an archive is not guaranteed to be consistent: if some of the files change during the process, what version will be included in the zip is up to anyone's guess. For the same reason, filesystem backups done with tools like rsync (among others) are better than nothing, but they're not guaranteed to restore into a consistent state. In fact, if you're backing up a live database by copying the files, it's pretty much guaranteed to result in garbage.

"Snapshot" is the solution to the above problem. The snapshot is not data you can see directly, it is a feature offered by a database or filesystem. When you ask it to take a snapshot, it gives you a virtual view of the data, frozen in time, as it was when the snapshot was taken. Unlike a backup, which is a bunch of files that exist independent of the application they came from, a snapshot only exists as internal state within the system. It's basically a time travel portal that allows you to look at your data as it was when the snapshot was taken. So the snapshot itself cannot be copied to another machine, because it's not a file. But we usually also call "snapshot" the data you can read through this time portal and actually copy, so you could say you "copied a snapshot" meaning you copied the data.

Usually you can access your snapshot in the same way as you access your data, for a filesystem the snapshot will present as a read-only filesystem, for a database it will look just like a read-only version of the live database with SQL queries, etc.

Implementations are system dependent, but the general idea is that when a write occurs, the data that would be overwritten but can still be seen by any snapshot taken in the past cannot be simply overwritten. Instead the system has to keep separate versions of the modified rows, and keep track of which snapshot can see which version.

All multiuser transactional databases must make snapshots in order to work. Usually it's called "transaction isolation level" which determine when these are made. For example, a snapshot can be made at the beginning of each query. Then the query executes without seeing modifications to the tables that are done while it runs. Otherwise the query wouldn't have a consistent view of the database, foreign key relations would break, etc.

Now once you have your snapshot, you can do several things with it:

  • Query it

  • Roll it back

You can take a snapshot, then do some modifications. If you don't like the result, roll back to the snapshot.

  • You can back it up

If you run your normal backup program on your filesystem or database snapshot, it's guaranteed the data it sees won't be modified during the copy process. Therefore, your backup will be consistent.

For example if you do a ZFS snapshot and then zip it or rsync it to another machine, then that backup will restore your data exactly as it was. If the data comes from programs that write files without crash recovery or transaction logs (ie, MyISAM) it will still be unusable, but that's not the snapshot's fault.

Most systems that allow snapshots for backup purposes will offer a faster way to do that backup than copying files or dumping SQL. That usually results in a huge file that contains all your data.

  • You can make incremental backups

Because snapshots imply version tracking, if you take a snapshot on Sunday and make a full backup of it, then take another snapshot on Monday... then, usually, the system will offer the possibility to do an incremental backup: that's a file that contains only the changes between the two snapshots. If you have huge data but only a small part of it changes, this will make your backups much faster.

On restore, you would restore the full backup from Sunday. Then you can stop there, or restore the incremental backups (in order) to get the data back to the point in time you want.

  • You can fork it or branch it

The snapshot being read-only is not a requirement. Nothing stops the developers from making it read-write. In this case it will not change the data as it was when the snapshot was taken, but simply fork it into a writable copy, while not having to actually store it all twice. I don't think any SQL database allows this kind of time travel paradox in their database, but this is how all source code version control systems work, like git for example.

Anyway, I hope that clears things up. A backup is a bunch of files. A snapshot is a time portal.

I have no idea what an "image" of a database is supposed to be.

It's a bit fuzzy since the term can be applied to both a snapshot or a backup. For example a "disk image" like .iso is a file that contains the whole contents of the disk. So it's a backup of the disk. Some also call a snapshot an image too. But the "image" term also conveys the notion that you'll be able to access it directly like it was the real thing, for example you can mount an ISO, but you can't run SQL queries on a database SQL dump, which is just a bunch of text files.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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