December 6, 2017 at 9:57 am
Hello experts,
I have the following scenario and am wondering if someone can confirm the steps for me.
I know how to script out a point-in-time restore where the point in time falls within the range of the existing log backups. However, I'm not sure about the following case:
Full backup: 6:00 pm
Log backup 1: 6:30 pm
Log backup 2: 7:00 pm
Log backup 3: 7:30 pm
Log backup 4: 8:00 pm
Then a client asks for a restore of the db to a dev environment as of 8:10 pm. But the 8:30 pm log backup hasn't happened yet. I tried searching for tail log backups but am not sure that is what is needed here.
Does anyone have any advice on how to do such a restore, I mean aside from waiting until the 8:30 pm log backup lol?
Thanks for any help!
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
December 6, 2017 at 10:14 am
Depending on how quickly the client wants the backup depends on your course of action. If they can wait until the 8:30 log backup to be taken, you could start the restore keeping the database in a restoring state (last log restore run with NORECOVERY), and when the 8:30 log backup is completed use it to restore the database to 8:10 using the STOPAT clause and RECOVERY clause.
If they can't wait, you just need to run an ad hoc log backup and ensure that backup is kept with all the other log backups.
Since you are restoring to a different environment and not to production due to a hardware failure, you really aren't looking at a tail log backup.
December 6, 2017 at 10:18 am
Lynn Pettis - Wednesday, December 6, 2017 10:14 AMDepending on how quickly the client wants the backup depends on your course of action. If they can wait until the 8:30 log backup to be taken, you could start the restore keeping the database in a restoring state (last log restore run with NORECOVERY), and when the 8:30 log backup is completed use it to restore the database to 8:10 using the STOPAT clause and RECOVERY clause.If they can't wait, you just need to run an ad hoc log backup and ensure that backup is kept with all the other log backups.
+1 Lynn nailed it first. I would run ad hoc back up and go from there, doing things on a timely fashion scores points with customers!
...
December 6, 2017 at 10:26 am
Thanks Lynn, HappyGeek!
Just to confirm - by ad hoc backup do you mean a log backup (which I would issue via T-SQL)? That would be OK and wouldn't disrupt the scheduled log backups such as the one due to happen at 8:30 pm?
Thanks again.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
December 6, 2017 at 10:31 am
Yes, you can run it from SSMS. Just be sure that the log backup you create is stored with all the scheduled log backups as it becomes part of the log chain.
December 6, 2017 at 10:53 am
It would simply become another TLog backup in the backup process, (chain) and restorable through normal process. You may want to read about backup chains and I would suggest reading about a read only backup to reinforce the concept.
...
December 6, 2017 at 11:03 am
HappyGeek - Wednesday, December 6, 2017 10:53 AMIt would simply become another TLog backup in the backup process, (chain) and restorable through normal process. You may want to read about backup chains and I would suggest reading about a read only backup to reinforce the concept.
Apologies,fighting with new phone and predictive text that should be copy only backup.
...
December 6, 2017 at 11:26 am
HappyGeek - Wednesday, December 6, 2017 11:03 AMHappyGeek - Wednesday, December 6, 2017 10:53 AMIt would simply become another TLog backup in the backup process, (chain) and restorable through normal process. You may want to read about backup chains and I would suggest reading about a read only backup to reinforce the concept.Apologies,fighting with new phone and predictive text that should be copy only backup.
No worries, thanks for your reply! Thanks for confirming that the copy only log backup would become another backup in the chain.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
December 6, 2017 at 11:50 am
webrunner - Wednesday, December 6, 2017 11:26 AMHappyGeek - Wednesday, December 6, 2017 11:03 AMHappyGeek - Wednesday, December 6, 2017 10:53 AMIt would simply become another TLog backup in the backup process, (chain) and restorable through normal process. You may want to read about backup chains and I would suggest reading about a read only backup to reinforce the concept.Apologies,fighting with new phone and predictive text that should be copy only backup.
No worries, thanks for your reply! Thanks for confirming that the copy only log backup would become another backup in the chain.
- webrunner
No, a copy-only log backup would NOT be part of the log chain. I am not even sure if you can do a copy_only log backup (never tried) or if it would work for what you are doing. I would just run a log backup, keep it with all the other log backups.
December 6, 2017 at 11:54 am
Lynn Pettis - Wednesday, December 6, 2017 11:50 AMwebrunner - Wednesday, December 6, 2017 11:26 AMHappyGeek - Wednesday, December 6, 2017 11:03 AMHappyGeek - Wednesday, December 6, 2017 10:53 AMIt would simply become another TLog backup in the backup process, (chain) and restorable through normal process. You may want to read about backup chains and I would suggest reading about a read only backup to reinforce the concept.Apologies,fighting with new phone and predictive text that should be copy only backup.
No worries, thanks for your reply! Thanks for confirming that the copy only log backup would become another backup in the chain.
- webrunnerNo, a copy-only log backup would NOT be part of the log chain. I am not even sure if you can do a copy_only log backup (never tried) or if it would work for what you are doing. I would just run a log backup, keep it with all the other log backups.
Ah OK, thanks Lynn, so the log backup chain would look something like this, with the ad hoc backup just like the others, except with a time in between the others?
Full backup: 6:00 pm
Log backup 1: 6:30 pm
Log backup 2: 7:00 pm
Log backup 3: 7:30 pm
Log backup 4: 8:00 pm (Client asks for restore as of 8:10 pm)
Log backup 5: 8:17 pm (ad hoc)
Log backup 6: 8:30 pm ...
etc
Thanks again.
- webrunner
P.S. Seems like copy-only log backups are possible, but as you said, probably unnecessary in most cases.
Ref.:
Copy-only log backups (full recovery model and bulk-logged recovery model only)
A copy-only log backup preserves the existing log archive point and, therefore, does not affect the sequencing of regular log backups. Copy-only log backups are typically unnecessary. Instead, you can create a new routine log backup (using WITH NORECOVERY) and use that backup together with any previous log backups that are required for the restore sequence. However, a copy-only log backup can sometimes be useful for performing an online restore.
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
December 6, 2017 at 12:17 pm
webrunner - Wednesday, December 6, 2017 11:26 AMHappyGeek - Wednesday, December 6, 2017 11:03 AMHappyGeek - Wednesday, December 6, 2017 10:53 AMIt would simply become another TLog backup in the backup process, (chain) and restorable through normal process. You may want to read about backup chains and I would suggest reading about a read only backup to reinforce the concept.Apologies,fighting with new phone and predictive text that should be copy only backup.
No worries, thanks for your reply! Thanks for confirming that the copy only log backup would become another backup in the chain.
- webrunner
I did not actually say it would be become another backup in the chain, I wanted to illustrate it as a means of creating a backup without breaking the chain, apologies again if this came across wrong. I actually said the TLog would become part of the chain. I offered the other as a means of learning, by reading yourself, to understand the difference.
...
December 6, 2017 at 12:21 pm
HappyGeek - Wednesday, December 6, 2017 12:17 PMwebrunner - Wednesday, December 6, 2017 11:26 AMHappyGeek - Wednesday, December 6, 2017 11:03 AMHappyGeek - Wednesday, December 6, 2017 10:53 AMIt would simply become another TLog backup in the backup process, (chain) and restorable through normal process. You may want to read about backup chains and I would suggest reading about a read only backup to reinforce the concept.Apologies,fighting with new phone and predictive text that should be copy only backup.
No worries, thanks for your reply! Thanks for confirming that the copy only log backup would become another backup in the chain.
- webrunnerI did not actually say it would be become another backup in the chain, I wanted to illustrate it as a means of creating a backup without breaking the chain, apologies again if this came across wrong. I actually said the TLog would become part of the chain. I offered the other as a means of learning, by reading yourself, to understand the difference.
OK, sorry for my misreading. Thanks again, and yes I will read up on backup chains.
Thanks again -
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
December 6, 2017 at 12:30 pm
webrunner - Wednesday, December 6, 2017 11:26 AMHappyGeek - Wednesday, December 6, 2017 11:03 AMHappyGeek - Wednesday, December 6, 2017 10:53 AMIt would simply become another TLog backup in the backup process, (chain) and restorable through normal process. You may want to read about backup chains and I would suggest reading about a read only backup to reinforce the concept.Apologies,fighting with new phone and predictive text that should be copy only backup.
No worries, thanks for your reply! Thanks for confirming that the copy only log backup would become another backup in the chain.
- webrunner
I did NOT say that copy only backup would become part of the backup chain, (apologies again if tis got mixed up) I was saying an additional log backup would become a part of the chain but additionally it is possible to do copy only backups that would not affect the backup chain and it would be worthwhile reading about those too.
...
December 18, 2017 at 8:06 am
HappyGeek - Wednesday, December 6, 2017 12:30 PMwebrunner - Wednesday, December 6, 2017 11:26 AMHappyGeek - Wednesday, December 6, 2017 11:03 AMHappyGeek - Wednesday, December 6, 2017 10:53 AMIt would simply become another TLog backup in the backup process, (chain) and restorable through normal process. You may want to read about backup chains and I would suggest reading about a read only backup to reinforce the concept.Apologies,fighting with new phone and predictive text that should be copy only backup.
No worries, thanks for your reply! Thanks for confirming that the copy only log backup would become another backup in the chain.
- webrunnerI did NOT say that copy only backup would become part of the backup chain, (apologies again if tis got mixed up) I was saying an additional log backup would become a part of the chain but additionally it is possible to do copy only backups that would not affect the backup chain and it would be worthwhile reading about those too.
Thanks again! No worries - the misreading was all on my part. Much appreciate your help.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
December 18, 2017 at 8:41 am
Lynn Pettis - Wednesday, December 6, 2017 10:14 AMDepending on how quickly the client wants the backup depends on your course of action. If they can wait until the 8:30 log backup to be taken, you could start the restore keeping the database in a restoring state (last log restore run with NORECOVERY), and when the 8:30 log backup is completed use it to restore the database to 8:10 using the STOPAT clause and RECOVERY clause.If they can't wait, you just need to run an ad hoc log backup and ensure that backup is kept with all the other log backups.
Since you are restoring to a different environment and not to production due to a hardware failure, you really aren't looking at a tail log backup.
Better still, put the database into standby mode which will leave it readable and still allow further logs to be restored
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply