February 14, 2009 at 2:23 pm
Adi Cohn (2/14/2009)
I don’t think that he can use the log after he did the restore operation.Adi
doh! talk about a brain fart. I can't see what I was thinking when I wrote that answer :pinch::Whistling:
February 15, 2009 at 9:49 pm
Adi Cohn (2/14/2009)
I don’t think that he can use the log after he did the restore operation.Adi
But if there are t-logs, he could run the restore again, this time leave it non-operational so that the t-logs can be applied.
But, he could only restore as far as the unbroken chain of t-logs exists.
February 16, 2009 at 2:10 am
So how does that work?
Take a tlog backup.
Run the restore again (maybe as a different db)
Restore to point in time with the log and recover?
February 16, 2009 at 4:26 am
I mean that if he has 5 months of transaction logs (not likely) then he could re-run the restore and then apply the 5 months transaction logs.
February 16, 2009 at 5:38 am
Yes I got that, can you post the precise steps to go about doing that so that he can make sure to not screw it up?
February 16, 2009 at 6:18 am
Ninja's_RGR'us (2/16/2009)
Yes I got that, can you post the precise steps to go about doing that so that he can make sure to not screw it up?
😀 Me too excited to know that...
February 16, 2009 at 6:18 am
We haven't heard that there are any t-logs. If there were, you would do a FULL DB restore WITH NORECOVERY of the database. Then restore each t-log in sequence WITH NORECOVERY, until the last t-log which gets restored WITH RECOVERY.
February 16, 2009 at 6:24 am
homebrew01 (2/16/2009)
We haven't heard that there are any t-logs. If there were, you would do a FULL DB restore WITH NORECOVERY of the database. Then restore each t-log in sequence WITH NORECOVERY, until the last t-log which gets restored WITH RECOVERY.
Magy
Posted 2/13/2009 2:46:42 AM
SSC Veteran
I made a small mistake in my database today and decided to restore from a previous database backup.The only problem is the restore file I used was 5 months old! So I lost 5 months of critical data! I can't find any more recent backups. Is there a way to undo this restore?
OP has told us in this first post;)
February 16, 2009 at 6:33 am
Anyhow, I'm sure it'll help someone someday...
February 16, 2009 at 8:23 am
How do I know if I have any transaction logs?
Thanks for all the input!
February 16, 2009 at 8:26 am
Magy (2/16/2009)
How do I know if I have any transaction logs?Thanks for all the input!
Query the "backupset" system table in msdb databse.
February 16, 2009 at 8:29 am
Query your backup history to see what backups have been run. This will show you all the FULL, DIFFERENTIAL and LOG backups. You can modify the # of days to go back and select just for a specific database name. Then if you did take backups, you have to see if they still exist somewhere or got deleted.
SELECT sysdb.name, bkup.description, bkup.backup_finish_date,
case
when type='D' then '** FULL **'
when type='I' then 'DIFFERENTIAL'
when type='L' then 'LOG'
end as Backup_Type,
(STR(ABS(DATEDIFF(day, GetDate(),(backup_finish_date))))) as 'Days_Ago',
ceiling(bkup.backup_size /1048576) as 'Size Meg' ,
cast((bkup.backup_size /1073741824) as decimal (9,2)) as 'Gig',
server_name, sysdb.crdate
,datediff(minute, bkup.backup_start_date, bkup.backup_finish_date) as 'Mins'
,cast(cast(datediff(minute, bkup.backup_start_date, bkup.backup_finish_date)
as decimal (8,3))/60 as decimal (8,1)) as 'Hours', first_lsn, last_lsn, checkpoint_lsn
FROM master.dbo.sysdatabases sysdb LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name
where backup_finish_date > DATEADD(DAY, -160, (getdate())) -- Last 5 months
--AND sysdb.name = 'MY_DB_NAME'
ORDER BY sysdb.name, bkup.backup_finish_date desc
February 16, 2009 at 8:34 am
Ok, all I see is FULL in the backup_type. How do I turn on transactional logs?
Thanks!
February 16, 2009 at 8:37 am
Magy (2/16/2009)
Ok, all I see is FULL in the backup_type. How do I turn on transactional logs?Thanks!
Which means you don't have Transaction log backups. Then, you can only do Full Restore not a point-in-time. When was the last time the backup was taken?
February 16, 2009 at 8:40 am
Magy (2/16/2009)
Ok, all I see is FULL in the backup_type. How do I turn on transactional logs?
For transaction log backups, see the entry in Books Online "Backup log"
it's not going to help you get those 5 months of data back, it will only help prevent future data loss.
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 15 posts - 16 through 30 (of 43 total)
You must be logged in to reply to this topic. Login to reply