Restoring a dropped table

  • Hi,

    I am particularly new to SQL Server. I have mostly worked with Oracle Databases in the past. I have a question regarding database backup and restore. Here's the scenario.

    -- I took a full backup of my database (The Recovery Model is FULL)

    -- I created a table and inserted some rows in it.

    -- I dropped that table.

    Now my question is, can I recover that dropped table by restoring the backup I took before creating table using transaction logs? In Oracle the strategy was to restore the file from backup and apply archive logs on it. Can the same be done in SQL Server?

    Regards,

    Hammad

  • in this case SQL server works just like Oracle, restore Full backup and then TRN log until the point where you dropped the table ( using stopat=timestamp)

    Regards,
    MShenel

  • Thanks for the reply.

    Please guide on how to apply transaction log on Full Backup Restore. My main concern is that if, for example, a full backup was performed last night. Today at 2:00 PM the disk crashed containing the MDF files. Transaction Log Files are available. Database is in Full Recovery Mode. Can I restore the last night's backup and then apply transaction log on this to restore data until last night+data entered since morning until 2:00 PM? If yes, please provide the syntax on how to do this. Thanks.

  • restore database Db_name from disk ='path' with norecovery

    go

    restore log db_name from disk='path\log1' with norecovery;

    go

    restore log db_name from disk='path\lastlog' with recovery ,stopat='timestamp';

    go

    Regards,
    MShenel

  • Thanks.

    When I tried 'restore database CMS_TEST from disk ='D:\Backups\try' with norecovery' it gives the following message.

    Msg 3159, Level 16, State 1, Line 1

    The tail of the log for the database "CMS_TEST" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

    Please advice on this.

    Regards

  • Before you attempt any sort of restore, you should back up the transaction log. This gives you the option to restore to any point in time. It may not be possible if you have suffered a disk failure, but you should try.

    John

  • Hi John

    When I took the Database backup I also took the backup of Transaction Log. In case the MDF files are missing, how can we restore using a full backup and apply existing transactional log on it?

  • You also need to back up the log just before you attempt the restore. If you can't do that, for example if any database files are missing, you can only restore to the point where you last backed up the log. If you only do that at the same time as your full backup, then having the database in Full recovery mode is pointless.

    John

  • hammad772001 (6/15/2011)


    Hi John

    When I took the Database backup I also took the backup of Transaction Log. In case the MDF files are missing, how can we restore using a full backup and apply existing transactional log on it?

    The MDF files are irrelevant. If you have a Full Backup and existing transaction log backups, you can restore the database to any location with SQL Server installed, regardless of whether the MDF file exists or not.

    However, you cannot mix-n-match MDF and LDF files. SQL Server will not let you take two different copies of the same database and swap transaction logs. It's a bad practice to do this even if you could.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • msenel (6/15/2011)


    in this case SQL server works just like Oracle, restore Full backup and then TRN log until the point where you dropped the table ( using stopat=timestamp)

    Just to point out for future readers of this topic, the database has to be in FULL recovery mode with both a Full database backup and transaction logs being taken to use Point In Time restores. Bulk-Logged or Simple recovery don't cut it.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Brandie Tarvin (6/15/2011)


    msenel (6/15/2011)


    in this case SQL server works just like Oracle, restore Full backup and then TRN log until the point where you dropped the table ( using stopat=timestamp)

    Just to point out for future readers of this topic, the database has to be in FULL recovery mode with both a Full database backup and transaction logs being taken to use Point In Time restores. Bulk-Logged or Simple recovery don't cut it.

    Bulk logged allows point in time restores, providing there are no minimally logged operations within the log backup covering the interval within which you want to restore.

    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
  • GilaMonster (6/15/2011)


    Brandie Tarvin (6/15/2011)


    msenel (6/15/2011)


    in this case SQL server works just like Oracle, restore Full backup and then TRN log until the point where you dropped the table ( using stopat=timestamp)

    Just to point out for future readers of this topic, the database has to be in FULL recovery mode with both a Full database backup and transaction logs being taken to use Point In Time restores. Bulk-Logged or Simple recovery don't cut it.

    Bulk logged allows point in time restores, providing there are no minimally logged operations within the log backup covering the interval within which you want to restore.

    I wish I still had my SQL 2000 BOL. I could have sworn that said PIT restores weren't usable for Bulk Logged. But I checked 2005 BOL and sure enough, it's there. (If vaguely stated: "However, point-in-time recovery and online restore are affected by the bulk-logged recovery model.")

    I really didn't think you could do it.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • Thanks to all of you for your input.

    Based on the replies, I guess the following scenario is correct.

    1. Database Full Backup occurred at 12:00 AM

    2. Transactional log backups are scheduled at regular intervals.

    3. Lets assume, a disk containing the MDF files crashed at 3:00 PM. Last Transactional Log backup was taken at 2:00 PM. So the database can be restored PIT until 2:00 PM, and all of the work done between 2:00 PM and 3:00 PM is lost.

    Please correct me if I'm wrong.

    Thanks,

    Hammad

  • hammad772001 (6/15/2011)


    3. Lets assume, a disk containing the MDF files crashed at 3:00 PM. Last Transactional Log backup was taken at 2:00 PM. So the database can be restored PIT until 2:00 PM, and all of the work done between 2:00 PM and 3:00 PM is lost.

    No. The ldf is intact.

    Take a tail-log backup.

    Restore the full

    Restore the logs

    Restore the tail-log backup

    0 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
  • Suppose the MDF Files are missing and LDF are available. Full Database Backup and Transactional Log Backup are also available.

    When I restore the Database using 'RESTORE DATABASE CMS_TEST FROM DISK = 'D:\Backups\try' WITH RECOVERY', it gives the following error.

    Msg 3159, Level 16, State 1, Line 1

    The tail of the log for the database "CMS_TEST" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

    When I try to backup the log tail using 'BACKUP LOG CMS_TEST WITH NORECOVERY', it gives the following error.

    Msg 945, Level 14, State 2, Line 1

    Database 'CMS_TEST' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

    Msg 3013, Level 16, State 1, Line 1

    BACKUP LOG is terminating abnormally.

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

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