Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sebdee28
Starting Member
3 Posts |
 Posted - 2008-04-16 : 13:53:10
|
| Hi,Is there a preferred backup order for backing up data and log files?Which is preferable to backup first?Thank you,Seb. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-16 : 13:56:46
|
| Well they should occur at different intervals. We perform full backups nightly and transaction log backups every 15 minutes.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
sebdee28
Starting Member
3 Posts |
Posted - 2008-04-16 : 15:18:41
|
| Hi TK,We're not doing any log shipping or replication.Just straight nightly fulls followed by log backups and a weekly cleanup job.I was wondering if the order had an impact on log truncation? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-04-16 : 15:43:45
|
| The full backup does not affect the logs - space is only released by a log backup (or truncate).The full backup also includes a backup of the log needed for recovery so it's not just a data backup.Don't think of the full (or diff) backups being before or after the log, they are not really connected.You need an initial full backup to take log backups but after that ....It sounds like you're only taking one log backup per day? You could take a log backup just before and just after the full - but if you're doing that why are you taking log backups? What is your recovery strategy?I would at least consider taking one during the day - usually every 4 hours on very low transaction non-critical systems, more frequently depending on criticality and volatility.For batch systems I would tend to use a simple recovery model and rely on full and diff backups and design to be able to replay all batches.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2008-04-16 : 16:38:25
|
quote:
Originally posted by sebdee28
Hi TK,We're not doing any log shipping or replication.
That doesn't matter. You should be backing up your transaction log on a much more frequent basis than just after the full backup. You have the potential of losing 24 hours of data with your current schedule.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
sebdee28
Starting Member
3 Posts |
Posted - 2008-04-17 : 10:02:58
|
| >> Don't think of the full (or diff) backups being before or after the log, they are not really connected.>> That doesn't matter. You should be backing up your transaction log on a much more frequent basis than just after >> the full backup.Thanks for the clarifications, I was under the impression log and full backup order had an impact on how the transaction log released free space. Good to know one doesn't affect the other.As for regular log backups, I will definitely have to revisit our recovery strategies.Thanks again,Seb |
|
|
|
fedrar7321
Starting Member
2 Posts |
Posted - 2008-06-03 : 02:50:40
|
| Hey you should go through http://www.diskemergency.com atleast once as I think it will benefit you . It is one of the well known data recovery service provider which retrieve data from any drives or disks. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2008-06-03 : 03:11:30
|
| Hey man...stop spamming the forums. You'll be banned in no-time!--Lumbago |
|
|
|
|
|
|
Backup order for data and log files