Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
SQL Server 2005 General discussion of SQL Server *2005* version only.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2005 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old December 26th, 2006, 08:49 PM
Registered User
Join Date: Mar 2006
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Backup help

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.

  #2 (permalink)  
Old December 26th, 2006, 09:23 PM
Wrox Author
Join Date: Dec 2006
Location: kirkland, wa, USA.
Posts: 17
Thanks: 0
Thanked 0 Times in 0 Posts

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"
  #3 (permalink)  
Old December 28th, 2006, 05:11 AM
Registered User
Join Date: Dec 2006
Location: Belgrade, Republic of Serbia, Yugoslavia.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts

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...

Similar Threads
Thread Thread Starter Forum Replies Last Post
Need Help - BackUp and Restore sivap SQL Server 2005 5 July 13th, 2007 08:51 PM
Create backup folder & also backup files miracles Excel VBA 0 April 16th, 2007 02:28 AM
Backup scandalous Access 3 February 9th, 2007 01:19 PM
What's the best way to backup? thomasks Visual Basic 2005 Basics 2 September 13th, 2006 03:36 PM
BAckUp sachindhillan Oracle 2 September 28th, 2003 07:32 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.