Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Recovery senarios Expand / Collapse
Author
Message
Posted Friday, October 18, 2013 7:56 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 18, 2013 10:45 PM
Points: 10, 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
Post #1506175
Posted Friday, October 18, 2013 8:27 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:04 AM
Points: 40,172, Visits: 36,559
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 2008, MVP
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

Post #1506190
Posted Friday, October 18, 2013 8:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 18, 2013 10:45 PM
Points: 10, 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

Post #1506196
Posted Friday, October 18, 2013 9:42 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:11 AM
Points: 31,168, Visits: 15,611
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
Post #1506235
Posted Friday, October 18, 2013 12:13 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 7:22 AM
Points: 77, Visits: 150
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
Post #1506283
Posted Friday, October 18, 2013 12:55 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:04 AM
Points: 40,172, Visits: 36,559
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 2008, MVP
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

Post #1506307
Posted Friday, October 18, 2013 1:10 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 7:22 AM
Points: 77, Visits: 150
Thanks GilaMonster, my post is of course applicable only during extreme emergencies.

Jonathan Bernardez Bernardez
___________________________________________________________
DBD. MCSA SQL Server 2012
Post #1506313
Posted Friday, October 18, 2013 1:19 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:04 AM
Points: 40,172, Visits: 36,559
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 2008, MVP
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

Post #1506317
Posted Friday, October 18, 2013 1:26 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 7:22 AM
Points: 77, Visits: 150
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
Post #1506321
Posted Friday, October 18, 2013 1:38 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 10:04 AM
Points: 40,172, Visits: 36,559
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 2008, MVP
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

Post #1506324
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse