SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


backup, copy is working however, restore is not working


backup, copy is working however, restore is not working

Author
Message
Akayisi
Akayisi
SSC-Enthusiastic
SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)

Group: General Forum Members
Points: 121 Visits: 198
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
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56865 Visits: 17747
Are you using Log shipping or your own variation of it

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8984 Visits: 3718
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?

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Akayisi
Akayisi
SSC-Enthusiastic
SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)

Group: General Forum Members
Points: 121 Visits: 198
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.....
HanShi
HanShi
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8984 Visits: 3718
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)?

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56865 Visits: 17747
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" ;-)
Akayisi
Akayisi
SSC-Enthusiastic
SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)

Group: General Forum Members
Points: 121 Visits: 198
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
Akayisi
Akayisi
SSC-Enthusiastic
SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)

Group: General Forum Members
Points: 121 Visits: 198
Hi Perry on secondary the output is

YayinSistemi_Data 219269000000023400116

on primary

YayinSistemi_Data 219269000000023400116
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)SSC Guru (56K reputation)

Group: General Forum Members
Points: 56865 Visits: 17747
Akayisi (1/22/2014)
Hi Perry on secondary the output is

YayinSistemi_Data 219269000000023400116

on primary

YayinSistemi_Data 219269000000023400116

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" ;-)
Akayisi
Akayisi
SSC-Enthusiastic
SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)SSC-Enthusiastic (121 reputation)

Group: General Forum Members
Points: 121 Visits: 198
Perry Whittle (1/22/2014)
Akayisi (1/22/2014)
Hi Perry on secondary the output is

YayinSistemi_Data 219269000000023400116

on primary

YayinSistemi_Data 219269000000023400116

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?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search