backup, copy is working however, restore is not working

  • 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

  • Are you using Log shipping or your own variation of it

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • 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’! **
  • 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.....

  • 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’! **
  • 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" 😉

  • 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

  • Hi Perry on secondary the output is

    YayinSistemi_Data219269000000023400116

    on primary

    YayinSistemi_Data219269000000023400116

  • Akayisi (1/22/2014)


    Hi Perry on secondary the output is

    YayinSistemi_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" 😉

  • Perry Whittle (1/22/2014)


    Akayisi (1/22/2014)


    Hi Perry on secondary the output is

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

  • Akayisi (1/22/2014)


    Perry Whittle (1/22/2014)


    Akayisi (1/22/2014)


    Hi Perry on secondary the output is

    YayinSistemi_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" 😉

  • I'm guessing by the sudden stunned silence you have fixed it 😀

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

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • 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

  • 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