April 2, 2004 at 2:53 pm
Hi all:
I have developed DB's for many years but over the last year I have been forced to take over all administration tasks as well. In going over the existing backup strategies I find our current practises may be insufficient.
Currently we place database files on NAS devices which, contrary to some reports, performs quite well for us. The problem is that the only backups being performed are backups of the raw data files (mdf, ldf). No actual SQL SERVER database backup commands are being issued.
I believe this might work in some circumstances but I know there must be some problems with this method. Our backup person insists it is fine so hopefully you all can provide me with some ammunition as to why we need to do proper database backups.
Thanks in advance
April 2, 2004 at 3:21 pm
Are you sure they are being backed up? SQL Server will hold the mdf and ldf open just like Word, Excel, whatever. Most backup software doesn't handle this or doesn't do it well. You need to test extensively to be sure that your backup software actually captures the integrity of the file since parts of it could change between when a backup starts and when it finishes. If the backup software handles this correctly, you are ok.
However, there's still a risk. A SQL Server backup (or 3rd party equivalent, Litespeed, SQLZip, etc.) ensures that the backup is transactionally consistent. That any changes to the files (tables, rows) are not written inside a backup that is being run. These are held until that affected parts of the file can be backed up. this is the main reason.
the last thing you want is to restore a file and find out it's corrupt. IMHO, it isn't worth taking the chance. SQL Server backups are robust and bulletproof.
April 2, 2004 at 5:54 pm
In the past I have been at a company that had a NAS device. The NAS had the ability to snapshot the attach resources. In doing this it also did snapshots of the data and log files. The the drives were acquiesced and the logfile checkpoint was set during the snapshot process so recovering those files and attaching the database was not a problem.
--------------------------
Zach
April 5, 2004 at 8:32 am
If you are 'snapshotting' the .mdf/.ldf files, this usually isn't a good thing. I suggest trying to restore one of the 'snapshots' to see if it really works.
There are two 'best' ways to backup SQL Server.
1. Use the SQL Server BACKUP DATABASE and BACKUP LOG commands.
2. Stop the SQL Server services and copy the .mdf/.ldf files, then restart the SQL Server services.
-SQLBill
April 8, 2004 at 2:36 am
Hi there
Any SW that backs up whilst the DB is open and accepting read/writes, is risky and really needs full certification from MS (of course). As a guide, Tivoli flashcopy/PPRC for example is a tool that allows just that with database files; we use it to flash copy drives on the SAN to another; now note here is drives, not picking out selected files etc (its been a while since Ive looked at it in depth). So may be a consideration. Not sure on other tools....
Cheers
Ck
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply