asked
Facing Error While Restoring Transaction Log Backup
Hello, I am facing problem while restoring Transaction Log (*.trn) File into a new database. Here are the steps that i am following. 1. I have created one Full backup then Two Transaction Log backup. 2. Restored Full Backup Onto new Database, with MOVE File Command. 3. While Restoring first Transaction log backup. I am getting this error : Msg 3154, Level 16, State 4, Line 1 The backup set holds a backup of a database other than the existing 'SAMPLE_RESTORED' database. Msg 3013, Level 16, State 1, Line 1 RESTORE LOG is terminating abnormally. Here is the complete code which i am running... --CREATE A NEW DATABASE “SAMPLLE_DB” IN FULL RECOVERY MODEL Use Master GO CREATE DATABASE [SAMPLE_DB] ON PRIMARY ( NAME = N'SAMPLE_DB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SAMPLE_DB.mdf' , SIZE = 4048KB , FILEGROWTH = 1024KB ) LOG ON ( NAME = N'SAMPLE_DB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SAMPLE_DB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%) GO EXEC dbo.sp_dbcmptlevel @dbname=N'SAMPLE_DB', @new_cmptlevel=90 GO ALTER DATABASE [SAMPLE_DB] SET RECOVERY FULL GO --PERFORM MULTIPLE BACKUPS (FULL and T-LOG) --Take a FULL database backup (This is a starting point of the backup set and is mandatory to have before you can initiate T-Log backup) BACKUP DATABASE [SAMPLE_DB] TO DISK = N'D:\Backup\DB_FULL.bak' WITH NOFORMAT, NOINIT, NAME = N'SAMPLE_DB-Full Database Backup',SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO --Create a TEST table Use [SAMPLE_DB] GO CREATE TABLE dbo.Table_1 ( Name varchar(50) NULL ) ON [PRIMARY] GO --Insert a 1st Row in TEST table Insert into dbo.Table_1 values ('Rob') GO --Take 1st T-LOG backup. BACKUP LOG [SAMPLE_DB] TO DISK = N'D:\Backup\SAMPLE_DB-TLog1.trn' WITH NOFORMAT, NOINIT, NAME = N'SAMPLE_DB-Transaction Log Backup',SKIP,NOREWIND,NOUNLOAD,STATS = 10 GO --Insert a 2nd Row in TEST table Insert into dbo.Table_1 values ('Bob') GO --Take 2nd T-LOG backup. BACKUP LOG [SAMPLE_DB] TO DISK = N'D:\Backup\SAMPLE_DB-TLog2.trn' WITH NOFORMAT, NOINIT, NAME = N'SAMPLE_DB-Transaction Log Backup',SKIP,NOREWIND,NOUNLOAD,STATS = 10 GO --RESTORE MULTIPLE BACKUPS (THIS TIME DOING IT ACTUALLY!!) -- Restore 1st FULL Backup with NORECOVERY RESTORE DATABASE [SAMPLE_RESTORED] FROM DISK = N'D:\Backup\DB_FULL.bak' WITH FILE=1, MOVE N'SAMPLE_DB' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SAMPLE_RESTORED.mdf', MOVE N'SAMPLE_DB_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\SAMPLE_RESTORED_1.ldf', NORECOVERY, NOUNLOAD, STATS = 10 GO -- Restore 1st T-Log Backup with NORECOVERY RESTORE LOG [SAMPLE_RESTORED] FROM DISK = N'D:\Backup\SAMPLE_DB-TLog1.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10, REPLACE GO -- Restore 2nd T-Log Backup with NORECOVERY RESTORE LOG [SAMPLE_RESTORED] FROM DISK = N'D:\Backup\SAMPLE_DB-TLog2.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10 GO Error Coming While Doing Transaction Log (.trn) file Restore. Msg 3154, Level 16, State 4, Line 1 The backup set holds a backup of a database other than the existing 'SAMPLE_RESTORED' database. Msg 3013, Level 16, State 1, Line 1 RESTORE LOG is terminating abnormally.
This site runs off of votes. Please mark all helpful answers by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate that by clicking on the check mark next to that answer.
Your second restore should not use NORECOVERY BUT RECOVERY as its the last transtion backup
Assuming they are ready to bring the database to operational state - yes. If the intention is for more restores, then no.
Thanks simmonnd and Blackhawk-17, You are right. I should have used Recovery on my last transaction restore command. But the error was coming on the first transaction restore.
It's telling you that the backup set, the log backup, is from a different database. It's not from the database you were restoring. I'd suggest querying the backup file that is generating the error to understand what is in it. I wrote an article about reading that [information here][1]. [1]:
https://www.simple-talk.com/sql/backup-and-recovery/how-to-get-information-about-your-backups/
Sir, Thanks for your reply. I have taken backup of "SAMPLE_DB" database and I am Restoring it on different database "SAMPLE_RESTORED". SQL Server allowed me to restore when i was restoring *.bak backup on SAMPLE_RESTORED. While It shoots an error when i was restoring *.trn file. IS it impossible to restore backup to database having different name then your original database ? OR Do i always have to Restore on database having same name as original DB ?
You can go to a different database name, yes, but the error means that the log you have is not from the same source as the other restore you ran.
Have you run this multiple times in your testing? Drop the databases, clear out the old files, and test again.
Thanks Sir, I follow your advice and I was successfully able to Restored till lasts Transaction backup. Thanks for the Tip. Still can you give me the reason why i was getting error for the first time ?
The script as is works. I feel that you may have had a typo in an earlier test and the TLog was from a dB of a different name. By clearing everything out and trying again you had a script in a consistent state for the test. This is just a thought but seemed, to me, the most logical.
16 People are following this question.
