My database is being backed up by a person who feels that it is only necessary to backup the .mdf and .ldf files. Essentially the .mdf's are being backed up nightly and the .ldf's are being backed up every 3 hours during the course of the day. He insists that I do no backups via SQL server, and especially no transaction log backups as that interferes with his backup of the .ldf file. Is this a good practice or, is it as I suspect, dangerous. Any advice is welcome, along with explanations please.

If he is copying ALL the .mdf and .ldf files, AND SQL Server is NOT running while th copy occurs, then this is a form of relatively safe backup, but its certainly not recomended, nor a very usable way of backing up the system.

For example, its the most expensive way to do a backup as you have to stopthe server, AND copy ALL Db files to ensure a consistent backup. If the server is running, OR any DB files are NOT copied then any attempts to restore the system from files copied while SQL is running, OR if any DB files are missing may result in an inconsistent DB thats effectively worthless.

The safest way to do the backups is to use the built in SQL server backup options.



Steve Wort
Co Author "Professional SQL Server 2005 Administration"

Using that backup strategy (file system backup):
1. You cannot be sure that it backups right (what if you're destination disk have some bad blocks, and OS reports no errors during file backup process).
2. You cannot backup database while it is online and being used actively
3. You cannot recover you're data in point in time
4. You cannot be sure that you're backup files were not modified since backup
5. You cannot use all hot and new backup and restore options (just to mention few: online page, file and filegroup restores). Just imagine that some pages becomes corrupted, in that case you will lose a lot of informations and valuable work.


Not just you have to manually truncate transaction log file (if you're using Full or Bulk logged recovery model), but you database in case of failure may become inconsistent. You cannot recover you're database in point in time and use all good benefits from transaction log backup...

Preferred way is to decide which backup strategy most apply to your organisation, and use it, and test it regularly.

SQL Server 2005 provides very useful options for ensuring that you're backup copy is reliable and safe, you can use:
 Verify backup when finish to make sure that it is backed right and fully readable (you cannot do that from copying pure .mdf and .ldf files)
 Perform checksum before writing to media (see BOL for more details),
anytime you can check to see if your backup files are readable...