asked
Restore error in sql 2012
ok, here again as a newbie I am trying to restore/refresh a database from one instance to another instance. I had taken a full back up, then a differential back up and then two transactional log back up at 07.09 pm and 07.11 pm respectively. Now I am trying to restore/refresh this database backup to another instance replacing the old database there. I restore the full backup, then the differential one and then the first transactional log backup but while trying to restore the second log backup I am getting an error. below is what I was doing: -- restore full back up restore database universitydatabase from disk = 'F:\Study materials\sample databases\Universitydatabase\Backup\fullbackup.bak' with replace, norecovery, move 'UniversityDatabase' to 'F:\Study materials\sample databases\Universitydatabase-inst01\Universitydatabase-inst01.mdf', move 'UniversityDatabase_log' to 'F:\Study materials\sample databases\Universitydatabase-inst01\UniversityDatabase_log.ldf' -- restore differential backup restore database [UniversityDatabase] from disk = 'F:\Study materials\sample databases\Universitydatabase\Differential backup\Diffbackup121115.diff' with norecovery; -- restore first log backup restore log [UniversityDatabase] from disk = 'F:\Study materials\sample databases\Universitydatabase\Transactional backup\universitytran.trn' with norecovery; -- trying to restore the second log backup but got an error **restore log [UniversityDatabase] from disk = 'F:\Study materials\sample databases\Universitydatabase\Transactional backup\universitytran2.trn' with recovery; -- gives error Msg 4305, Level 16, State 1, Line 1 --The log in this backup set begins at LSN 34000000267200001, --which is too recent to apply to the database. An earlier log backup that includes LSN 34000000265600001 can be restored. --Msg 3013, Level 16, State 1, Line 1 --RESTORE LOG is terminating abnormally.** Please help me to understand the error.
It looks as though something else took a log backup between your two log backups. Is this database coming from a shared server? Are there other maintenance routines running that would include backup of the transaction logs at 7:10pm (say)? If so, it's entirely possible that you've just broken the log chain, rendering that database unrecoverable until the next scheduled full backup... If you were just taking a copy of the database, there's a `WITH COPY_ONLY` parameter that can be applied when taking FULL database backups - this doesn't interfere with the normal backup cycle.
thanks so much for the link, I had access to msdb and I found that there were three log backups taken at 07:08, 07:09 and 07:11(normally I wanted two 07:09 and 07:11) the first two log backups(07:08 and 07:09) had same filename(F:\Study materials\sample databases\Universitydatabase\Transactional backup\universitytran.trn) so I guess the later one replaced the old one but the one at 07:11 seemed fine. Was this the problem?
Is there any way I can see the report of all the backups taken in the database. Yes I was aware of the COPY_ONLY parameter, in-fact I took one but it was at 07:29 pm
Do you have access to the msdb database? There are various tables there about backup history. A query such as the one at
https://www.mssqltips.com/sqlservertip/1601/script-to-retrieve-sql-server-database-backup-history-and-no-backups/ might be a good starting point... if you have access to the msdb database.
18 People are following this question.
