November 6, 2008 at 5:29 pm
In the senerio you have created, it may not be possible. It might, however, if the mdf file became corrupt during normal processing to perform a tail log backup and recover the database up to the point of failure. Some problems, you may not be able to recover from up the moment of failure.
November 6, 2008 at 5:42 pm
By "Point-in-time" I mean right up to the last second.
November 6, 2008 at 6:06 pm
Lynn Pettis (11/6/2008)
In the senerio you have created, it may not be possible. It might, however, if the mdf file became corrupt during normal processing to perform a tail log backup and recover the database up to the point of failure. Some problems, you may not be able to recover from up the moment of failure.
Assuming you deleted the file from the OS, and didn't detach it or delete it from SQL Server, you can get a log backup even if the MDF file is missing, assuming you throw on a few WITH options. I just tried it and the following seems to work (after deleting an MDF file while SQL was stopped from the OS):
backup log deleteme to DISK='c:\temp\taillog.bak'
with NORECOVERY,
CONTINUE_AFTER_ERROR,
MEDIANAME ='DeletemeTailLogBK'
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 7, 2008 at 2:49 am
Hi,
let's say you
rename the old log file,
then restore the db, diff and log backups
stop sql server
rename the new log file
rename the old log file to its original name
start sql server
- should not sql server roll foreward all transactions in the original log file?
karl
Best regards
karl
November 9, 2008 at 6:49 pm
thanks Matt,
its worked for me. I recovered the data inserted into table 'A' upto 3:10pm
by doing:
I have full backup 2:00am,diff every 4hrs and log backup every 15mins.
1.I inserted data into a table 'A' from 3:00pm to 3:10pm in database 'abc'
2.Stop the sql server.
3.delete mdf file of abc
4.start the server
5.backup log deleteme to DISK='c:\temp\taillog.bak'
with NORECOVERY,
CONTINUE_AFTER_ERROR,
MEDIANAME ='DeletemeTailLogBK'
6.Restore full backup(2am),diff(2pm) n log backups upto 3:00pm
and finally restore tail backup taken at 3:11pm
Result:recovered the data inserted into table 'A' upto 3:10pm
But what the MEDIANAME ='DeletemeTailLogBK' command doing here?
November 9, 2008 at 10:53 pm
Read BOL (Books On-Line), it will explain the different options available on the BACKUP command. If, after reading BOL, you still have questions, just ask.
November 10, 2008 at 10:59 am
I agree with the point.
And can send me the last script u used to restore.
with stop at time...
November 10, 2008 at 11:22 am
Karl Klingler (11/7/2008)
Hi,let's say you
rename the old log file,
then restore the db, diff and log backups
stop sql server
rename the new log file
rename the old log file to its original name
start sql server
- should not sql server roll foreward all transactions in the original log file?
karl
No. Rather SQL will tell you that the log file doesn't match the mdf file and it will mark your database suspect. You can't mix and match database files.
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
November 10, 2008 at 11:27 am
rsmaloo (11/10/2008)
I agree with the point.And can send me the last script u used to restore.
with stop at time...
Lookup Restore Database in Books Online. It will give you all the various options and examples of them, including Stop At.
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
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply