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 |
|
maranbe
Starting Member
28 Posts |
 Posted - 2006-09-25 : 05:49:03
|
| Hi,Is it alright to run t-logs backup while complete or differential backup is still running in the background?Objective is zero data loss and faster recovery time, which of the schedule is appropriate?1. full backup (weekly), then t-logs afterwards (round the clock, every 15 minutes), diff (daily)2. full backup then differential then t-logs (round the clock,every 15 minutes)3. full backup at the same time running the t-logs continuously, also while running diff backup (is this possible?)Kindest regard,SQL Server 2K (Clustered)/2K5_________________________Our life is frittered away by detail ... Simplify, simplify. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-09-25 : 06:02:30
|
| Think it will be blocked until the other backup is finished.Try it and see.==========================================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. |
 |
|
|
maranbe
Starting Member
28 Posts |
Posted - 2006-10-01 : 21:05:15
|
| Thanks NR.Kindest regard,SQL Server 2K (Clustered)/2K5_________________________Our life is frittered away by detail ... Simplify, simplify. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-10-02 : 07:53:27
|
| I think (1) is what you need.I see no sense in doing a DIFF until 24 hours (if that's your interval) after the FULL.I have set (accidentally or otherwise!) all types of backup running when they were already running and my request just got stalled until the previous task finished, so I don't think you will have a problem with that.What we do is have a table with the names of the databases and "next run time" for each of Full, Diff and Tlog. We also store the required interval for each of these - e.g. 7 days, 24 hours, 15 minutesEvery backup period (15 minutes in your example) teh scheduler kicks off an Sproc that checks each database in the BackupTable and:Checks if TLog backup overdue:Y -> Make TLog backup, advance NextRunTime for TLogChecks if Full backup overdue:Y -> Make Full backup, advance NextRunTime for both FULL and DIFFChecks if Diff backup overdue:Y -> Make Diff backup, advance NextRunTime for DIFFNote that this approach takes a TLog just before a FULL or DIFF (which in turn means that there is a little less "stuff" to wade through if you need to restore the Full/Diff followed by the next Tlog file)Running the Full backup also advances the Diff backup - so it doesn't run for another, say, 24 hours."advance NextRunTime" means "calculate a date/time which is an aniversary of the "nextruntime" and which is next soonest after the current time".So if you set the NextRunTime of your FULL backup, on a given database, to run at 4am next Sunday, and an interval of 7 days, it will run on Sunday and then the following Sunday at 4am. If for some reason the run next Sunday is delayed, or takes an hour to complete, this will not effect the launch time of a subsequent run.If you shut the server down on Friday and start it back up on Monday on the next 15-minute-schedule it will backup the transaction first, then do the (overdue!) Full, but the Diff won't trigger until early Tuesday morning - the next anniversary.Our routine walks though each database in order, so in practice at 4am on a Sunday it may be a couple of hours before the last databases "ZZZZ" gets backed up.We have an Sproc that makes a backup, and use this for "one off" backups. So if I made an ad-hoc backup of "ZZZZ" at 4:01am the backup would be made immediately (concurrently with the scheduled task backing up "AAAA", "BBBB" etc) and when the scheduled task got ti "ZZZZ" it would consider that it did not need backing up as, assuming it got to it at, say, 6am it would have a NextRunTime of Sunday-week)We also store the filenames of backups and delete them after a suitable period. (Each database has a retention time for Full/Diff/TLog, and each filename generated has a DeleteAfter date calculated from that). Backup files are deleted once they become stale - so the FULL backup is retained until a) its retention date has passed and b) all DIFFs and TLogs generated from it have also past their re tensions date.When we do a "quickie-one-off-Diff" we often set a retention date of 1 day so that it doesn't clutter up the disk for its default retention of 7 daysKristen |
|
|
|
maranbe
Starting Member
28 Posts |
Posted - 2006-10-17 : 01:59:10
|
Thanks all for sharing...Kristen - i will definitely consider your approach.. Kindest regard,SQL Server 2K (Clustered)/2K5_________________________Our life is frittered away by detail ... Simplify, simplify. |
|
|
|
|
|
|
|
|
appropriate time to launch t-logs back