Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

backup, copy is working however, restore is not working Expand / Collapse
Author
Message
Posted Wednesday, January 22, 2014 2:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 12:21 AM
Points: 41, Visits: 194
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
Post #1533513
Posted Wednesday, January 22, 2014 4:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 6,626, Visits: 14,197
Are you using Log shipping or your own variation of it

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

"Ya can't make an omelette without breaking just a few eggs"
Post #1533547
Posted Wednesday, January 22, 2014 4:31 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:18 AM
Points: 2,448, Visits: 2,988
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’! **
Post #1533550
Posted Wednesday, January 22, 2014 5:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 12:21 AM
Points: 41, Visits: 194
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.....
Post #1533565
Posted Wednesday, January 22, 2014 5:20 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:18 AM
Points: 2,448, Visits: 2,988
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’! **
Post #1533568
Posted Wednesday, January 22, 2014 5:30 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 6,626, Visits: 14,197
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"
Post #1533573
Posted Wednesday, January 22, 2014 6:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 12:21 AM
Points: 41, Visits: 194
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


Post #1533596
Posted Wednesday, January 22, 2014 6:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 12:21 AM
Points: 41, Visits: 194
Hi Perry on secondary the output is

YayinSistemi_Data 219269000000023400116

on primary

YayinSistemi_Data 219269000000023400116
Post #1533601
Posted Wednesday, January 22, 2014 7:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:33 PM
Points: 6,626, Visits: 14,197
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"
Post #1533610
Posted Wednesday, January 22, 2014 7:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 12:21 AM
Points: 41, Visits: 194
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?
Post #1533611
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse