stupid stupid stupid restore problem

  • 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:

  • 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.

  • 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?

  • 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.

  • 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?

  • 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...

  • 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.

  • 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;)

  • Anyhow, I'm sure it'll help someone someday...

  • How do I know if I have any transaction logs?

    Thanks for all the input!

  • 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.

  • 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

  • Ok, all I see is FULL in the backup_type. How do I turn on transactional logs?

    Thanks!

  • 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?

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 16 through 30 (of 43 total)

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