OS 3rd Party Backup Tool - Why Did This Work??

Question

OS 3rd Party Backup Tool - Why Did This Work??

Jeff-67583

SSCertifiable

Points: 5625
More actions
February 24, 2004 at 9:50 am

#107091

Hi:
OK ... I've been told not to rely solely on OS backup files of the data and log files for recovery purposes re: open databases could be corrupt, but please convince me after this little story:
MSSQL 7; NT4; approx. 20 databases; using VER***S OS bckup software to copy data (. mdf) and log (.ldf) files to tape. Had a power disruption that crashed the drive heads on the db server. In addition, a corrupt OS had to be re-installed. Lost the registry info. MSSQL had to be re-installed. Then COPIED from tape using OS backup the .mdf and .ldf files back to disk. Started up the database services ... everything worked fine!?? Recovery was very quick??!! Did not have to use the MSSQL backups?
Why did this work??!!. At they very least I was expexting the databases to be marked as "Suspect" for the active databases? Does this vendor's OS backup software tool somehow "close" the open databases (forces a checkpoint)? (Note: when the OS backups process occurs, it does not stop the db services).
Any thoughts on this would be greatly appreciated. Thanks Jeff.
Many thanks. Jeff

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

SQLBill SSC Guru Points: 51440 More actions · Answer 1

From your post, I'm guessing you use Veritas Backup Exec (which I also use). Do you have the Agent for SQL? (In BE, select Help>About Backup Exec and look at the licenses). If so, the agent does the same thing a SQL Server BACKUP command does.

If not, did something else stop the services? Check the Windows Event Viewer logs, they will show whether the SQL Services stopped and when they stopped.

BE doesn't normally stop the SQL services. I'm guessing something else stopped them prior to the backup.

-SQLBill

Steve Jones - SSC Editor SSC Guru Points: 734449 More actions · Answer 2

There are a few possibilities:

1. nothing was "happening" in the db as far as writes were concerned, so even though the files where open, the mdf and ldf matched, so no corruption.

2. you got lucky.

I wouldn't depend on these unless you keep a very current resume or CV handy

Jeff-67583 SSCertifiable Points: 5625 More actions · Answer 3

Hi,

Veritas Backup Agent for ver 8.6.

Nothing in event viewer or MSSQL error logs ... ?

Many thanks. Jeff

Jeff-67583 SSCertifiable Points: 5625 More actions · Answer 4

Hi,

How is the OS tool able to make copies? I've run tests using win explorer and receive "sharing violations" when i try to copy the data files to another loaction (to mimic the OS backup to tape procedure).

Many thanks. Jeff

Jeremy Huppatz SSC-Addicted Points: 420 More actions · Answer 5

I'm going out on a limb here and making some guesses based on products that were still in developent a couple of years ago when I last reviewed them. Sticking to broad brush strokes... I believe that the way this tool works is that it caches writes to the database to a different part of the disk via some kind of proprietary system, allowing it to keep the database itself more or less read-only while it streams the backup of the underlying files to tape.

As I understand it this tool was written against - and significantly extends - the SQL Server Backup API to allow this to happen without disrupting SQL database usage during tape backups. Because the disk heads weren't writing to your SQL database files at the time the backup took place your MDF and LDF files were okay.

This is relatively new tech (last 2-3 years in the SQL Server space) and mainly used by administrators of large high-transaction environments to shrink their backup windows. Sounds like your infrastructure heads have their heads screwed on right.

Jeremy Huppatz

SQL Server DBA

EDS Adelaide Solution Centre

jeremy.huppatz@eds.com

SQLBill SSC Guru Points: 51440 More actions · Answer 6

I use BE 8.6 also and what you experienced is a fluke according to Veritas. The only ways BE will backup a MS SQL Server database are:

1. the database is offline (detached or services stopped) and .mdf/.ldf files copied

2. you have the Agent for SQL installed and are using it.

-SQLBill

Jeff-67583 SSCertifiable Points: 5625 More actions · Answer 7

Hi,

"you have the Agent for SQL installed and are using it."

Agent, is different from MSSQLAgent? This agent is provided by Veritas? How can I verify this "agent" is running?

Many thanks. Jeff

SQLBill SSC Guru Points: 51440 More actions · Answer 8

Open Backup Exec, click on Help, click on About Backup Exec. In that pop-up there will be a window that lists all your licenses. See if there is one for the Agent for SQL. It's NOT the same thing as the MSSQLAgent.

-SQLBill