Post reply

Restore From a Complete Transaction Log only

  • May 19, 2003 at 1:05 pm

    #105850

    I have a complete transaction log file from the start of the existance of the database ( a year or so). I mistakenly deleted about 2K rows in 1 table. The database has never been backed up.

    I would like to get the rows back of course. I was thinking along the path of making a backup of the the databse and log, then restoring to a STOPAT point (just before mistake). When I backup the I only get a small log backup, not the complete 76 MB log, using the NO_TRUNCATE option or not...

    I've tried "attaching" the big log to a new database, putting the new db in "suspect" mode, then doing a backup, but I still just get a tiny log backup?????

    How can I get a backup operation to backup the entire log, so I can do a RESTORE ... STOPAT afterwards ?

    or

    How can I re-apply my complete transaction log to an empty db, just up to a STOPAT point?

    Thanks



    Once you understand the BITs, all the pieces come together

  • May 22, 2003 at 8:00 am

    This was removed by the editor as SPAM

  • May 22, 2003 at 1:03 pm

    #458245

    Transaction log backups can only be restored to the FULL BACKUP they belong to. So, if you've never done a full backup the transaction logs can't be restored. The procedure is:

    1. restore the latest Full backup

    2. restore the latest differential backup made since the latest full backup

    3. restore any transaction log backups made since the latest differential (or full backup if differential backups have never been done.)

    I'm not sure there's anyway to recover a database when there hasn't been a full backup done. That's why you are supposed to do a full backup after installing SQL Server. I hope that I'm wrong and someone does know a way to recover from only a TL backup, but I think you are out of luck.

    -SQLBill

Viewing 3 posts - 1 through 2 (of 2 total)

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