Restoration fail

soonyusoonyu Posts: 8
edited July 10, 2006 10:03PM in SQL Backup Previous Versions
I restore fullbackup and diffrential backup with standby mode to sqlserver
I want change the standby mode to recovery mode
but it give me this message...


Server: Msg 3013
RESTORE DATABASE is terminating abnormally.
Server: Msg 3136
Cannot apply the backup on device 'SQLBACKUP_DF0467BB-91AB-4D0A-B4EA-8C0F440C57A6' to database 'studentdb'.

SQL error code: 3136


script
master..sqlbackup N'-SQL "RESTORE DATABASE [studentdb] FROM DISK = ''D:\student_differentialbackup.sqb'' WITH RECOVERY


any idea? why wrong? Is that mean i can't change the standby mode to recovery mode? or i need to backup another transaction and restore with recovery mode with new backup transaction?

Soonyu

Comments

  • peteypetey Posts: 2,358 New member
    Could you pls run the RESTORE HEADERONLY command e.g.

    master..sqlbackup '-sql "RESTORE HEADERONLY FROM DISK = [...]" '

    and post the values for the DifferentialBaseLSN column for the full and differential backups? Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • SQL Backup (DLL v4.2.0.425)
    Reading file header of "D:\student_differentialbackup.sqb"

    Backup name : Database (studentdb), 7/10/2006 5:30:01 AM
    Description : Backup on 7/10/2006 5:30:01 AM Server: STAR
    Database: studentdb
    Backup type : 5 (Differential database)
    User name : STAR\dba
    Backup start : 7/10/2006 5:30:01 AM
    Backup finish: 7/10/2006 5:50:31 AM

    Server name : STAR
    Database name : studentdb
    Database version : 539
    Creation date : 10/1/2005 12:49:18 PM
    Size : 3.111 GB
    Sort order : 52
    Code page : 228
    Machine name : STAR
    Collation : SQL_Latin1_General_CP1_CI_AS

    First LSN : 153921000001594400005
    Last LSN : 153921000006626900001
    Checkpoint LSN : 153921000001594400005
    Differential base LSN : 153774000000132000003



    (25 row(s) affected)

    name value

    exitcode 0
    sqlerrorcode 0
    filename01 D:\student_differentialbackup.sqb

    (3 row(s) affected)
  • peteypetey Posts: 2,358 New member
    I guess that this is the backup that you are having problems applying the differential restore with. What are the Differential base LSN values for the full backup and the differential backup that you applied earlier? They need to be the same values if you want to apply this differential backup.

    You could also check if there was a full backup made between the full backup that you restored and the differential backup that you are trying to restore e.g.

    SELECT TOP 50 b.type, b.backup_finish_date, a.physical_device_name, * FROM msdb..backupmediafamily a
    INNER JOIN msdb..backupset b ON a.media_set_id = b.media_set_id
    WHERE b.database_name = 'studentdb'
    ORDER BY b.media_set_id DESC

    If there was, that would explain why you are unable to restore the differential backup.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.