August 30, 2017 at 10:29 am
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?
August 30, 2017 at 11:25 am
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
August 30, 2017 at 11:50 am
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?
August 30, 2017 at 1:29 pm
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
August 31, 2017 at 1:56 pm
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.
August 31, 2017 at 11:20 pm
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
September 1, 2017 at 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.
September 1, 2017 at 9:02 am
Charles Bilodeau-369032 - Friday, September 1, 2017 8:54 AMI 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/
September 1, 2017 at 11:48 am
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.
September 1, 2017 at 12:45 pm
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/
September 1, 2017 at 1:21 pm
Charles Bilodeau-369032 - Friday, September 1, 2017 8:54 AMI 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
September 1, 2017 at 1:33 pm
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/
September 5, 2017 at 7:30 am
I did that and received the exact same error.
September 5, 2017 at 8:38 am
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/
September 5, 2017 at 11:02 am
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 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply