1

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?

2
  • 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, 2011 at 15:25
  • Quest Litespeed also Apr 15, 2011 at 15:46

4 Answers 4

3

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.

3

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.

2

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.

5
  • What does backup software like Symantec BE backup if not MDF/LDF?
    – Ian Boyd
    Apr 15, 2011 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, 2011 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, 2011 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, 2011 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, 2011 at 17:31
1

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .