Recovery senarios

Question

Recovery senarios

amitsdba

SSC Veteran

Points: 282
More actions
October 18, 2013 at 7:56 am

#284729

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 without
truncating 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

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

I 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 STOPAT

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

amitsdba SSC Veteran Points: 282 More actions · Answer 2

Good 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 NORECOVERY

GO

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.

Regards

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 3

First, 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

restore database xx with recovery

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.

jonysuise Ten Centuries Points: 1206 More actions · Answer 4

Hi amitsdba, and welcome to the forum. 😀

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.

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.

Jonathan Bernardez Bernardez
___________________________________________________________
DBD. MCSA SQL Server 2012

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

jonysuise (10/18/2013)
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.

Yuck. Unnecessary, broken log chain and possibly not going to help.

If the log fills up, check this article: http://www.sqlservercentral.com/articles/Transaction+Logs/72488/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jonysuise Ten Centuries Points: 1206 More actions · Answer 6

Thanks GilaMonster, my post is of course applicable only during extreme emergencies. 😀

Jonathan Bernardez Bernardez
___________________________________________________________
DBD. MCSA SQL Server 2012

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

jonysuise (10/18/2013)
Thanks GilaMonster, my post is of course applicable only during extreme emergencies. 😀

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.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jonysuise Ten Centuries Points: 1206 More actions · Answer 8

There 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. 🙂

Jonathan Bernardez Bernardez
___________________________________________________________
DBD. MCSA SQL Server 2012

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Whatever works for you. I prefer to diagnose the cause of a problem before deciding on a solution, but maybe that's just me.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

amitsdba SSC Veteran Points: 282 More actions · Answer 10

Good Morning All the Master's (Gila, Steve, jony)

Thanks for your valuable suggestions.

Regards

kevaburg SSCoach Points: 18131 More actions · Answer 11

amitsdba (10/18/2013)
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 without
truncating 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

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 more