Diff Restore Fails with SQL error 3136

ftauteftaute Posts: 6
edited February 17, 2009 6:52AM in SQL Backup Previous Versions
I have a SQL2000 production database which will not restore from a diff or trans log backup to a new db. Exits with sql backup exit code 1100, sql error 3136 after it has restored the full backup. (using the default settings of with recovery). The original DB is in bulk-logged recovery model.

However, I have done a restore from a full backup to a new db which is also now in production - and have then added this new db to the SAME backup jobs in sql backup which are doing the full, diff and log backups for the original db.
I can then successfully do a restore from diff or log of this new db.

Both dbs seem to have the same settings, recovery model etc.
the only big difference is that the diff backups for the new db are large - about 1/3 the size of the full weekly backups, wheras my original db diff backups are about 1/10 the size of the full. And the original db is larger, and by far the more active of the two. No idea if this is a clue to the cause, but there it is.

I have also done some other tests of a restore of a full backup from the original 30GB db, to a new test db, then done a full and diff backup of that db with sql backup, and have been able to restore from the diff. So what the *^(*2@ ?

The only other thing that may be at play here, is that the original db used to be in a cluster, and was then moved to new drives, and reattached in a new non-clustered instance of sql server.

Any suggestions would be most welcome!
Thanks
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    Could you please post the contents of the SQL Backup log for the failed restore? By default, the log files are stored in C:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\<instance name>.

    Thanks.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • ftauteftaute Posts: 6
    Looks like we have two logs - #1
    SQL Backup log file 5.4.0.55

    -SQL "RESTORE DATABASE [PARestore] FROM DISK = 'I:\PABackup\PracticeAdmin\FULL_(local)_Practiceadmin_20090207_181027.sqb' WITH NORECOVERY, MOVE 'PracticeAdmin_dat' TO 'O:\PARestore\PARestore_1.mdf', MOVE 'Indexes' TO 'O:\PARestore\PARestore_2.mdf', MOVE 'PracticeAdmin_log' TO 'O:\PARestore\PARestore_3.ldf', PASSWORD = 'XXXXXXXXXXXX', REPLACE "
    PROCESSES COMPLETED SUCCESSFULLY

    2/13/2009 7:21:02 AM: Restoring PARestore (database) from:
    2/13/2009 7:21:02 AM: I:\PABackup\PracticeAdmin\FULL_(local)_Practiceadmin_20090207_181027.sqb

    2/13/2009 7:21:02 AM: RESTORE DATABASE [PARestore] FROM VIRTUAL_DEVICE = 'SQLBACKUP_14359837-1A50-4AEF-8DC1-A9679747ACE3', VIRTUAL_DEVICE = 'SQLBACKUP_14359837-1A50-4AEF-8DC1-A9679747ACE301', VIRTUAL_DEVICE = 'SQLBACKUP_14359837-1A50-4AEF-8DC1-A9679747ACE302', VIRTUAL_DEVICE = 'SQLBACKUP_14359837-1A50-4AEF-8DC1-A9679747ACE303', VIRTUAL_DEVICE = 'SQLBACKUP_14359837-1A50-4AEF-8DC1-A9679747ACE304', VIRTUAL_DEVICE = 'SQLBACKUP_14359837-1A50-4AEF-8DC1-A9679747ACE305', VIRTUAL_DEVICE = 'SQLBACKUP_14359837-1A50-4AEF-8DC1-A9679747ACE306', VIRTUAL_DEVICE = 'SQLBACKUP_14359837-1A50-4AEF-8DC1-A9679747ACE307', VIRTUAL_DEVICE = 'SQLBACKUP_14359837-1A50-4AEF-8DC1-A9679747ACE308', VIRTUAL_DEVICE = 'SQLBACKUP_14359837-1A50-4AEF-8DC1-A9679747ACE309', VIRTUAL_DEVICE = 'SQLBACKUP_14359837-1A50-4AEF-8DC1-A9679747ACE310', VIRTUAL_DEVICE = 'SQLBACKUP_14359837-1A50-4AEF-8DC1-A9679747ACE311', VIRTUAL_DEVICE = 'SQLBACKUP_14359837-1A50-4AEF-8DC1-A9679747ACE312', VIRTUAL_DEVICE = 'SQLBACKUP_14359837-1A50-4AEF-8DC1-A9679747ACE313', VIRTUAL_DEVICE = 'SQLBACKUP_14359837-1A50-4AEF-8DC1-A9679747ACE314' WITH BUFFERCOUNT = 30, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576 , NORECOVERY, MOVE 'PracticeAdmin_dat' TO 'O:\PARestore\PARestore_1.mdf', MOVE 'Indexes' TO 'O:\PARestore\PARestore_2.mdf', MOVE 'PracticeAdmin_log' TO 'O:\PARestore\PARestore_3.ldf', REPLACE

    Processed 2575552 pages for database 'PARestore', file 'PracticeAdmin_dat' on file 1.
    Processed 1255464 pages for database 'PARestore', file 'Indexes' on file 1.
    Processed 82 pages for database 'PARestore', file 'PracticeAdmin_log' on file 1.
    RESTORE DATABASE successfully processed 3831098 pages in 771.778 seconds (40.665 MB/sec).
    2/13/2009 7:35:40 AM: SQL Backup process ended.


    then #2
    SQL Backup log file 5.4.0.55

    -SQL "RESTORE DATABASE [PARestore] FROM DISK = 'I:\PABackup\PracticeAdmin\DIFF_(local)_Practiceadmin_20090212_180851.sqb' WITH RECOVERY, PASSWORD = 'XXXXXXXXXXXX' "
    ERRORS AND WARNINGS


    2/13/2009 7:35:40 AM: Restoring PARestore (database) from:
    2/13/2009 7:35:40 AM: I:\PABackup\PracticeAdmin\DIFF_(local)_Practiceadmin_20090212_180851.sqb

    2/13/2009 7:35:40 AM: RESTORE DATABASE [PARestore] FROM VIRTUAL_DEVICE = 'SQLBACKUP_1F09A775-ECAA-4946-9575-EC42CCCF5C0E', VIRTUAL_DEVICE = 'SQLBACKUP_1F09A775-ECAA-4946-9575-EC42CCCF5C0E01', VIRTUAL_DEVICE = 'SQLBACKUP_1F09A775-ECAA-4946-9575-EC42CCCF5C0E02', VIRTUAL_DEVICE = 'SQLBACKUP_1F09A775-ECAA-4946-9575-EC42CCCF5C0E03', VIRTUAL_DEVICE = 'SQLBACKUP_1F09A775-ECAA-4946-9575-EC42CCCF5C0E04', VIRTUAL_DEVICE = 'SQLBACKUP_1F09A775-ECAA-4946-9575-EC42CCCF5C0E05', VIRTUAL_DEVICE = 'SQLBACKUP_1F09A775-ECAA-4946-9575-EC42CCCF5C0E06', VIRTUAL_DEVICE = 'SQLBACKUP_1F09A775-ECAA-4946-9575-EC42CCCF5C0E07', VIRTUAL_DEVICE = 'SQLBACKUP_1F09A775-ECAA-4946-9575-EC42CCCF5C0E08', VIRTUAL_DEVICE = 'SQLBACKUP_1F09A775-ECAA-4946-9575-EC42CCCF5C0E09', VIRTUAL_DEVICE = 'SQLBACKUP_1F09A775-ECAA-4946-9575-EC42CCCF5C0E10', VIRTUAL_DEVICE = 'SQLBACKUP_1F09A775-ECAA-4946-9575-EC42CCCF5C0E11', VIRTUAL_DEVICE = 'SQLBACKUP_1F09A775-ECAA-4946-9575-EC42CCCF5C0E12', VIRTUAL_DEVICE = 'SQLBACKUP_1F09A775-ECAA-4946-9575-EC42CCCF5C0E13', VIRTUAL_DEVICE = 'SQLBACKUP_1F09A775-ECAA-4946-9575-EC42CCCF5C0E14' WITH BUFFERCOUNT = 30, BLOCKSIZE = 65536, MAXTRANSFERSIZE = 1048576 , RECOVERY

    2/13/2009 7:35:41 AM: SQL Server error

    SQL error 3013: SQL error 3013: RESTORE DATABASE is terminating abnormally.
    SQL error 3136: SQL error 3136: Cannot apply the backup on device 'SQLBACKUP_1F09A775-ECAA-4946-9575-EC42CCCF5C0E' to database 'PARestore'.


    Thanks a lot
    Fred
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Could you please check LSN values of the original backups, to check if they match up? E.g.
    SELECT *
FROM msdb..backupmediafamily a
INNER JOIN msdb..backupset b ON a.media_set_id = b.media_set_id
WHERE b.database_name = !your source database name!
  AND &#40;a.physical_device_name LIKE '%FULL_&#40;local&#41;_Practiceadmin_20090207_181027.sqb'
   OR a.physical_device_name LIKE '%DIFF_&#40;local&#41;_Practiceadmin_20090212_180851.sqb'&#41;

    If you're not sure what to check for, see here for details.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • ftauteftaute Posts: 6
    thanks Peter, here are the LSN's - I dont see any correlation but poking around among the other backups on dbs where the restore does work, I see that the full checkpoint LSN matches the diff dbbackup LSN.

    ID file first_lsn last_lsn checkpoint_lsn database_backup_lsn
    1258 FULL_(local)_Practiceadmin_20090207_181027.sqb 255693000002340800001 255693000002471900001 255693000002340800035 255431000001163200099
    1594 DIFF_(local)_Practiceadmin_20090212_180851.sqb 257122000001116500038 257122000001159100001 257122000001116500038 256975000001127700024

    thanks again
    Fred
    · Share on Twitter
  • ftauteftaute Posts: 6
    hmm, I now see where the LSN pollution is coming from. We have a separate backup that is called from a sql agent job daily in the morning, and the diff is keying off the checkpoint_LSN from that backup.

    However, we are trying to use the weekly full and daily diff from the sql backup toool to streamline file copying to an offsite location, and so trying to keep the files on weekdays smaller, with the larger full backup being done on the weekend when it is possible to move it without interfering with other bandwidth demands.

    Is there a way to modify the diff job to key off the LSN from that one specific weekly full backup then?

    thanks a lot
    Fred
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    Unfortunately, no, not in SQL Server 2000. In SQL Server 2005 and later, there is a COPY_ONLY option which you can apply to your daily full backups, which will cause later differential backups to ignore it as a base backup.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • ftauteftaute Posts: 6
    OK thanks, another nail in the 'migrate to 2008 coffin' ;)

    Fred
    · Share on Twitter
Sign In or Register to comment.