SQLServerCentral.com / SQL Server 2008 - General / SQL Server 2008 / Recovery senarios / Latest PostsInstantForum.NET v2.9.0SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 02 Aug 2015 13:21:01 GMT20RE: Recovery senarioshttp://www.sqlservercentral.com/Forums/Topic1506175-391-1.aspx[quote][b]amitsdba (10/18/2013)[/b][hr]Good evening all,I am new to sql sever admin .I want to clarify few recovery scenarios.1)if i take weekly full backup on sunday at 10 PM.and daily i take diff backup at 10 PM and hourly i run log backup withouttruncating the logs.a)suppose i want to recover database till monday 10.30 AM.Please let me know the process to achieve the same.b)suppose i want to recover database till monday 11.30 PM.Please let me know the process to achieve the same.Regards[/quote]This isn't a question to ask in a forum. You need to understand the whys and wherefores and then practice them. NEVER rely on a fixed list of options from people that don't understand your environment.Friendly advice.....nothing moreSat, 19 Oct 2013 11:26:05 GMTkevaburgRE: Recovery senarioshttp://www.sqlservercentral.com/Forums/Topic1506175-391-1.aspxGood Morning All the Master's (Gila, Steve, jony)Thanks for your valuable suggestions.RegardsFri, 18 Oct 2013 22:20:25 GMTamitsdbaRE: Recovery senarioshttp://www.sqlservercentral.com/Forums/Topic1506175-391-1.aspxWhatever works for you. I prefer to diagnose the cause of a problem before deciding on a solution, but maybe that's just me.Fri, 18 Oct 2013 13:38:22 GMTGilaMonsterRE: Recovery senarioshttp://www.sqlservercentral.com/Forums/Topic1506175-391-1.aspxThere are times in production environments when your database is unresponsive because of the size of your transaction log, disk is full, backup of your log doesn't work (true story) and everybody is waiting for you to bring service back. THIS is the kind of scenario when i would use what i posted before. I agree with you it's indeed a terrible solution. :-)Fri, 18 Oct 2013 13:26:21 GMTjonysuiseRE: Recovery senarioshttp://www.sqlservercentral.com/Forums/Topic1506175-391-1.aspx[quote][b]jonysuise (10/18/2013)[/b][hr]Thanks GilaMonster, my post is of course applicable only during extreme emergencies. :-D[/quote]Except it's worse advice for extreme emergencies. You're advocating taking actions that can have consequences without any form of investigation whatsoever as to what the root cause of the full log actually is. Lack of log backups is one out of a number of possible causes of a full log and hence your advice will only achieve anything in that specific case. In any other it just wastes time and breaks the log chain.Fri, 18 Oct 2013 13:19:21 GMTGilaMonsterRE: Recovery senarioshttp://www.sqlservercentral.com/Forums/Topic1506175-391-1.aspxThanks GilaMonster, my post is of course applicable only during extreme emergencies. :-DFri, 18 Oct 2013 13:10:51 GMTjonysuiseRE: Recovery senarioshttp://www.sqlservercentral.com/Forums/Topic1506175-391-1.aspx[quote][b]jonysuise (10/18/2013)[/b][hr]If you run out of space and sql server becomes unresponsive, switch the database’s recovery model to simple . This empties out the transaction log, and then run DBCC SHRINKFILE afterwards to free the remaining space, then switch the recovery model back to full.[/quote]Yuck. Unnecessary, broken log chain and possibly not going to help.If the log fills up, check this article: [url]http://www.sqlservercentral.com/articles/Transaction+Logs/72488/[/url]Fri, 18 Oct 2013 12:55:09 GMTGilaMonsterRE: Recovery senarioshttp://www.sqlservercentral.com/Forums/Topic1506175-391-1.aspxHi amitsdba, and welcome to the forum. :-DIf you run out of space and sql server becomes unresponsive, switch the database’s recovery model to simple . This empties out the transaction log, and then run DBCC SHRINKFILE afterwards to free the remaining space, then switch the recovery model back to full.Your backup strategy is very important. Make sure you do all the backups you need to attain your recovery time and point objective. If you use the full recovery model, backing up your transaction log is mandatory.Hope it helps.Fri, 18 Oct 2013 12:13:52 GMTjonysuiseRE: Recovery senarioshttp://www.sqlservercentral.com/Forums/Topic1506175-391-1.aspxFirst, I'd always restore with norecovery. If you make a mistake, you haven't caused an issue. Do all restores with that or STANDBY and once you are sure things are correct and all restores are complete, you can [code="sql"]restore database xx with recovery[/code]If your log fills up, you need to perform log backups that allow the logs to clear. If you run out of disk space, you need more disk space.Fri, 18 Oct 2013 09:42:36 GMTSteve Jones - SSC EditorRE: Recovery senarioshttp://www.sqlservercentral.com/Forums/Topic1506175-391-1.aspxGood Evening Gail ,Thanks a lot for the update.googling i found below command .i hope this is the right RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK' WITH NORECOVERYGO RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN' WITH RECOVERY, STOPAT = 'Mar 23, 2009 05:31:00 PM' GO.One more thing Gail ,if my log destination gets full,what is the best option to reduce it.RegardsFri, 18 Oct 2013 08:36:43 GMTamitsdbaRE: Recovery senarioshttp://www.sqlservercentral.com/Forums/Topic1506175-391-1.aspxI certainly hope you don't run the log backups without truncating the log, if you really do, you'll be running out of disk space very shortly. Log backups are supposed to truncate the log.1) Latest full backup from before the time you want to restore to, latest diff backup after that full and from before the time you want to restore to, all log backups from after that diff up to the time you want to restore to, run the log backups with STOPAT1) Latest full backup from before the time you want to restore to, latest diff backup after that full and from before the time you want to restore to, all log backups from after that diff up to the time you want to restore to, run the log backups with STOPATFri, 18 Oct 2013 08:27:36 GMTGilaMonsterRecovery senarioshttp://www.sqlservercentral.com/Forums/Topic1506175-391-1.aspxGood evening all,I am new to sql sever admin .I want to clarify few recovery scenarios.1)if i take weekly full backup on sunday at 10 PM.and daily i take diff backup at 10 PM and hourly i run log backup withouttruncating the logs.a)suppose i want to recover database till monday 10.30 AM.Please let me know the process to achieve the same.b)suppose i want to recover database till monday 11.30 PM.Please let me know the process to achieve the same.RegardsFri, 18 Oct 2013 07:56:21 GMTamitsdba