Post reply

How Can Using SSMS Database Restore Possible Work in Full Recovery Mode

  • September 23, 2009 at 12:58 pm

    #226478

    Hi Folls

    I was running backup and restore test on Northwind database.

    After making a copy of it and doing a backup on the copy

    I tried to restore it and got a message like this:

    "the tail of the log for the database "Northwind_new" has not been

    backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work

    you do not want to loose. Use the WITH REPLACE or WITH STOPAT clause of

    the RESTORE statement to just overwrite teh contents of the log"

    my question is how can I use SSMS to resore a database which is in FULL recovery mode

    with just the full backup file (no differential and no transactions)

    it looks to me like i have to generate the tsql commands to actually restore the database i as i do not see

    these options in SSMS for the restore

    Thanks

    Jim

  • September 23, 2009 at 2:00 pm

    #1057747

    What method did you use to "copy" the database?

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • September 24, 2009 at 6:58 am

    #1057982

    I would assume when you're selecting your backup, near the bottom in the files to backup, you have a trn backup in there. Put a check into your .bak file, remove the trn, and go to options and make sure "Leave the database ready to use" is checked within the Recovery State options.

    If you're overwriting the database, make sure there's a check into "Overwrite the existing database".

  • September 24, 2009 at 10:38 am

    #1058146

    Hi Melton

    here is my test steps:

    Launch SSMS

    Select Databases

    --create copy of Northwind database via SSMS

    --Recovery Mode is set to FULL

    Select Northwind >Task>Copy Database

    (Northwind_new) created

    --logout of SSMS and back in to see new database

    Launch SSMS

    Select Databases

    --backup new database (Northwind_new)

    Select Northwind_new>Task>Back up

    --Creates file

    T:\Backups\Northwind_new.bak

    --check if file is there

    Select Northwind_new>TaskDatabase

    select the .back file

    T:\Backups\Northwind_new.bak

    get errors:

    the tail of the log for the database "Northwind_new" has not been

    backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work

    you do not want to loose. Use the WITH REPLACE or WITH STOPAT clause of

    the RESTORE statement to just overwrite teh contents of the log

    thanks

    jim

  • September 24, 2009 at 10:42 am

    #1058149

    Hi Tristan

    the only thing i see under the database options>recovery is "Page Verify"; value is CHECKSUM

    what am i missing

    jim

Viewing 5 posts - 1 through 4 (of 4 total)

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