SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Database backup using Veritas
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 06/15/2005 :  08:00:04  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Bobbie writes "Our organization uses Veritas software to backup our systems. Databases are included in this backup. I am wondering if we should be doing seperate database backups. Our transaction logs have to be manually truncated. But besides that, what are the ramifications and/or benefits of a database backup vs a total system backup? We run SQL Server 2000 on Windows 2000 Pro.

Thanks in advance!!"

Thrasymachus
Constraint Violating Yak Guru

USA
483 Posts

Posted - 06/15/2005 :  08:34:32  Show Profile  Reply with Quote
I do not know much about Veritas, but I am little concerned about your recovery plan because you say you have to manually truncate your transaction log. I assume you are using the FULL recovery mode, and that you are not making transaction log backups because t-log backups would truncate the transaction log.

The gotcha here is that t-log backups allow you to restore to the point of failure. If Veritas allows you to do this or you do not care about losing the data since your last backup, then you are OK.

I never understand why people buy 3rd party software that does the stuff sql server does well.

One last footnote, over at dbforums a month or 2 ago there were 2 posts by people who got burned restoring from Veritas on the same day.

Sean Roussy

Please backup all of your databases including master, msdb and model on a regular basis. I am tired of telling people they are screwed. The job you save may be your own.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15659 Posts

Posted - 06/15/2005 :  18:49:12  Show Profile  Visit robvolk's Homepage  Reply with Quote
I have to agree with the previous statement. Vertias, Backup Executive, and several other major backup vendors seem to equate backing up the SQL Server data and log files with backing up the database; this is not the case. I have dealt with more than a few failed database restorations using 3rd party backup tools such as these, all of them related to the transaction log. I know at least one of the top 3 big vendors (can't remember which) forces a log truncation before it backs up the database, which effectively ruins any log backup plan you may have and your ability to restore to a point in time. I also know that one of the top 3 has a SQL Server backup "agent" that is supposed to perform true database backups, but does not.

I would recommend using the standard SQL Server BACKUP command over Veritas, etc. Or you could purchase a specialized SQL Server backup tool like SQL LiteSpeed:

http://www.imceda.com/

Idera also has a backup tool specifically for SQL Server:

http://www.idera.com/

These tools are not standard file backup tools, and they work correctly with SQL Server's internal backup/restore processes. I have gotten excellent results from SQL LiteSpeed, in its compression features, speed, and especially reliability.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 06/15/2005 :  19:43:13  Show Profile  Reply with Quote

I am not a fan of direct-to-tape database backups using the Veritas, ArcServer or other SQL Server backup agents for several reasons:

1. The tape drive is usually a shared resource, so it may not be able to backup a database at the point in time that I want it backed up. Also, a tape drive can only backup one database at a time, and I may need to backup multiple databases at the same time.

2. Restore requires the use of the tape backup software, and the last thing I want is an NT backup admin restoring a database.

3. I like to have the current backup on disk, because it is the one that I am most likely to need to restore from, and I usually need it in a hurry. Tape has a higher media failure rate than disk, so there is a higher safety factor in a restore from disk. Also, if the tape drive is in use, I may have to wait until its current operation is complete to start a restore.

I prefer to backup the database and transaction logs to disk with a regular SQL Server backup, and then have the backup files saved off to tape on a daily basis. It requires more disk, but I think that the safety of the database has to be the first consideration.

A backup of the physical disks containing the database files is not an adequate substitute for SQL Backups. Only a SQL Server backup and log restore is dependable method for recovering a database.

I recommend that they do not attempt to backup the database files directly to tape, unless SQL Server is shutdown. Some backup software will attempt to backup open files, and will fill the disk with cache files as it attempts to gather all the changed data blocks in an active database.


CODO ERGO SUM
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36823 Posts

Posted - 06/15/2005 :  19:45:51  Show Profile  Visit tkizer's Homepage  Reply with Quote
Michael,

Agreed on everything!

I bookmarked this thread so that I can refer to your post when someone asks a similar question as you've summed up everything bad about backing up directly to tape.

Tara
Go to Top of Page

Bobbie2004
Starting Member

USA
1 Posts

Posted - 06/17/2005 :  11:53:58  Show Profile  Reply with Quote
Thanks for your help!

Bobbie
Go to Top of Page

drtherc
Starting Member

USA
19 Posts

Posted - 06/22/2005 :  16:39:35  Show Profile  Reply with Quote
Another caveat is that Veritas doesn't back up open files, so unless you shut down SQLSERVER first, you're not really backing up the .mdf and .ldf files, unless you have the SQL snap-in.
Suggest that you schedule your backups through SQL Server, then back up the backup directory with Veritas.

David
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36823 Posts

Posted - 06/22/2005 :  16:47:59  Show Profile  Visit tkizer's Homepage  Reply with Quote
Yes that's true, but the point is to not backup the MDF or the LDF at all. If you are going to use Veritas to backup SQL Server, then you need to use its SQL backup tool. So you are getting a BACKUP DATABASE and not just a copy of the MDF and LDF. But just don't use Veritas accept to backup your database backups on disk to tape.

Tara
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000