2 Totally Different Ways To Do SQL Server Backup & Recovery

Author: Nick Mueller, Zetta.net 

This post is about how traditional database backup products work and how Zetta’s new SQL server backup feature, which is part of our DataProtect solution, does the job in a distinctly different way. Both the solution and this post were created because finding a database backup solution for a medium-sized organization is painful.

Database Backup Using “Full, Differential, and Log” Modes

Most traditional database backup products use “full, differential, and log” based backup modes. So, what do these terms mean?

A full backup is just what it implies — everything.

A log is essentially a list of the changes the database has made since the last full backup. If you have a full backup from, say, 4/1/12 and logs that cover 4/1/12 – 4/10/12, you can take the original backup, and replay the logs, bringing it current to the 4/10/12 version.

A differential is similar to a log based backup, but can be smaller and faster to recover in that it will have some amount of update coalescing, where if a given record (say a daily account balance) was updated over time the logs would contain each discrete value but the differential would contain only the latest version for that record.

With both of these backup types, it’s necessary to restore the original backup and the differential or log sets. This is what makes it less space and network efficient than a pure incremental backup.

Database Backup Using ZettaMirror with Sub-file Technology

Zetta’s lightweight software agent, called ZettaMirror, leverages sub-file technology to scan the database byte-for-byte to find database pages that have changed, and only transmit the pages that have changed up to the Zetta service.

While this is similar to an incremental backup, we actually restore the changes on our end, resulting in the most-recent backup being available and fully formed, ready for restore. Version history is always available via our space-efficient snapshots, going back in time.

The advantage is that it’s, “incremental forever,” handles any update type, and is the most efficient possible format for a restore.

Backup process of a Microsoft SQL database using ZettaMirror:

1) Connect to the database, inquire if it’s in simple recovery mode, or full recovery mode.

2) Using the .NET SQL Server Manager API, cause the database to de-stage any dirty pages out of memory onto disk, and use VSS to create a read-consistent version for the ZettaMirror Job.

3) Scan the pages across the VSS snapshot for changes, then transmit just the changes up to Zetta’s datacenter.

4) Create a named snapshot recovery point for this database on the Zetta volume.

5) If the database mode was full recovery mode and everything was successful, release the snapshot, and truncate the logs.

Because our methodology of always taking full backups, then using binary comparison to derive the change set, the use of Microsoft or other backup utilities will not cause ZettaMirror — unlike most other products — to produce a backup that can’t be restored.

As you can see, traditional database backup and Zetta’s DataProtect service featuring ZettaMirror are really 2 totally different ways to do SQL server backup and recovery. If you’re ready to try the Zetta way in your environment, you can start a trial here. If you have questions or want to learn more, feel free to give us a callor find out more about how it works.

For parties interested in cloud backup, data protection, disaster recovery, school online backup, online backup, online data backup, online server backup, enterprise online backup, server backup, cloud server backup, snapshots