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 |
|
triton45
Starting Member
16 Posts |
 Posted - 2007-07-23 : 10:02:44
|
| I come from an Informix background. In Informix, when a logical log is filled (similar to transaction log except there are multiple logical logs) the server kicks off a script. In my environment, this script checks for to see if a certain number of logical logs are filled and then starts a backup of those logs.I am new to sql server and looking to implement a similar situation in sql server. Most of what I have seen is a script that backs up the transaction log at timed intervals. The first problem with that is that it does not take into account the amount of activity in that given time period. The second is that should the server become unrecoverable between timed backups, the data for that period is lost.Any suggestions? If I am missing something obvious, please point it out.Thanks,Andy |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-07-23 : 10:46:47
|
| ok,1. you could build a script that looked at how full your tlog was and then kicked off a backup. You could run it every minute if you wanted to. Seems like a lot of work though when you can just schedule a tlog backup to run every 15 mins instead.2. This is the biggest limitation of SQL server imho. If your server was destroyed, or the datafiles became unreadable in some way, you could lose all the data since your last tlog backup. There is no easy solution to this problem unfortunately. You could implement log shipping, database mirroring or even replication for DR. More frequent tlog backups could also be considered to limit the potential data loss.-ec |
 |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-07-23 : 13:41:03
|
| You can do db and log backup with maintenance plan, run those job in scheduled time. |
|
|
|
triton45
Starting Member
16 Posts |
Posted - 2007-07-23 : 13:44:51
|
quote:
Originally posted by eyechart1. you could build a script that looked at how full your tlog was and then kicked off a backup. You could run it every minute if you wanted to. You could implement log shipping, database mirroring or even replication for DR. More frequent tlog backups could also be considered to limit the potential data loss.
Thanks for the suggestions.Andy |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-07-23 : 13:51:52
|
| Its swings and roundabouts, isn't it?In SQL Server you could set a backup interval of, say, 15 minutes.If the server was mega-busy you would lose a lot of data.However, it you did a backup every, say, 10MB of log data at a quiet time you could lose several orders that had been on the system for several hours. That would be embarrassing ...Kristen |
|
|
|
|
|
|
|
|
Enterprise backup