| Author |
Topic |
|
frant101
Starting Member
12 Posts |
 Posted - 2011-09-29 : 10:57:02
|
| Hi,If I had a database with the follow backup routineFull backup Sat 7amDiff Backup Every Weekday 7amTransactional Backup Everyday Midday and 6pm.On Tuesday at 5pm I restore the database to Tuesday midday withFull Backup, Tuesday Diff , Tuesday Midday Transactional BackupMy question is once the restore is complete do I need to do a full backup of the database before the 6pm transactional log backup runs for the backup to be consistent ? Or is this not necessary and the transactional backup should run as expected ?Thanks in advance for your help |
|
|
tduggan
Starting Member
26 Posts |
Posted - 2011-09-29 : 13:02:48
|
| Why are you doing tlog backups so infrequently? You do not need to do a backup after a restore to make anything consistent. But I believe you do need to start the transaction log chain, and in this case that'll mean a full backup. |
 |
|
|
frant101
Starting Member
12 Posts |
Posted - 2011-09-29 : 13:28:41
|
| just an example we are actually doing them every 15 mins my concern was once the db was restored that if i then needed to restore again later in the week i would be able to even though we are only doing one full backup a week and there wouldnt have been a full backup since the original restoreto do this i was guessing that i would need to do a full backup after the restore but really wasnt very sure and couldnt find out much information |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-09-29 : 14:19:16
|
| I'm reasonably sure that you can Restore a Full Backup (plus DIFf and TLog backups if necessary) and then start making Tlog backups and then, if necessary, restore the same FULL + Diff + TLogs that you originally restored, and then restore the Tlog backups since then. But it would need another opinion, or an experiment, to be certain. |
|
|
|
tduggan
Starting Member
26 Posts |
Posted - 2011-09-29 : 15:15:19
|
| Yes you can do that as the LSNs are still intact. |
|
|
|
frant101
Starting Member
12 Posts |
Posted - 2011-09-29 : 15:42:21
|
| thanks for the informationif i had a log backup taken at 7 and 8 , 9, 10 and at 9.20 i restored by database to the 7 oclock backup. then at 10 the log backup happenswould the log chain be the 7 oclock log and the 10 oclock log i.e it disregards the 8 and 9 log backups as no longer needed thanks again for the help |
|
|
|
tduggan
Starting Member
26 Posts |
Posted - 2011-09-29 : 16:01:39
|
| Well they are needed if you need to get to those points in time as you can still restore them using the 7 backup. |
|
|
|
frant101
Starting Member
12 Posts |
Posted - 2011-09-29 : 16:25:03
|
| as the restore happened at 9.20 does the 10 oclock log backup hold all the details of the restore and changes to the data that would have happened through the restore then as after the 9.20 restore the data will have reverted back to the 7 oclock data |
|
|
|
tduggan
Starting Member
26 Posts |
Posted - 2011-09-29 : 16:29:48
|
| Could you reword your question and maybe add some punctuation to make it clear? |
|
|
|
frant101
Starting Member
12 Posts |
Posted - 2011-09-29 : 17:02:25
|
| As the restore happened at 9.20 does the 10 oclock log backup that will happen after the restore hold all the details of the restore and changes to the database ?How does the restore not break the log chain as the datbase is being restored back to a point before the 8 and 9 log backups ? |
|
|
|
tduggan
Starting Member
26 Posts |
Posted - 2011-09-29 : 17:40:12
|
| Thinking about this more, I believe it does break the chain, so you'll need to run a full backup to start the chain. Do a quick test: restore a database, then attempt a tlog backup. I believe you'll get an error. Let us know. |
|
|
|
frant101
Starting Member
12 Posts |
Posted - 2011-09-29 : 17:53:19
|
| found this which was exactly what i was looking , think it covers what i was askinghttp://msdn.microsoft.com/en-us/library/ms175078(v=SQL.90).aspx |
|
|
|
|
Backup Required after Full Restore