SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Restore point in time from Log FILE		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

denis_the_thief
Constraint Violating Yak Guru

Canada
463 Posts
Posted - 11/11/2012 :  10:48:11  Show Profile  Reply with Quote
Here's a hypothetical situation:
- our Log File is on E Drive
- our Database File is on D Drive
- We have a Backup from last night on G Drive
- During the day our D Drive dies and we can't get the Database File Back
- We have no Transaction Log backups


Can the Database be restored to a point in time using our backup and our Log File?

Hommer
Aged Yak Warrior

693 Posts
Posted - 11/12/2012 :  10:28:52  Show Profile  Reply with Quote
Try to perform a tail-log backup first.
Go to Top of Page

denis_the_thief
Constraint Violating Yak Guru

Canada
463 Posts
Posted - 11/12/2012 :  10:49:21  Show Profile  Reply with Quote
quote:
Originally posted by Hommer

Try to perform a tail-log backup first.


Thanks. In this case we can't since the D Drive which has the Database File is dead.

Anyways, this is only a hypothetical situation.

I was just wondering if the Transaction Log file (rather than Log backup) would be of any use if you lose your Database.
Go to Top of Page

jeffw8713
Aged Yak Warrior

USA
693 Posts
Posted - 11/13/2012 :  14:40:15  Show Profile  Reply with Quote
No - you cannot restore to a point in time without available transaction log backups. The best you can do is restore to the latest backup...

Now, if the database is in full recovery model - and you are able to perform a tail-log backup (database does not need to be available for this) then you could restore to a point in time.
Go to Top of Page

chadmat
The Chadinator

USA
1947 Posts
Posted - 11/13/2012 :  15:07:09  Show Profile  Visit chadmat's Homepage  Reply with Quote
Yes, like Hommer and Jeff said, perform a Tail of the log backup.

-Chad
Go to Top of Page

denis_the_thief
Constraint Violating Yak Guru

Canada
463 Posts
Posted - 11/13/2012 :  15:20:37  Show Profile  Reply with Quote
quote:
Originally posted by jeffw8713

No - you cannot restore to a point in time without available transaction log backups. The best you can do is restore to the latest backup...

Now, if the database is in full recovery model - and you are able to perform a tail-log backup (database does not need to be available for this) then you could restore to a point in time.


I appreciate that. I am very surprised. I did not think I could do anything if the Database is not available. I am how curious how I could execute any command if the Database is not available. Maybe I worded the question poorly. Let us say all the Databases are unavailable (i.e. if all were on D Drive as well) or Master is unavailable. And lets say (I forgot to mention) the Database is in Recovery mode. Any idea or any link would be helpful.

Is it that I could restore my back up and then use my Log File (I have no Log backup) to then somehow do a Tail Backup?
Go to Top of Page

chadmat
The Chadinator

USA
1947 Posts
Posted - 11/13/2012 :  15:35:01  Show Profile  Visit chadmat's Homepage  Reply with Quote
http://sqlskills.com/BLOGS/PAUL/post/Disaster-recovery-101-backing-up-the-tail-of-the-log.aspx

-Chad
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35007 Posts
Posted - 11/13/2012 :  16:02:00  Show Profile  Visit tkizer's Homepage  Reply with Quote
Paul's wife, Kimberly, illustrated the database being available when the drive it's on isn't available. This was at PASS 2011. It's because it's running in memory. But try a checkpoint, and it'll finally fail.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

denis_the_thief
Constraint Violating Yak Guru

Canada
463 Posts
Posted - 11/17/2012 :  10:14:36  Show Profile  Reply with Quote
quote:
Originally posted by tkizer

Paul's wife, Kimberly, illustrated the database being available when the drive it's on isn't available. This was at PASS 2011. It's because it's running in memory.


How could the entire Database be in memory? I realize SQL Server keeps what it feels like in the cache. But everything running in memory?
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35007 Posts
Posted - 11/18/2012 :  21:26:59  Show Profile  Visit tkizer's Homepage  Reply with Quote
Well it depends on how much memory you have and what's been read into cache.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000