Create a database from backup when the backup transaction log is damage.

  • Charles Bilodeau-369032

    Old Hand

    Points: 326

    Good day everyone.

    I would like to know if it possible to restore a dump file to create a database even if the backup transaction log is damage.
    Can the transaction log from the backup be skipped and a new transaction log be created once the data file is restored?

  • Gail Shaw

    SSC Guru

    Points: 1004446

    A database backup doesn't contain all the transaction log, just part of the active portion. You can't skip restoring that (it's what makes the database consistent), but if the backup succeeded with the source DB having a damaged log file, then the backup should restore ok.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Charles Bilodeau-369032

    Old Hand

    Points: 326

    The restore does not complete, it produce an error and than stop.
    The database remain in restore mode. Is there any way that I could remove the database from restore mode and bring it back on line?

  • Gail Shaw

    SSC Guru

    Points: 1004446

    What error?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Charles Bilodeau-369032

    Old Hand

    Points: 326

    Here is the error I receive.

    Msg 9004, Level 16, State 3, Line 113
    An error occurred while processing the log for database 'TEST'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.
    Msg 3013, Level 16, State 1, Line 113
    RESTORE DATABASE is terminating abnormally.

  • Gail Shaw

    SSC Guru

    Points: 1004446

    And if you try the
    CONTINUE_AFTER_ERROR option on the restore?

    Is the source DB intact?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Charles Bilodeau-369032

    Old Hand

    Points: 326

    I tried to run the restore in the following way:

    RESTORE DATABASE DMPROD
        FROM DISK = 'D:\VMDB2012$DM_DMPROD_FULL_20170830_020006.bak'
        WITH MOVE 'CORDDB' TO 'E:\SQL Databases\DMPROD\corddb.MDF', 
        MOVE 'CORDDB_Log' TO 'E:\SQL Databases\DMPROD\corddb_log.LDF';
    RESTORE LOG CORDDB_Log
        FROM VMDB2012$DM_DMPROD_FULL_20170830_020006
        WITH CONTINUE_AFTER_ERROR;

    I received this for error
    Msg 9004, Level 16, State 3, Line 1
    An error occurred while processing the log for database 'DMPROD'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.
    Msg 3206, Level 16, State 1, Line 5
    Backup device 'VMDB2012$DM_DMPROD_FULL_20170830_020006' does not exist. To view existing backup devices, use the sys.backup_devices catalog view. To create a new backup device use either sp_addumpdevice or SQL Server Management Studio.
    Msg 3013, Level 16, State 1, Line 5
    RESTORE LOG is terminating abnormally.

  • SQL_Hacker

    SSCommitted

    Points: 1514

    Charles Bilodeau-369032 - Friday, September 1, 2017 8:54 AM

    I tried to run the restore in the following way:

    RESTORE DATABASE DMPROD
        FROM DISK = 'D:\VMDB2012$DM_DMPROD_FULL_20170830_020006.bak'
        WITH MOVE 'CORDDB' TO 'E:\SQL Databases\DMPROD\corddb.MDF', 
        MOVE 'CORDDB_Log' TO 'E:\SQL Databases\DMPROD\corddb_log.LDF';
    RESTORE LOG CORDDB_Log
        FROM VMDB2012$DM_DMPROD_FULL_20170830_020006
        WITH CONTINUE_AFTER_ERROR;

    I received this for error
    Msg 9004, Level 16, State 3, Line 1
    An error occurred while processing the log for database 'DMPROD'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.
    Msg 3206, Level 16, State 1, Line 5
    Backup device 'VMDB2012$DM_DMPROD_FULL_20170830_020006' does not exist. To view existing backup devices, use the sys.backup_devices catalog view. To create a new backup device use either sp_addumpdevice or SQL Server Management Studio.
    Msg 3013, Level 16, State 1, Line 5
    RESTORE LOG is terminating abnormally.

    A key piece of information is the "Backup device...does not exist". Wherever the backup was created, it was created using a backup device (dumpdevice). That device does not exist on the instance where you're trying to perform the restore. You can add the device by running "sp_addumpdevice" (as it shows in the error message) and then the instance will recognize the device. When creating the dump device, it has to match the path (which is all a backup device is--a "shortcut" to a file path) of the device that already exists on the other instance. Note that if these are different machines, you will likely have to use UNC path to navigate to that path--i.e. \\servername\drivename\foldername\subfoldername... instead of C:\Folder\Subfolder.

    Alan H
    MCSE - Data Management and Analytics
    Senior SQL Server DBA

    Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Charles Bilodeau-369032

    Old Hand

    Points: 326

    I saw that error and did what you just explained. The backup is on the same machine so I created my sql command the following way.

    EXEC sp_addumpdevice 'disk', 'dmprod','D:\VMDB2012$DM_DMPROD_FULL_20170830_020006.bak';

    I than ran the restore again and I received the exact same error.

    So I wanted to check if my backup device did exists so I ran the command again and received the following error.\

    Msg 15026, Level 16, State 1, Procedure sp_addumpdevice, Line 85
    Logical device 'dmprod' already exists.

  • SQL_Hacker

    SSCommitted

    Points: 1514

    What does
    SELECT * FROM sys.backup_devices
    return?

    Alan H
    MCSE - Data Management and Analytics
    Senior SQL Server DBA

    Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Sue_H

    SSC Guru

    Points: 90287

    Charles Bilodeau-369032 - Friday, September 1, 2017 8:54 AM

    I tried to run the restore in the following way:

    RESTORE DATABASE DMPROD
        FROM DISK = 'D:\VMDB2012$DM_DMPROD_FULL_20170830_020006.bak'
        WITH MOVE 'CORDDB' TO 'E:\SQL Databases\DMPROD\corddb.MDF', 
        MOVE 'CORDDB_Log' TO 'E:\SQL Databases\DMPROD\corddb_log.LDF';
    RESTORE LOG CORDDB_Log
        FROM VMDB2012$DM_DMPROD_FULL_20170830_020006
        WITH CONTINUE_AFTER_ERROR;

    I received this for error
    Msg 9004, Level 16, State 3, Line 1
    An error occurred while processing the log for database 'DMPROD'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.
    Msg 3013, Level 16, State 1, Line 1
    RESTORE DATABASE is terminating abnormally.
    Msg 3206, Level 16, State 1, Line 5
    Backup device 'VMDB2012$DM_DMPROD_FULL_20170830_020006' does not exist. To view existing backup devices, use the sys.backup_devices catalog view. To create a new backup device use either sp_addumpdevice or SQL Server Management Studio.
    Msg 3013, Level 16, State 1, Line 5
    RESTORE LOG is terminating abnormally.

    I didn't notice this before Alan mentioned the devices but try restoring just the full backup. Nothing other than the bak file. And use with continue_after_error for the restore of the full backup. And just that .bak file.

    It's in the restore log where you reference the device named VMDB2012$DM_DMPROD_FULL_20170830_020006
    And that device does not exit. And for now, you only want to restore the full database backup file anyway
    Then when you went to add the device, you are adding one called dmprod which as it says in the error, already exists. So those are two different devices.

    Sue

  • SQL_Hacker

    SSCommitted

    Points: 1514

    Good catch Sue!!
    Another thing I didn't notice before is it appears that you're trying to restore a backup for one database (DMPROD) and a log from another database (CORDDB).
    Like Sue recommended, execute a restore from the .bak file only, still using continue_after_error.
    So, the syntax would be:

    RESTORE DATABASE DMPROD
    FROM DISK = 'D:\VMDB2012$DM_DMPROD_FULL_20170830_020006.bak'
    WITH CONTINUE_AFTER_ERROR

    Not sure why you're referencing a different name in the MOVE statements, but if you're trying to rename the database that you're restoring, you can't do it there. Instead, do the following:

    RESTORE DATABASE DMPROD_Copy1
    FROM DISK = 'D:\VMDB2012$DM_DMPROD_FULL_20170830_020006.bak'
    WITH CONTINUE_AFTER_ERROR

    Alan H
    MCSE - Data Management and Analytics
    Senior SQL Server DBA

    Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Charles Bilodeau-369032

    Old Hand

    Points: 326

    I did that and received the exact same error.

  • SQL_Hacker

    SSCommitted

    Points: 1514

    Okay, I don't know why I didn't see this last week, but there are a number of problems with the statement you posted.
    First, you have to pick which "FROM" you're going to use--when you type "FROM DISK", that indicates to the database engine that it should go out to disk to get the backup file. If you use "FROM VMDB2012$DM_DMPROD_FULL_20170830_020006", that is the backup device (which already has the path saved in the device). Don't use both; pick one or the other--and, since you seem to be having problems with the backup device, I personally would point to the .bak file directly and leave out the backup device.
    Next, take out the "RESTORE LOG" portion completely, and do it separately. The first step would be to figure out if the .bak file is usable--if we get to that point, you can restore the log later.
    Finally, do you "have" to move the database files? Or, can they be installed in the default directories? Everything you can remove to simplify this problem will help get at the root problem.
    To start, please try running the following:
    RESTORE DATABASE DMPROD
    FROM DISK = 'D:\VMDB2012$DM_DMPROD_FULL_20170830_020006.bak'
    WITH CONTINUE_AFTER_ERROR, RECOVERY;

    After this command executes, if there are errors, please post them. If there are no errors, then you have proven there is nothing wrong with the .bak file.
    Assuming the above restore statement works, you can then drop the database (to be able to restore the log file(s) you have) and restore the .bak and any log file backups. To do that, you need to alter the above statement and execute "NORECOVERY" instead of "RECOVERY". This will leave your database in the "RECOVERING" state, and you can then restore the log file backup(s). If you have more than one, make sure to use "NORECOVERY" on each of them, until you get to the last one...which you can then specify "RECOVERY".
    After restoring the database, you can then move the files around using the following sequence (which comes from https://docs.microsoft.com/en-us/sql/relational-databases/databases/move-user-databases:
    ALTER DATABASE DMPROD SET SINGLE_USER
    GO
    ALTER DATABASE DMPROD SET OFFLINE
    GO
    MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' );  GO
    EX: MODIFY FILE(NAME = 'CORDDB', FILENAME = 'E:\SQL Databases\DMPROD\corddb.MDF');  GO
    MODIFY FILE(NAME = 'CORDDB_Log', FILENAME = 'E:\SQL Databases\DMPROD\corddb_log.LDF'); GO

    ALTER DATABASE DMPROD SET ONLINE
    GO
    ALTER DATABAE DMPROD SET MULTI_USER
    GO

    Alan H
    MCSE - Data Management and Analytics
    Senior SQL Server DBA

    Best way to ask a question: http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • Charles Bilodeau-369032

    Old Hand

    Points: 326

    I used the script you provided:
    RESTORE DATABASE DMPROD
    FROM DISK = 'D:\VMDB2012$DM_DMPROD_FULL_20170830_020006.bak'
    WITH CONTINUE_AFTER_ERROR, RECOVERY;

    I  made some changes  and I was able to restore the database but I still received an error message. As long as I can access the database and perform some test I should be good.

    Processed 1101728 pages for database 'DMPROD', file 'corddb' on file 1.
    Processed 1507 pages for database 'DMPROD', file 'corddb_log' on file 1.
    Msg 9004, Level 16, State 3, Line 9
    An error occurred while processing the log for database 'DMPROD'. If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.
    Converting database 'DMPROD' from version 706 to the current version 782.
    Database 'DMPROD' running the upgrade step from version 706 to version 770.
    Database 'DMPROD' running the upgrade step from version 770 to version 771.
    Database 'DMPROD' running the upgrade step from version 771 to version 772.
    Database 'DMPROD' running the upgrade step from version 772 to version 773.
    Database 'DMPROD' running the upgrade step from version 773 to version 774.
    Database 'DMPROD' running the upgrade step from version 774 to version 775.
    Database 'DMPROD' running the upgrade step from version 775 to version 776.
    Database 'DMPROD' running the upgrade step from version 776 to version 777.
    Database 'DMPROD' running the upgrade step from version 777 to version 778.
    Database 'DMPROD' running the upgrade step from version 778 to version 779.
    Database 'DMPROD' running the upgrade step from version 779 to version 780.
    Database 'DMPROD' running the upgrade step from version 780 to version 781.
    Database 'DMPROD' running the upgrade step from version 781 to version 782.
    RESTORE WITH CONTINUE_AFTER_ERROR was successful but some damage was encountered. Inconsistencies in the database are possible.
    RESTORE DATABASE successfully processed 1103235 pages in 1915.275 seconds (4.500 MB/sec).

Viewing 15 posts - 1 through 15 (of 15 total)

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