February 4, 2004 at 1:20 pm
Hi,
What are the pros/cons using an Operating System (OS) Backup/restore strategy (ie: copy to/from tape the MDF and LDF files) vs using MSSQL backup/restore method\command?
Is there a possibility that some of the tables could be "open" when backed up by OS and may be corrupt when trying to restore from OS tape?
THanks. Jeff
Many thanks. Jeff
February 4, 2004 at 6:01 pm
You can’t copy the LDF file while the DB is online. You must take the database into offline and copy it to the tape. If your database is prod & 24/7 I don’t think it’s a good practice. With SQL backup, you can take the regular backup and copy it to the tape.
Shas3
February 4, 2004 at 6:02 pm
I believe if your SQL service is running you cannot copy or backup the MDF's or LDF's.
HTH
------------
Ray Higdon MCSE, MCDBA, CCNA
February 5, 2004 at 8:00 am
Hi,
More background - Our Network Administrator takes an OS copy of all files on the drive to tape that contains the MDF and LDF files. His position is that if a database goes down, he simplies copies back to the drive, the OS files from tape (overwriting the corrupt ones). He believes that performing SQL backups is redundant and duplicateing data storage.
Any thoughts?
Many thanks. Jeff
February 5, 2004 at 8:09 am
I agree with the above, I would even question how they can pull a backup of the MDFs/LDFs since SQL has them open. We have ADSM and it just complains that it can't get access to the files, eventually moves on.
But what state is the DB in when he does his BU. Let's say he's half way through the filecopy and the SQL updates the first data page. Corrupt DB after his restore. Simple example, but you get the idea.
The SQL backup gives you a clean cutoff point.
I recommend that you have him NOT backup the MDF/LDFs, you run your SQL backup before he does the filecopy backup, and have him grab your backup. That is a good solution. It does require extra Dasd, but disk is cheap today. You now have a copy on the server so you can recover quickly if just the DB fails, and a copy off the server in case it fails.
KlK
February 5, 2004 at 8:22 am
To properly backup the .mdf and .ldf files they must be in a detached state. This can be done by stopping all the services or detaching the database(s). If the database is not detached, then reattaching it will fail and the database will be in a suspect or worse state.
So, yes you can backup the .mdf/.ldf files. It's even the best/quickest way to do backup and restore. An detach/attach is much quicker than doing a SQL Server BACKUP/RESTORE command. HOWEVER, as everyone has stated, the database must be offline at the time.
Ask yourself (and management): Can you afford to have your database offline while the backup is being done?
If the answer is yes - then detach the database(s) or take them offline by stopping services and backup the .mdf/.ldf files.
If the answer is no - then do SQL Server backups.
You can test this by creating a database, backing up the .mdf/.ldf files while it's online, then deleting the database and then trying to restore it from the backup.
-SQLBill
February 5, 2004 at 8:54 am
As has been said before, copying a db while it is online is a bad, bad idea.
I once tried this, burned a CD to take it home and spent the whole weekend in vain to bring it back to life.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply