Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2005 > SQL Server 2005
Password Reminder
| FAQ | Members List | Calendar | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #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.

Reply With Quote
  #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"
Reply With Quote
  #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...

Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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

All times are GMT -4. The time now is 10:27 PM.

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