Post reply

SQL Restore Problem

  • October 26, 2005 at 6:42 am

    #111167

    Hi all.

    I have a problem with a SQL restore on SQL 2000.

    I have a full backup file created each night and during operational hours I create differential backups to flat files.

    When I try to restore to a different server, I can apply the full with the move command, but when I restore the differential I get a MSG saying I can't apply the differential to the database.

    When I look at headernly info the full has a Database type: 1 and the Differential has a Database type: 5.

    I know the full happens before the differential, but I can not seem to get the differential to be applied. I am using norecovery on the full restore, and norecovery on the diff, followed by a recovery.

    I am missing something, but not sure what it is. Can someone assist?

    Thanks,

    David.

     

  • October 26, 2005 at 6:53 am

    #600105

    As a followup my restore command is as follows:

    USE MASTER

    RESTORE DATABASE [acuity_sys] from Disk=N'F:\FTProot\SQLBackup\Acuity_sys_full_backup.bak' with norecovery, move 'acusydb70data' to 'F:\Acuity_sys_data.mdf', move 'acusydblog' to F:\Acuity_sys_log.ldf'

    I then try to restore the differential

    RESTORE DATABASE [acuity_sys] from Disk=N'F:\FTPRoot\SQLBackup\Acuity_sys_diff_8am_backup.bak' with norecovery.

    I get MSG 3136 Cannot apply backup from device F:\FTPRoot\SQLBackup\Acuity_sys_diff_8AM_backup.bak' to database acuity_sys

  • October 27, 2005 at 7:53 am

    #600575

    Have you tried using the with move command when restoring the differential?

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • October 28, 2005 at 11:46 am

    #601027

    Yes, I have with no luck. I get the same error.

    I also looked at restore headers only and the file types look OK.

    I am unsure of how to solve the problem. Any suggestions?

    I am not sure of what is causing this.

    David.

  • October 28, 2005 at 1:14 pm

    #601068

    When you do your differential backup, do you use WITH INIT or WITH NOINIT (overwrite or append)?

    If you are appending (WITH NOINIT), then the restore is trying to use an old differential and you need to tell it which differential to use. If this is the case, use RESTORE FILELIST ONLY. This will give a list of the files in the backup and then you can choose the correct one to restore. Refer to the BOL for the actual syntax for RESTORE FILELIST ONLY.

    -SQLBill

  • October 28, 2005 at 1:18 pm

    #601072

    BTW-if that is the case, check out Example 2 in the BOL under RESTORE DATABASE for an example of restoring from a specific part of the backup.

    -SQLBill

  • October 31, 2005 at 5:27 am

    #601257

    I am using INIT for the backup.

  • October 31, 2005 at 1:51 pm

    #601414

    What makes this more perplexing is that the files look good. I am really not sure what SQL is trying to indicate with a 3136 error. I believe SQL is saying there is something in the file that I can't apply because of maybe a file type. But since I don't know much about SQL internals and how it applies backups. I know the files are in the right order, in that the full is taken at 9PM nightly and differentials are taken every two hours during the day. My goal is to be able to restore to some point in time, say the business corrupts the database at 10AM. I can restore back to 8AM. (god forbid of course).

    I am restoring to a different server, so I am using the move command to redirect the database to a new location. Restore Filelist only gave me the logical filenames I needed.

    I can restore the full. The differential is the problem.

    Any help would be appreciated. I see this as a fairly tough issue, because, no one really has an answer for me.

     

  • November 28, 2005 at 1:30 pm

    #606216

    Hello all,

    I have solved this problem. The problem was caused by the timing of the backup. As it turns out there was an additional full backup in an automated job (Veritas) that was causing the issue. Once I ripped out all the backup jobs (it took me a while), I have replaced them with flat file fulls. If anyone gets a 3136 error saying they can't apply the backups check the fileheaders and headeronly data in the files and look at backup times and dates.

Viewing 9 posts - 1 through 8 (of 8 total)

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