Unable to recover the data to the point in time??

Question

Post reply

Unable to recover the data to the point in time??

madhu-686862

SSChampion

Points: 10707
More actions
November 5, 2008 at 5:23 pm

#195461

Hi,
I have
full backup at 3:00pm,log backup at 4:00pm.
I inserted some data into test table from 4:00pm to 4:10pm
I deleted the test table at 4:10pm.
I took the tail log backup immediately after deleting the table.
and restored the full backup of 3:00pm, logbackup of 4:00pm and tail logbackup.
But I did not get the test table back.There is no test table at all.
Where Iam doing the wrong things?
plz advice me...

Viewing 15 posts - 1 through 15 (of 23 total)

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

intel_astacrs Newbie Points: 8 More actions · Answer 1

I think still we can recover the table If you have the log backup.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Provide us with the t-sql scripts you used to restore the database. Based solely on your description, we really can't tell you what you did wrong, if anything.

madhu-686862 SSChampion Points: 10707 More actions · Answer 3

Thank you..

Here is the script

Backup tail log:

BACKUP LOG [abc]

TO DISK = N'F:\BACKUPS\abc_Log\abc_Log.trn'

WITH NO_TRUNCATE , NOFORMAT, NOINIT, NAME = N'abc-Transaction Log Backup',

SKIP, NOREWIND, NOUNLOAD, NORECOVERY , STATS = 10

GO

Restore full backup:

RESTORE DATABASE [abc]

FROM DISK = N'F:\BACKUPS\abc\abc_11-05-2008.bak'

WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

GO

Restore logbackup(4:00pm) and tail log backup:

RESTORE LOG [abc]

FROM DISK = N'F:\BACKUPS\abc_Log\abc_Log20081105160000.trn'

WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10

GO

RESTORE LOG [abc]

FROM DISK = N'F:\BACKUPS\abc_Log\abc_Log.trn'

WITH FILE = 1, RECOVERY, NOUNLOAD, STATS = 10

GO

homebrew01 SSC Guru Points: 55544 More actions · Answer 4

I think you need to restore only up to just before you deleted the table. If you restore the entire log, then you'll re-delete the table that you just restored. I don't see STOPAT in your code.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Yep, you are missing the STOPAT = date-time in the second restore log. You need to specify the time just before dropping the table to recover the table from the backup.

madhu-686862 SSChampion Points: 10707 More actions · Answer 6

Thank you..

I got it by using stop at = datetime.

But when whole database is deleted, we can not take the tail backup right?

So in that case we can not recover the data to point in time..plz correct if Iam misunderstand the concept of tail log backup?

homebrew01 SSC Guru Points: 55544 More actions · Answer 7

Correct. If you delete the entire database, then there is no database of which to take a backup, so you can only recover up to the last t-log before the DB was deleted.

madhu-686862 SSChampion Points: 10707 More actions · Answer 8

Ok..

What happens in below scenarios:

1.If mdf file exist and ldf file deleted can we do the point in time recovery?

2.If ldf file exist and mdf file deleted can we do the point in time recovery?

homebrew01 SSC Guru Points: 55544 More actions · Answer 9

madhu.arda (11/6/2008)
Ok..
What happens in below scenarios:
1.If mdf file exist and ldf file deleted can we do the point in time recovery?
2.If ldf file exist and mdf file deleted can we do the point in time recovery?

By "Point-in-time" do you mean right up to the last second ?? Point-in-time can also mean 8:47 pm 2 weeks ago.

For #2, if your MDF is deleted, that is the same as your database being deleted. You can recover back to your last backups (FULL + T-Logs)

For #1, I think it's the same answer as #2.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

If the transaction log file still exists, you may still be able to run a tail log backup and recover your database up to the point the database file was deleted or became corrupt.

I haven't tested this, but I thought I had read it was possible some where.

madhu-686862 SSChampion Points: 10707 More actions · Answer 11

Hi,

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

Now how can I recover the data inserted into table 'A' upto 3:10pm.

plz advice me

Thanks in advance

Steve Jones - SSC Editor SSC Guru Points: 734498 More actions · Answer 12

You would need to run a tail log backup. The problem is I'm not sure how you will run one if you deleted the MDF.

You can recover to 3:00 using the full and logs, but anything after 3 might be gone.

How was the mdf deleted? Have you tried recovering the deleted file? and how did this happen? Stopping SQL and deleting the MDF must have been deliberate (malicious) or negligence on someone's part.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 13

Or it is being done in a test environment to identify/test recovery processes.

madhu-686862 SSChampion Points: 10707 More actions · Answer 14

Thank You,

Yes, it is being done in a test environment to identify/test recovery processes. As Lynn Pettis told 'If the transaction log file still exists, you may still be able to run a tail log backup and recover your database up to the point'.

Iam tyring the above scenario. But after deleting the mdf file Iam not able to take any tail logbackup? In management studio->database->all tasks->backup-> everything is greayed out.

So how and upto what time we will recover the data back. is it possible to recover the data upto 3:10pm?

plz advice me.