| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-06-15 : 08:00:04
|
| 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
483 Posts |
Posted - 2005-06-15 : 08:34:32
|
| 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 RoussyPlease 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. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-06-15 : 18:49:12
|
| 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. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-06-15 : 19:43:13
|
| 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 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-06-15 : 19:45:51
|
| 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 |
|
|
|
Bobbie2004
Starting Member
1 Post |
Posted - 2005-06-17 : 11:53:58
|
| Thanks for your help!Bobbie |
|
|
|
drtherc
Starting Member
19 Posts |
Posted - 2005-06-22 : 16:39:35
|
| 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 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-06-22 : 16:47:59
|
| 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 |
|
|
|
|
Database backup using Veritas