Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

i know that Microsoft SQL Server nowhere documents that it is safe, correct, valid, or intended to backup the data (.mdf) and log (.ldf) files (*while SQL Server is running). i.e. you have no idea what state those files are in - they're not your files.

Some backup software claims to be able to backup SQL Server data&log files while they are in use.

Nevermind the fact if it (happens) to work - is it intended behavior?

share|improve this question
    
Notably, there are many different options for SQL backup, some of which may be more flexible than Backup Exec. BackupAssist and RedGate are two additional vendors worth evaluating. –  Skyhawk Apr 15 '11 at 15:25
    
Quest Litespeed also –  Holocryptic Apr 15 '11 at 15:46
add comment

4 Answers 4

up vote 3 down vote accepted

Yes, it is valid IF (!) the program fully integrates into the backup AP's that windows offers. Basically they TELL SQL Server to bring the files int oa consistent state and then the file is snapshotte. The term is VSS (Volume Shadow Copy) and there is an API for that. SQL Server supports it.

share|improve this answer
add comment

Typically a program that is SQL aware, will use the VSS writer to take a point in time snapshot of the database. If you start your backup at 11am, and data is entered at 11:01, it won't be included in the backup.

share|improve this answer
add comment

Using a SQL aware backup program (like Symantec BE) is perfectly acceptable. Symantec BE does not back up the mdf and ldf files directly and you'll find no mdf or ldf files in any of your backup sets of SQL server.

share|improve this answer
    
What does backup software like Symantec BE backup if not MDF/LDF? –  Ian Boyd Apr 15 '11 at 14:41
    
I'm not sure exactly how it's done but BE actively excludes the mdf and ldf files from the backups if you select the directories that contain the mdf and ldf files in your selection list. It may simply be using Transact-SQL statements to backup the databases just as if you were doing it from SSMS. –  joeqwerty Apr 15 '11 at 14:46
    
BE has a built in agent that knows how to connect to SQL (via ODBC, OLE DB, SQL Native Client, etc) and do its thing. There are others that do this as well. Veritas NetBackup, I think, is one. –  squillman Apr 15 '11 at 15:04
    
@squillman: Right. Under the hood I'm assuming it's using Transact-SQL to perform the backup, much like you would do from within SSMS. Any idea how it actually backs up the databases? –  joeqwerty Apr 15 '11 at 17:22
    
No, I never actually used the agent to do SQL backups (I'm a LiteSpeed fan myself) so I never really dug into what exactly it's doing. –  squillman Apr 15 '11 at 17:31
add comment

I would not recommend trying to backup the actual files in use by SQL server. We had seen issues with a backup utility locking the actual data files causing unresponsiveness and resulting in the following errors being logged to the SQL error log:

Date Time spid51 Database master: IO is frozen for snapshot 
Date Time spid51 Database master: IO is thawed

See the following:

http://support.microsoft.com/kb/903643

http://sqlbie.wordpress.com/2010/08/31/io-is-thawedfrozen-when-using-3rd-party-backup-agents/

In a similar vein for past employers (including my current) they tend to use something similar via SAN replication where the data files are replicated to a backup SAN at a DR facility.

Actual recovery tests revealed that, even though this was a sector by sector copy of the data files (ldf, mdf and ndf), we did at times encounter corruption due to incomplete sector writes that hadn't completed on the DR SAN.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.