Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    50

    Unanswered: Restore transaction log?????????

    Hi all,
    I had been made full backup, differential backup, transaction log backup. I want to create new database from these file backup.
    I did it follow:
    1. Create new database from full backup file, it is ok. then
    2. restore this database use transaction log backup file, an message raise :
    "The proceding restore operation did not specify WITH NORECOVERY or WITH STANBY. Restart the restore sequence, specfying WITH NORECOVERY or WITH SATNBY for all but the final step... "
    thank for reading.

  2. #2
    Join Date
    Sep 2003
    Posts
    522
    if you're restoring the full backup using gui, then check the box saying something about leaving the database in the loading state...wait...yes, leave database nonoperational but able to restore additional transaction logs...there, that's what it say

  3. #3
    Join Date
    Jan 2004
    Posts
    50
    Originally posted by ms_sql_dba
    if you're restoring the full backup using gui, then check the box saying something about leaving the database in the loading state...wait...yes, leave database nonoperational but able to restore additional transaction logs...there, that's what it say
    thank you,
    i do follow your guide, but it does not work. please show me again
    best regard.

  4. #4
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    You cannot restore a backup if you will be applying a log or differential backup without specifying it not be in recoverable status.
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  5. #5
    Join Date
    Jan 2004
    Posts
    50
    Originally posted by rhigdon
    You cannot restore a backup if you will be applying a log or differential backup without specifying it not be in recoverable status.
    Thanks, but i can not understan what you mean, please show me.
    Best regard

  6. #6
    Join Date
    Oct 2001
    Location
    Naples, FL
    Posts
    273
    Do you have BOL installed? This is from BOL - http://msdn.microsoft.com/library/de...kprst_565v.asp

    Try this script to illustrate:
    --Add devices
    USE master
    EXEC sp_addumpdevice 'disk', 'MyFull',
    'c:\myfull.dat'
    EXEC sp_addumpdevice 'disk', 'MyDiff',
    'c:\MyDiff.dat'
    EXEC sp_addumpdevice 'disk', 'MyLog',
    'c:\MyLog.dat'
    --create database
    create database blah
    --set recovery model
    alter database blah set recovery full
    --do our backups
    backup database blah to MyFull

    use blah
    create table afterfull(ident int identity(1,1))

    BACKUP DATABASE blah TO MyDiff WITH DIFFERENTIAL

    use blah
    drop table afterfull
    create table afterdiff (ident int identity(1,1))

    backup log blah to MyLog

    ---Now start our restores
    use master
    RESTORE DATABASE blah
    FROM MyFull
    --The above works, this was the full backup
    RESTORE DATABASE blah
    FROM MyDiff
    --The above does not work as you did the full restore without specifying "with recovery"
    RESTORE DATABASE blah
    FROM MyFull
    with norecovery
    RESTORE DATABASE blah
    FROM MyDiff
    --the above works as we specified norecovery with the full restore
    --now try the tran log
    RESTORE log blah
    FROM MyLog
    --Look familiar? We need to use the NORECOVERY with the full and DIFF so we can apply the log backup
    RESTORE DATABASE blah
    FROM MyFull
    with norecovery
    RESTORE DATABASE blah
    FROM MyDiff
    with norecovery
    RESTORE log blah
    FROM MyLog
    --Voila!
    ---------------
    Ray Higdon MCSE, MCDBA, CCNA

  7. #7
    Join Date
    Feb 2004
    Posts
    4

    Re: Restore transaction log?????????

    1. Create New Database
    2. right click all task and restore database
    3. tab options at Recovery completion state select "Leave database read-only and able to restore additional transaction logs"
    Last edited by fridays; 02-22-04 at 23:23.

Posting Permissions

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