How important is it to stop SQL server before creating an image?

Can someone tell me, how important it is to stop an SQL server instance before making a disk image? If data is being written to the SQL server during the backup will it result in a corrupt SQL database in the backup, or just that it's not completely up to date? Thanks! -- Mike

 

Hello DeeMikey,

Thank you for your interest in Acronis Disk Backup Software.

We recommend that you suspend SQL data base because different entries of data base can appear to be of different time. Since these entries can be interlinked with each other in a complicated way the suspending of data base would prevent the possible corruption (when, for example, entry1 is modern and entry2 is a little older). Please note that you do not need to suspend the data base for all the time the image is being created. In fact, you need to suspend it before you press the "Proceed" button and resume after you see the screen with the progress bar (this time is about a second).

Thank you.
--
Ilya Toytman

 

It depends on how you have installed & configured the MS SQL Server instance, and much work you are prepared to do after a restore.

I must admit that I have no experience with MS SQL Server, but I assume that it works like any other transactional database server. This means that every database update/transaction is done in a two-step fashion:

1. Changes are commited to the database.
2. Changes are written to a transaction log.

If you need to restore such a database, the first question you need to answer is this: Restore to which point in time? Yesterday 08:00 AM? Today 1:57 PM?

Once you know the point in time to which you want to restore the database, restoring can then be done by:

1. Copying a known, good database to disk.
2. Copying the transaction log(s) created since the known, good database was backed up. (*)
3. Applying the changes in the transaction logs to the database, all the way to the point in time you decided.

If you do it this way, you never need to shutdown the database. But you DO need to determine to which point in time you need to restore the database, and that can often be more difficult to do than to shut it down for a short time

(*) Since the transaction logs are critical to the restore, yet constantly being updated, you should keep the logs on a different physical disk than the database itself.

 

Most MS apps are VSS aware (Volume shadow copy)

Symantec Livestate recovery already uses this to put databases in "quiet" mode as the snapshot is being taken.

It's a solution for MS SQL, Exchange, ...

Alternative is to use TI Enterprise, schedule before and after snapshot scripts.

However, this is also known as an "offline" backup, and for exchange, the transaction logs are never erased (as SQL or Exchange aware backups do after a succesfull full backup of the database. At that point the old logs are irrevelant)

That being said, the active directory is also a kind of database, and I never "stopped" that one during sector based backups. I even restored a full server backupped with a running exchange, without error. It replayed the logs on the other disk, and it ran without problems.

 

Thanks guys! A couple questions in response to the comments:

I don't suppose there's anyway to do this automatically or script the stop/start, other than with the options to run scripts before/after the snapshot, is there?
Also, if it's not too complex to explain, what process takes place here which makes it "backup-safe"?

I presume that TrueImage doesn't do this, is that right?

MiniMax, just to get it straight, you're saying that the database should be fine but the transaction logs might get corrupted? Or just that you need valid transaction logs in order to restore an otherwise corrupt database?

Thanks! -- Mike

 

No. What I am saying is this:

Valid database backup no. N + Valid transactions logs since N =>
Valid database N+1
​

If your database is corrupt, there is no point in trying to roll it forward using the logs. In that case, you no to go back to the latest non-corrupt DB, and apply ALL the transactions since the non-corrupt DB backup was created.

 

Deemikey,

Database and transaction logs are a system to get back to your latest alteration on a database, so you can recover a machine until the very second your disaster happenend. This can be done as long as the database *or* transaction logs are intact, even when you only take full backups every day, or week, or...

Look:

Every change you make to the database (transaction), the database is changed, and a "log" file is created that reflects this change as well. So the "transaction" is written to two places. Database itself and logs.

Fact: A backup that "knows" the database, will backup the database fully, then delete the logs. This is an "online" backup. The Database goes to a "quiet" state until the backup is completed (meaning, only writing to the logs, leaving the database alone, until backup completed, then it writes the logs to the database) - this makes sure the backup is "good"

Remember database and logs are on different disks.

So, here are your disaster possibilities:

A) The Log file Disk crashes. No problem. Replace the log disk. The database disk is safe, so your data is safe until the crash point. The system resumes writing transactions to log & database.

B) The DB disk crashes - uhoh? No. Replace the DB disk. Take your last backup (lets say it's a week old) that you made of the database. You know have a database that is 1 week old. The database manager sees that there is an inconsistency between DB and logs. The database manager starts to "replay" the logs, that contain all the transactions between the last backup of the DB and "now", altering the database so it's again up to date.

You didn't lose a single transaction in either process! THAT is the power of transaction logging.

In case of Exchange, it would mean you didn't lose a single e-mail, even when your last backup was a day, or maybe a week old.

Downside? You *have* to backup or your log files will grow very very large...

Your other question:

Snapshot based backups have a technique where they "snapshot" how the sectors are laid out, in the beginning of the backup process. ("before" snapshot) - "After" snapshot, those sectors are backed up. If another program tries to alter a sector marked for backup, it gets written elsewhere (in free space) - this way, the backup can take for example 4 hours, but it's perfectly consistent to that one point the snapshot was taken.

this means you could stop a database completely (go offline) and backup it like that. It's always consistent this way. Now, because the snapshot only takes a few seconds, it's enough to stop your databases for the time of the snapshot. Acronis lets you run batch scripts before and after snapshot.

LiveState is VSS aware, and this is a technique where the database knows it's about to be backupped. No need to go offline, the database goes to quiet during the snapshot, replays the logs after snapshot, while the BU is running.

I'm not a database manager, but this is the extent to which I understand databases and backups.

 

Hello DeeMikey,

The detailed description about how Acronis snapshot technology works is described at this article. If you have further questions about it please feel free to ask.

As for service suspending, using Acronis True Image 8.0 and Acronis True Image 8.0 Server you can do it only manually. However, Acronis True Image 8.0 Enterprise Server and Acronis True Image 8.0 Corporate Workstation do support launching scripts you wish before the image creation starts and after the snapshot is taken (roughly speaking, these scripts are usually: stop data base and start data base).

Thank you.
--
Ilya Toytman

 

Thanks for your helpful explanations, I think I understand the process more or less now

Just one question Tonio, out of interest. You said, "If another program tries to alter a sector marked for backup, it gets written elsewhere (in free space)". So that means that during the backup process any edits aren't actually being written to their eventual destination, but the OS has to be aware that a backup is taking place and then afterwards rearranges everything as needed? Just curious.

Okay, now I have a problem. I'm trying to add pre/post snapshot scripts to my scheduled backup operation, and it doesn't seem that I'm able to do this anymore. I'm using Enterprise Server build 1,143 which I think should handle this. The build I was using before (build 826, I think) did have the option to a script before and after snapshot. Do I have the wrong version or something?

Thanks! -- Mike

 

That is not how I understand the process. AFAIU, it's more like True Image (or rather, the infamous snapman driver) is notified by the OS that a sector is about to be modified. In response, True Image hurries along and grabs the data from that sector. TI then tells the OS to go ahead and write the sector.

Now, I don't know if TI then writes the data to the image (with a little note saying "this sector is a bit out of place, fix it when we need to restore"), or if it just keeps the data in memory and waits until it gets to the point where it would have gotten the sector data of the disk.

 

MiniMax is right, I was wrong about sectors being written "elsewhere"

TI has the snapshot in memory while the backup runs. There is a "filter" driver in place between the programs calling to write and the OS.

If a sector marked for backup is about to be overwritten by a program, the backup process checks if this sector is already copied to the backup. If yes, it doesn't matter if it gets overwritten (as we're doing a backup *at* the time of the snapshot) - if it's not, the sector's data is being written to the backup immediatly, *before* the new data is allowed to be written on the sector.

I've got this information from "Drive Snapshot", a lowcost alternative to sector based backups.