January 22, 2014 at 2:32 am
Hi All,
I have a problem about restore job. It is not working. When i read logs it says;
xxxx.trn failed to open. Operating system error 2(failed to retrieve text for this error. Reason: 15105).
When i tried to find the trn file that sql server is looking for, that trn log is not in the directory where all my trn log files are. Also, According to log files, there is a gap between transaction logs. I tried to restore manually, but it still did not work. Moreover, there is a strange problem here. Because once i run this query which finds the latest trn log that has been restored;
select distinct r.destination_database_name, r.restore_date,
bs.server_name, m.physical_device_name as backup_device, b.physical_name
from msdb.dbo.restorehistory r
inner join msdb.dbo.backupfile b on r.backup_set_id = b.backup_set_id
inner join msdb.dbo.backupset bs on b.backup_set_id = bs.backup_set_id
inner join msdb.dbo.backupmediafamily m on bs.media_set_id = m.media_set_id
where r.destination_database_name='BPSDB'
ORDER BY restore_date DESC
OUTPUT
However, in the directory, date modified is 04:15 for this BPSDB_20140122021505.trn . Thus I could not restore it manually. IDK what is going on ?
FYI, i make databases up to date by restoring from ibm tivoli.
Thanks in advance
January 22, 2014 at 4:26 am
Are you using Log shipping or your own variation of it
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 22, 2014 at 4:31 am
Your post is hard to understand. Could you rewrite it a bit, so we (without knowledge of your system / settings / etc.) can understand?
If you can't find the TRN-file on disk yourself it's obvious the RESTORE command won't find the file either. Therefor the error message doesn't sound strange to me. Could it be someone deleted the file by accident?
January 22, 2014 at 5:01 am
Hanshi my bad, nobody can delete it accidentally.
ok now i want to know this.
Suppose that i have trn files which are 1.trn, 2.trn, 3.trn
I know for sure that last trn files that has been restored was 1.trn. But I tried to restore 2.trn manually, sqlservers says it is too recent to apply blabla......
But, I found out that on log shipping db, i have also backup job which should be working on prod db. I disabled the backup job on log ship db, but it is still not working ...
Any suggestions.....
January 22, 2014 at 5:20 am
What is the info about the LSN's of the TRN-files tat you're trying to restore when you query the tables [backupset] and[backupmediafamily] from the [msdb] database (on the instance where you create the backup)?
January 22, 2014 at 5:30 am
Akayisi (1/22/2014)
Hanshi my bad, nobody can delete it accidentally.ok now i want to know this.
Suppose that i have trn files which are 1.trn, 2.trn, 3.trn
I know for sure that last trn files that has been restored was 1.trn. But I tried to restore 2.trn manually, sqlservers says it is too recent to apply blabla......
But, I found out that on log shipping db, i have also backup job which should be working on prod db. I disabled the backup job on log ship db, but it is still not working ...
Any suggestions.....
Can you post the results of the following query when executed against the primary and secondary databases (change the dbname first)
select name, differential_base_lsn
from sys.master_files
where database_id = db_id('thedb')
and type = 0
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 22, 2014 at 6:41 am
once i run this query
SELECT d.name,
MAX(b.backup_finish_date) AS backup_finish_date
FROM master.sys.sysdatabases d
LEFT OUTER JOIN msdb..backupset b
ON b.database_name = d.name
AND b.type = 'L'
GROUP BY d.name
ORDER BY backup_finish_date DESC
OUTPUT is
name backup_finish_date
x 06:30
After restoring database from IBM tivoli, my last restore date is 15:15 by running this query;
select max(restore_date),destination_database_name from msdb..restorehistory
group by destination_database_name
The question is once i try to run RESTORE job it tries to apply trn log which has been copied at 06:45
Isnt sql server suppose to try to restore TNR logs after 15:15.
Can someone please explain
Thank you
January 22, 2014 at 6:44 am
Hi Perry on secondary the output is
YayinSistemi_Data219269000000023400116
on primary
YayinSistemi_Data219269000000023400116
January 22, 2014 at 7:01 am
Akayisi (1/22/2014)
Hi Perry on secondary the output isYayinSistemi_Data219269000000023400116
on primary
YayinSistemi_Data219269000000023400116
In this case you can fix the missing tran log backups issue by taking a differential backup on the primary and restoring it with NORECOVERY to the secondary This will bring the 2 in step and log restores should be successful. Do this before a full backup occurs on the primary
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 22, 2014 at 7:04 am
Perry Whittle (1/22/2014)
Akayisi (1/22/2014)
Hi Perry on secondary the output isYayinSistemi_Data219269000000023400116
on primary
YayinSistemi_Data219269000000023400116
In this case you can fix the missing tran log backups issue by taking a differential backup on the primary and restoring it with NORECOVERY to the secondary This will bring the 2 in step and log restores should be successful. Do this before a full backup occurs on the primary
perry is there any way to fix it besides taking full backup of primary?
January 22, 2014 at 7:08 am
Akayisi (1/22/2014)
Perry Whittle (1/22/2014)
Akayisi (1/22/2014)
Hi Perry on secondary the output isYayinSistemi_Data219269000000023400116
on primary
YayinSistemi_Data219269000000023400116
In this case you can fix the missing tran log backups issue by taking a differential backup on the primary and restoring it with NORECOVERY to the secondary This will bring the 2 in step and log restores should be successful. Do this before a full backup occurs on the primary
perry is there any way to fix it besides taking full backup of primary?
as i said above, immediately take a differential backup of the primary and apply to the secondary with NORECOVERY. Do this before a full backup occurs on the primary
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 22, 2014 at 7:45 am
I'm guessing by the sudden stunned silence you have fixed it 😀
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 22, 2014 at 7:57 am
haha Yeah that was a solution i was thinking about. bUt during work-day i did not want to blow up the our network. Yeah it is working by the way lol
January 22, 2014 at 8:19 am
Akayisi (1/22/2014)
during work-day i did not want to blow up the our network. Yeah it is working by the way lol
The diff generally wouldnt be too large to do that
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply