Results 1 to 5 of 5

Thread: restore table

  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: restore table

    Hi all I am testing backups right now and I'm getting an error message when I try to restore a table (I puposely deleted this table) its not a production database just a test.

    TITLE: Microsoft SQL Server Management Studio
    ------------------------------

    Restore failed for Server 'GCSQL'. (Microsoft.SqlServer.Smo)

    For help, click: Source: ID: ( ) - Events And Errors Message Center: Message Details

    ------------------------------
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ------------------------------

    The tail of the log for the database "SlotMachineDBSQL" 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.
    RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3159)

    For help, click: Source: MSSQLServer ID: 3159 (SQL Server 09.00.3054) - Events And Errors Message Center: Message Details

    ------------------------------
    BUTTONS:

    OK
    ------------------------------

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Just a table? I did not think you were allowed to restore just a table since the 6.5 days. Messes with referential integrity too much.

  3. #3
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Well heres what I did, I droped a table and tried to restore the database. But got an error message. I'm just testing backups. the recovery is set to full recovery so theres a full, diff and trans logs backups

  4. #4
    Join Date
    Jun 2007
    Location
    Ohio, USA
    Posts
    142
    If the DB is in full or bulk-logged mode, a normal restore will fail, since the tail of the log was not backed up. If you run the restore WITH REPLACE, it will succeed, but you can lose committed transactions.

    Using the REPLACE Option
    David Maxwell
    Data Integrity? Yeah, I've heard of that...

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Actually, I do not think it has anything to do with the tail of the log being backed up, or not. By default, SQL Server will restore the files to their original locations. If these files already exist (i.e. you intend to REPLACE them), then you need to specify the REPLACE option, so SQL Server does not stop, and question whether you are really sure or not. Backing up the log before you restore is a good idea, especially if you need to get back to a point in time that is covered by the current log. in fact, it is your only option in that case ;-).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •