SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Recovery senarios


Recovery senarios

Author
Message
amitsdba
amitsdba
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 33
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86685 Visits: 45254
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

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
amitsdba
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 33
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
Steve Jones
SSC Guru
SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)SSC Guru (61K reputation)

Group: Administrators
Points: 61943 Visits: 19100
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
jonysuise
jonysuise
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 166
Hi amitsdba, and welcome to the forum. :-D

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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86685 Visits: 45254
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
jonysuise
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 166
Thanks GilaMonster, my post is of course applicable only during extreme emergencies. :-D

Jonathan Bernardez Bernardez
___________________________________________________________
DBD. MCSA SQL Server 2012
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86685 Visits: 45254
jonysuise (10/18/2013)
Thanks GilaMonster, my post is of course applicable only during extreme emergencies. :-D


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
jonysuise
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 166
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)SSC Guru (86K reputation)

Group: General Forum Members
Points: 86685 Visits: 45254
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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search