The SQL Server Database backup is not more than a copy of the database, which is created as a kind of insurance policy in the situation when the original goes away. However, a database snapshot is not a backup as most of the users think, but, it is used to create a “copy” of the database and then a user can restore from that copy when required. As most of the users are not clear about the two and their difference. Therefore, let us differentiate between the snapshot and backup from SQL Server database point of view to have a proper understanding and choose right one at right time.
In SQL Server 2005 snapshot backups were introduced, which is available in the Enterprise versions of SQL Server also. It can also be said that it is a live backup. Whenever a user creates a snapshot it generates a read-only copy of the database. However, it is created by maintaining the copies of the database pages that are modified, but modified pages only. Or a user can say that it is copied to the snapshot before a page gets modified. Due this snapshot look like a backup containing a constant copy of the database. It is used as an alternative to backup in most of the situations.
It is always suggested that a user can use it when there is a requirement of short-term backup of the SQL Server database. Let us consider an example, a user can take a snapshot of a production upgrade so that it becomes easy to rollback any changes made during database modification. It is because snapshot allows users to revert the database back to the state at which the snapshot was taken. In addition, taking a snapshot is basically faster than backing up a SQL Server database and restoring the snapshot.
A SQL Server database backup is used to create the copy of the database, which is used to restore the data back whenever required. The backup files are the specific type of copy, that knows the transactional nature of SQL Server very well. This copy will be created in situations where transactions are ‘in process,’ and not completed yet. However, copying the files simply will not handle the transactions and may result in serious data corruption. Therefore in most of the situations, it always suggested to use the domestic backup processes or, third party tools that work directly with the domestic processes, for example, Red Gate SQL Backup is used.
Situations when a user can take Backup:
Situations when a user can use Snapshot:
After understanding the difficulties faced by users in deciding between snapshot and backup, we have discussed all relevant information regarding snapshots and backups. In this post, we have discussed the difference between the snapshot and backup of SQL Server database so, that it becomes for a user to choose between the two according to the situation.