SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Charles Bilodeau-369032
Charles Bilodeau-369032
SSC Veteran
SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)

Group: General Forum Members
Points: 238 Visits: 34
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?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)

Group: General Forum Members
Points: 919716 Visits: 48853
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
Charles Bilodeau-369032
SSC Veteran
SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)

Group: General Forum Members
Points: 238 Visits: 34
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?
GilaMonster
GilaMonster
SSC Guru
SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)

Group: General Forum Members
Points: 919716 Visits: 48853
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
Charles Bilodeau-369032
SSC Veteran
SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)

Group: General Forum Members
Points: 238 Visits: 34
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)SSC Guru (919K reputation)

Group: General Forum Members
Points: 919716 Visits: 48853
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
Charles Bilodeau-369032
SSC Veteran
SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)

Group: General Forum Members
Points: 238 Visits: 34
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.
Alan Horsman
Alan Horsman
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1370 Visits: 404
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
Charles Bilodeau-369032
SSC Veteran
SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)SSC Veteran (238 reputation)

Group: General Forum Members
Points: 238 Visits: 34
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.
Alan Horsman
Alan Horsman
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1370 Visits: 404
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search