how to force database to update date modified for the DB file

[Former Member]

I am using ASA 12.1 build 3501 and database size is about 10 GB and trying to use offsite backup solution called Mozypro where they can take the modified portion of the whole file and take the backup at scheduled event as there is now way we can take 10 GB file backup on ftp every night. the problem is they rely on "Date modified" of the .DB file and I don't see the "date modified" being changed so frequently, What statement or utility I can schedule to run so it updates the date modified for DB file?

[Breck_Carter]

VSS notwithstanding, I strongly suggest you do not attempt to use MozyPro to back up a running SQL Anywhere database... that comes perilously close to creating a "single point of failure" with both the MozyPro process and the database server depending on the same file... and MozyPro is running across TCP/IP, right? Nothing can go wrong with that go wrong with that go wrong with that... 🙂

Instead, use SQL Anywhere itself to create a full backup on the same hard drive, or another local hard drive (10G is not a lot of data), and then let MozyPro back up the resulting files: the backup copy of the *.db and the backup copy of the *.log. Those files will be closed, nice and stable, with up-to-date timestamps.

You can do the same with incremental backups of the log file: do a local backup, and then let MozyPro have its way with the resulting files. FWIW I use Carbonite, same idea, works fine.

Divide and conquer: the server updates the database files, the backup process creates the backup files, MozyPro handles offsite, and if necessary the server handles recovery.

[Former Member]

Pardon me for jumping in, I have similar situation that is not working. So what good is VSS if you can't backup a running database. My customer has a DSL internet connection and the database is 4.5g. It is taking all night to backup just the one dump file so I was looking for an alternative. I don't get the idea of backing up only the log files because it would take forever to recover if the database was lost unless the database is backed up every night.

[Breck_Carter]

If the log file grows in size rapidly, then it makes sense to back up the full database file more often. If backing up the full database file every night makes sense, then DO IT, and do not let the speed of an internet connection stand in your way... back up the database to another disk drive on the same computer, or an external USB drive, or a disk drive on another computer on the local LAN. Do the offsite copying on the local backup files... then, it doesn't really matter how long Mozy or Carbonite or whatever takes, does it?

4.5G is not huge, and nothing about the backup and recovery procedures should "take forever"... except maybe the worst-case scenario when your primary data center is utterly destroyed and the offsite backup must be recovered.

Under NO circumstances, however, should your offsite backup be your primary backup for the much-more-common scenario of local data loss (lost file, corrupted file, failed drive, etcetera).

[reimer_pods]

I strongly support Breck's point of view. A local backup scenario should be implemented (and tested!) even if you want to store a remote copy of the database.

If the connection speed is sufficent, a database mirror (using HA option), configured with asynchronous connection if needed, might be helpfull to get the latter.

Just my 2 cents ...

[Former Member]

I already had a local backup setup. My question was really what good is a VSS service if it is not reliable to use?

[Breck_Carter]

Your question should be, "Is a VSS backup service reliable when used on an active SQL Anywhere database?"

The answer, since there is no such thing as a "SQL Anywhere VSS Writer", is "probably not." For a discussion of VSS with Oracle, and a description of the "Oracle VSS Writer", see http://docs.oracle.com/cd/B28359_01/win.111/b32010/vss.htm

Personally, I have no idea if Mozy works directly on a SQL Anywhere database, nor do I care... since a perfectly good alternative works: let SQL Anywhere's dbbackup create a local backup, and then let Mozy copy that file.

[jeff_albion]

The Microsoft Windows VSS service is reliable to use, but you have to configure the SQL Anywhere service to integrate into it correctly. (As the previous question mentions, we still need to coordinate the database server memory contents and ensure the correct data makes it back to disk at the point of the VSS snapshot - this is the point of the SQL Anywhere VSS service).

The SQL Anywhere VSS service is not configured by default however - if you did want to configure SQL Anywhere to use VSS, you must also run the "SQL Anywhere Volume Shadow Copy Service" (dbvss12.exe) utility, as explained in the documentation.

[Former Member]

I have configured the service but it doesn't seem to work. BTW, I am using version 11. Maybe I should have started another thread and not hijacked this one. Thanks.

[VolkerBarth]

As the VSS service should work with v11, too, I'd really suggest you raise another question on this "doesn't seem to work" problem...

[VolkerBarth]

@Breck: Another good reason to re-read the complete doc set:)

[MarkCulp]

Short answer: it is not possible to do this easily with a 100% guarantee.

In order for the database file to get a new modification-time timestamp the server must update the meta data for the file. This is an expensive operation and therefore all attempts are made to keep this operation to a minimum.

The only time that I know of that the server will update the file timestamp is when it needs to grow the size of the file, and this is not done very often due to the way that the server will reuse space (when available).

Note however that the timestamp on the database will be updated when the database is stopped.

---

To answer your underlying question: "how do I do backups of my very large database?", the answer is you should (a) take an initial backup of the database and transaction log file and then (b) periodically take incremental backups of the transaction log file. Please read the section in the documentation on Backup and Data Recovery if you have not already done so. This section talks about how to create a backup and recovery plan. Make sure you TEST your plan!

[regdomaratzki]

I'd also be wary of any third-party tool backing up a running SQL Anywhere database engine. It's quite likely that changes made since the last checkpoint of the database engine are only stored in cache, and not on disk, so a snapshot of the database file at a given time has the possibility of being in a inconsistent state.

[VolkerBarth]

Besides Mark's explanation, if you want to backup a running database, I would like to

• suggest an incremental backup where only the log has to be copied regularly (and the full database file only now and then) and
• hint at this FAQ with a somewhat similar question - and the general answer that relying on a 3rd party tool to create a reliable backup might be difficult, at least...

A 3rd party tool that tries to identify via file-modification-time whether a file has been modified at all might not be able to backup the "modified file portions" of a running SQL Anywhere database file and log file, I suspect.