June 22, 2011 at 11:48 pm
Hi ALL, i am trying out to restore today backup as at 1220hrs. I have restore the full backup and differential backup, but having difficulty with the transaction log, i have selected all the transaction logs from the start to 1220hrs, but however it got the error message:
"The log in this backup set terminates at LSN 21000000039700001, which is too early to apply to the database. A more recent log backup that includes LSN 22000000011100001 can be restored. "
But i cant find this LSN 22000000011100001, the positions are in order. What might have happen?
P.S. just now i have restore successfully to 1120hrs transaction logs. thanks
June 23, 2011 at 12:46 am
The log backup you have is too early, all its transactions are already in the restored DB. Try the next one in the chain.
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
June 23, 2011 at 2:58 am
GilaMonster (6/23/2011)
The log backup you have is too early, all its transactions are already in the restored DB. Try the next one in the chain.
I have tried all kinds of combination but still cannot. Nevertheless i re-setup again for the database backup. I am using maintenance plan to backup as it contains timestamps of each individual backups that were done.
I trying out my restoration and i found it cumbersome if it were to use maintenance plan as it generate one file for every backup regardless whether is full, differential or transaction log.
I have tried to drop one of the database that i created yesterday, and restore it as today state. For the restoration portion, i have to restore the full backup file first, then follow by differential file then follow by transaction log files.
The most cumbersome was for the restoration of transaction logs as my backup strategy for transaction log was every 10 mins. So i have to restore the transaction logs, 1 file by 1 file, and i will need to remember which file that i have restore then follow by another , in sequence. Is there any other better way for the restoration? thanks
June 23, 2011 at 3:09 am
While backups frequently want to be totally automated, normally I find restores are done manually - especially for point-in-time issues requiring multiple log files.
Thus for backups I would normally favour command-line, script or timed job as i want it to happen automatically, for RESTORES I am more than happy to use the GUI features in SSMS which will by default select the most recent FULL, folllowed by the most recent DIFF, followed by all the logs in the right order.
So the default in SSMS is to bring you as up-to-date as possible, which sounds like exactly what you want.
It will alsi let you generate the TSQl script to do those restores if that helps.
Mike
June 23, 2011 at 3:18 am
I will be using 1GB backup tapes to backup the full backup, differential and transaction logs backup. Any backup strategy (dun take into consideration for RTP and RPO). The data averages to a few GBs, but it will tend to grows day by day. So i will need to tackle this issue in the future. The backup software that i will be using is HP Data PRotector which i am not really familiar with. THanks
June 23, 2011 at 11:03 am
Don't restore one by one, that's just plain silly.
MSDB stores the backup history, generate the restore scripts from MSDB, far easier than trying to figure out what order they should be in.
You need to start with the first log backup that ran AFTER the differential backup completed.
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
June 23, 2011 at 7:52 pm
GilaMonster (6/23/2011)
Don't restore one by one, that's just plain silly.MSDB stores the backup history, generate the restore scripts from MSDB, far easier than trying to figure out what order they should be in.
You need to start with the first log backup that ran AFTER the differential backup completed.
yup, this is what i think so too. Is quite silly to restore one by one for the transaction logs, but i am using Management Studio to do the restoration.
I am not so sure about generating the restore scripts from MSDB. Is it possible to guide me along? thanks
June 23, 2011 at 9:15 pm
Illegal_Operation (6/23/2011)
GilaMonster (6/23/2011)
Don't restore one by one, that's just plain silly.MSDB stores the backup history, generate the restore scripts from MSDB, far easier than trying to figure out what order they should be in.
You need to start with the first log backup that ran AFTER the differential backup completed.
yup, this is what i think so too. Is quite silly to restore one by one for the transaction logs, but i am using Management Studio to do the restoration.
I am not so sure about generating the restore scripts from MSDB. Is it possible to guide me along? thanks
what i did was after i restore the full backup and differential backup, i use T-SQL at the Management Studio to restore the transaction logs. This is the commands that i execute.
RESTORE LOG Database
FROM DISK = 'C:\Backup\Database\Database_backup_2011_06_24_090008_6875000.trn'
WITH NORECOVERY
RESTORE LOG Database
FROM DISK = 'C:\Backup\Database\Database_backup_2011_06_24_091009_2031250.trn'
WITH NORECOVERY
RESTORE LOG Database
FROM DISK = 'C:\Backup\Database\Database_backup_2011_06_24_092025_7031250.trn'
WITH NORECOVERY
RESTORE LOG Database
FROM DISK = 'C:\Backup\Database\Database_backup_2011_06_24_093009_3125000.trn'
WITH NORECOVERY
RESTORE LOG Database
FROM DISK = 'C:\Backup\Database\Database_backup_2011_06_24_094004_6093750.trn'
WITH NORECOVERY
RESTORE LOG Database
FROM DISK = 'C:\Backup\Database\Database_backup_2011_06_24_095029_5781250.trn'
WITH NORECOVERY
RESTORE LOG Database
FROM DISK = 'C:\Backup\Database\Database_backup_2011_06_24_100014_2343750.trn'
WITH NORECOVERY
RESTORE LOG Database
FROM DISK = 'C:\Backup\Database\Database_backup_2011_06_24_101027_6718750.trn'
WITH RECOVERY
Is it a faster way? thanks
June 26, 2011 at 10:56 pm
any comments or advice? thanks
June 27, 2011 at 12:15 am
got some problems again. I tried to restore last friday around 1700hrs backup.
I restore the full backup which is successfull and the differential backup on friday which is successful too. But when i tried to restore the transaction logs from friday morning 9am to 5pm using command line. It hit the below problems. By the way, I have attached the command for the restore of the Transaction Logs.
Processed 0 pages for database 'Database', file 'Database' on file 1.
Processed 1 pages for database 'Database', file 'Database_log' on file 1.
RESTORE LOG successfully processed 1 pages in 0.038 seconds (0.025 MB/sec).
Processed 0 pages for database 'Database', file 'Database' on file 1.
Processed 0 pages for database 'Database', file 'Database_log' on file 1.
RESTORE LOG successfully processed 0 pages in 0.006 seconds (0.000 MB/sec).
Processed 0 pages for database 'Database', file 'Database' on file 1.
Processed 0 pages for database 'Database', file 'Database_log' on file 1.
RESTORE LOG successfully processed 0 pages in 0.006 seconds (0.000 MB/sec).
Processed 0 pages for database 'Database', file 'Database' on file 1.
Processed 0 pages for database 'Database', file 'Database_log' on file 1.
RESTORE LOG successfully processed 0 pages in 0.007 seconds (0.000 MB/sec).
Processed 0 pages for database 'Database', file 'Database' on file 1.
Processed 0 pages for database 'Database', file 'Database_log' on file 1.
RESTORE LOG successfully processed 0 pages in 0.008 seconds (0.000 MB/sec).
Processed 0 pages for database 'Database', file 'Database' on file 1.
Processed 0 pages for database 'Database', file 'Database_log' on file 1.
RESTORE LOG successfully processed 0 pages in 0.007 seconds (0.000 MB/sec).
Processed 0 pages for database 'Database', file 'Database' on file 1.
Processed 0 pages for database 'Database', file 'Database_log' on file 1.
RESTORE LOG successfully processed 0 pages in 0.017 seconds (0.000 MB/sec).
Processed 0 pages for database 'Database', file 'Database' on file 1.
Processed 1 pages for database 'Database', file 'Database_log' on file 1.
RESTORE LOG successfully processed 1 pages in 0.008 seconds (0.793 MB/sec).
Processed 0 pages for database 'Database', file 'Database' on file 1.
Processed 0 pages for database 'Database', file 'Database_log' on file 1.
RESTORE LOG successfully processed 0 pages in 0.007 seconds (0.000 MB/sec).
Processed 0 pages for database 'Database', file 'Database' on file 1.
Processed 0 pages for database 'Database', file 'Database_log' on file 1.
RESTORE LOG successfully processed 0 pages in 0.008 seconds (0.000 MB/sec).
Msg 4326, Level 16, State 1, Line 41
The log in this backup set terminates at LSN 22000000017700001, which is too early to apply to the database. A more recent log backup that includes LSN 22000000020100001 can be restored.
Msg 3013, Level 16, State 1, Line 41
RESTORE LOG is terminating abnormally.
Processed 0 pages for database 'Database', file 'Database' on file 1.
Processed 1 pages for database 'Database', file 'Database_log' on file 1.
RESTORE LOG successfully processed 1 pages in 0.007 seconds (1.116 MB/sec).
Processed 0 pages for database 'Database', file 'Database' on file 1.
Processed 0 pages for database 'Database', file 'Database_log' on file 1.
RESTORE LOG successfully processed 0 pages in 0.007 seconds (0.000 MB/sec).
Processed 0 pages for database 'Database', file 'Database' on file 1.
Processed 0 pages for database 'Database', file 'Database_log' on file 1.
RESTORE LOG successfully processed 0 pages in 0.012 seconds (0.000 MB/sec).
Processed 0 pages for database 'Database', file 'Database' on file 1.
Processed 0 pages for database 'Database', file 'Database_log' on file 1.
RESTORE LOG successfully processed 0 pages in 0.006 seconds (0.000 MB/sec).
Processed 0 pages for database 'Database', file 'Database' on file 1.
Processed 0 pages for database 'Database', file 'Database_log' on file 1.
RESTORE LOG successfully processed 0 pages in 0.007 seconds (0.000 MB/sec).
Processed 0 pages for database 'Database', file 'Database' on file 1.
Processed 0 pages for database 'Database', file 'Database_log' on file 1.
RESTORE LOG successfully processed 0 pages in 0.007 seconds (0.000 MB/sec).
Processed 0 pages for database 'Database', file 'Database' on file 1.
Processed 0 pages for database 'Database', file 'Database_log' on file 1.
RESTORE LOG successfully processed 0 pages in 0.007 seconds (0.000 MB/sec).
Processed 0 pages for database 'Database', file 'Database' on file 1.
Processed 0 pages for database 'Database', file 'Database_log' on file 1.
RESTORE LOG successfully processed 0 pages in 0.007 seconds (0.000 MB/sec).
Processed 0 pages for database 'Database', file 'Database' on file 1.
Processed 0 pages for database 'Database', file 'Database_log' on file 1.
RESTORE LOG successfully processed 0 pages in 0.008 seconds (0.000 MB/sec).
Processed 0 pages for database 'Database', file 'Database' on file 1.
Processed 0 pages for database 'Database', file 'Database_log' on file 1.
RESTORE LOG successfully processed 0 pages in 0.008 seconds (0.000 MB/sec).
Processed 0 pages for database 'Database', file 'Database' on file 1.
Processed 0 pages for database 'Database', file 'Database_log' on file 1.
RESTORE LOG successfully processed 0 pages in 0.007 seconds (0.000 MB/sec).
Processed 0 pages for database 'Database', file 'Database' on file 1.
Processed 0 pages for database 'Database', file 'Database_log' on file 1.
RESTORE LOG successfully processed 0 pages in 0.007 seconds (0.000 MB/sec).
Processed 0 pages for database 'Database', file 'Database' on file 1.
Processed 0 pages for database 'Database', file 'Database_log' on file 1.
RESTORE LOG successfully processed 0 pages in 0.007 seconds (0.000 MB/sec).
Processed 0 pages for database 'Database', file 'Database' on file 1.
Processed 0 pages for database 'Database', file 'Database_log' on file 1.
RESTORE LOG successfully processed 0 pages in 0.006 seconds (0.000 MB/sec).
Processed 0 pages for database 'Database', file 'Database' on file 1.
Processed 0 pages for database 'Database', file 'Database_log' on file 1.
RESTORE LOG successfully processed 0 pages in 0.007 seconds (0.000 MB/sec).
Processed 0 pages for database 'Database', file 'Database' on file 1.
Processed 0 pages for database 'Database', file 'Database_log' on file 1.
RESTORE LOG successfully processed 0 pages in 0.006 seconds (0.000 MB/sec).
Msg 4305, Level 16, State 1, Line 109
The log in this backup set begins at LSN 22000000024600001, which is too recent to apply to the database. An earlier log backup that includes LSN 22000000021700001 can be restored.
Msg 3013, Level 16, State 1, Line 109
RESTORE LOG is terminating abnormally.
Msg 4305, Level 16, State 1, Line 113
The log in this backup set begins at LSN 22000000024600001, which is too recent to apply to the database. An earlier log backup that includes LSN 22000000021700001 can be restored.
Msg 3013, Level 16, State 1, Line 113
RESTORE LOG is terminating abnormally.
Msg 4305, Level 16, State 1, Line 117
The log in this backup set begins at LSN 22000000024600001, which is too recent to apply to the database. An earlier log backup that includes LSN 22000000021700001 can be restored.
Msg 3013, Level 16, State 1, Line 117
RESTORE LOG is terminating abnormally.
Msg 4305, Level 16, State 1, Line 121
The log in this backup set begins at LSN 22000000024600001, which is too recent to apply to the database. An earlier log backup that includes LSN 22000000021700001 can be restored.
Msg 3013, Level 16, State 1, Line 121
RESTORE LOG is terminating abnormally.
Msg 4305, Level 16, State 1, Line 125
The log in this backup set begins at LSN 22000000024600001, which is too recent to apply to the database. An earlier log backup that includes LSN 22000000021700001 can be restored.
Msg 3013, Level 16, State 1, Line 125
RESTORE LOG is terminating abnormally.
Msg 4305, Level 16, State 1, Line 129
The log in this backup set begins at LSN 22000000024600001, which is too recent to apply to the database. An earlier log backup that includes LSN 22000000021700001 can be restored.
Msg 3013, Level 16, State 1, Line 129
RESTORE LOG is terminating abnormally.
Msg 4305, Level 16, State 1, Line 133
The log in this backup set begins at LSN 22000000024600001, which is too recent to apply to the database. An earlier log backup that includes LSN 22000000021700001 can be restored.
Msg 3013, Level 16, State 1, Line 133
RESTORE LOG is terminating abnormally.
Msg 4305, Level 16, State 1, Line 137
The log in this backup set begins at LSN 22000000024600001, which is too recent to apply to the database. An earlier log backup that includes LSN 22000000021700001 can be restored.
Msg 3013, Level 16, State 1, Line 137
RESTORE LOG is terminating abnormally.
Msg 4305, Level 16, State 1, Line 141
The log in this backup set begins at LSN 22000000024600001, which is too recent to apply to the database. An earlier log backup that includes LSN 22000000021700001 can be restored.
Msg 3013, Level 16, State 1, Line 141
RESTORE LOG is terminating abnormally.
Msg 4305, Level 16, State 1, Line 145
The log in this backup set begins at LSN 22000000024600001, which is too recent to apply to the database. An earlier log backup that includes LSN 22000000021700001 can be restored.
Msg 3013, Level 16, State 1, Line 145
RESTORE LOG is terminating abnormally.
Msg 4305, Level 16, State 1, Line 149
The log in this backup set begins at LSN 22000000024600001, which is too recent to apply to the database. An earlier log backup that includes LSN 22000000021700001 can be restored.
Msg 3013, Level 16, State 1, Line 149
RESTORE LOG is terminating abnormally.
Msg 4305, Level 16, State 1, Line 153
The log in this backup set begins at LSN 22000000024600001, which is too recent to apply to the database. An earlier log backup that includes LSN 22000000021700001 can be restored.
Msg 3013, Level 16, State 1, Line 153
RESTORE LOG is terminating abnormally.
Msg 4305, Level 16, State 1, Line 157
The log in this backup set begins at LSN 22000000024600001, which is too recent to apply to the database. An earlier log backup that includes LSN 22000000021700001 can be restored.
Msg 3013, Level 16, State 1, Line 157
RESTORE LOG is terminating abnormally.
Msg 4305, Level 16, State 1, Line 161
The log in this backup set begins at LSN 22000000024600001, which is too recent to apply to the database. An earlier log backup that includes LSN 22000000021700001 can be restored.
Msg 3013, Level 16, State 1, Line 161
RESTORE LOG is terminating abnormally.
Msg 4305, Level 16, State 1, Line 165
The log in this backup set begins at LSN 22000000024600001, which is too recent to apply to the database. An earlier log backup that includes LSN 22000000021700001 can be restored.
Msg 3013, Level 16, State 1, Line 165
RESTORE LOG is terminating abnormally.
Msg 4305, Level 16, State 1, Line 169
The log in this backup set begins at LSN 22000000024600001, which is too recent to apply to the database. An earlier log backup that includes LSN 22000000021700001 can be restored.
Msg 3013, Level 16, State 1, Line 169
RESTORE LOG is terminating abnormally.
Msg 4305, Level 16, State 1, Line 173
The log in this backup set begins at LSN 22000000024600001, which is too recent to apply to the database. An earlier log backup that includes LSN 22000000021700001 can be restored.
Msg 3013, Level 16, State 1, Line 173
RESTORE LOG is terminating abnormally.
Msg 4305, Level 16, State 1, Line 177
The log in this backup set begins at LSN 22000000024600001, which is too recent to apply to the database. An earlier log backup that includes LSN 22000000021700001 can be restored.
Msg 3013, Level 16, State 1, Line 177
RESTORE LOG is terminating abnormally.
Msg 4305, Level 16, State 1, Line 181
The log in this backup set begins at LSN 22000000024600001, which is too recent to apply to the database. An earlier log backup that includes LSN 22000000021700001 can be restored.
Msg 3013, Level 16, State 1, Line 181
RESTORE LOG is terminating abnormally.
Msg 4305, Level 16, State 1, Line 185
The log in this backup set begins at LSN 22000000024800001, which is too recent to apply to the database. An earlier log backup that includes LSN 22000000021700001 can be restored.
Msg 3013, Level 16, State 1, Line 185
RESTORE LOG is terminating abnormally.
Msg 4305, Level 16, State 1, Line 189
The log in this backup set begins at LSN 22000000024800001, which is too recent to apply to the database. An earlier log backup that includes LSN 22000000021700001 can be restored.
Msg 3013, Level 16, State 1, Line 189
RESTORE LOG is terminating abnormally.
June 27, 2011 at 12:24 am
i think i know what is the problem. There is 1 transaction log that is missing which is 1050hrs. I am not sure why the file is missing, is it because during that time i was testing out a restoration and hence cause the log not backing up?
In that case what should i do? i can never get back last friday 1700hrs data? thanks
June 27, 2011 at 3:29 am
I think you are having problem in sequencing the transaction log files. The below query will help you to sequence the same.
SELECT b.physical_device_name,
a.backup_set_id,
Case When a.type = 'D' Then 'Full'
When a.type = 'I' Then 'Differential'
When a.type = 'L' Then 'Transaction Log' End as 'BackupType',
a.name, b.family_sequence_number, a.backup_start_date, a.backup_finish_date
FROM msdb..backupset a
INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
WHERE a.database_name = '<YourDatabaseName>'
ORDER BY a.backup_finish_date, b.family_sequence_number
---------------------------------------------------
Thanks,
Satheesh.
June 27, 2011 at 3:40 am
Erode P. Satheesh (6/27/2011)
I think you are having problem in sequencing the transaction log files. The below query will help you to sequence the same.SELECT b.physical_device_name,
a.backup_set_id,
Case When a.type = 'D' Then 'Full'
When a.type = 'I' Then 'Differential'
When a.type = 'L' Then 'Transaction Log' End as 'BackupType',
a.name, b.family_sequence_number, a.backup_start_date, a.backup_finish_date
FROM msdb..backupset a
INNER JOIN msdb..backupmediafamily b ON a.media_set_id = b.media_set_id
WHERE a.database_name = '<YourDatabaseName>'
ORDER BY a.backup_finish_date, b.family_sequence_number
oh... so i just paste this and change the YourDatabaseName to my database? But i am using Management plan for the schedule backup. Is it ok?
June 27, 2011 at 3:50 am
T-sql, maintenance plan and even if you are using third party tool the information will be stored in these tables.
---------------------------------------------------
Thanks,
Satheesh.
June 27, 2011 at 2:51 pm
Illegal_Operation (6/27/2011)
i think i know what is the problem. There is 1 transaction log that is missing which is 1050hrs. I am not sure why the file is missing, is it because during that time i was testing out a restoration and hence cause the log not backing up?
Partially. The log was backed up and you don't have the file (unless you restored the DB that the log backups are from, if that's the case, this is a lot more complex).
If you can't find the file, you won't be able to restore past that point. Check MSDB, see what the backup was that ran at that time.
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
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply