locked
Restoring TRN files in SQL Server RRS feed

  • Question

  • Hi Team,

    I am trying to prepare a T-SQL to restore .trn files.

    Part of this i am tryging to create a SQL JOB which can pick the .trn files from given location and restore to DB in recovery mode.

    Please provide the T-sql for this?




    • Edited by VijayKSQL Tuesday, August 21, 2018 11:06 PM add
    Tuesday, August 21, 2018 7:03 PM

Answers

  • Hi S.Vijay Kumar,

    It is necessary to provide TRN name in the T-sql statements otherwise it will not run successfully.


    Best Regards
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by VijayKSQL Monday, September 17, 2018 5:31 PM
    Monday, August 27, 2018 1:10 AM

All replies

  • Hi S.Vijay Kumar,

    According to your description, my understanding is that you want to use T-sql to create a SQL server job in order to restore the TRN files from given location. If anything is misunderstood, please tell me.

    Here is the T-sql statements.

    --Create job
    DECLARE @jobid uniqueidentifier
    EXEC msdb.dbo.sp_add_job
            @job_name = N'job name',
            @job_id = @jobid OUTPUT
     
    --Create job steps
    DECLARE @sql nvarchar(400),@dbname sysname, @path nvarchar(260),@fname nvarchar(100)
    
    set @fname=''PRODUCE_''+convert(varchar(10),getdate(),112)+''_l.trn'' --specify file name
    set @path=dbo.f_getdbpath(null)+@fname --Set file location
    
    SELECT @dbname=DB_NAME()
    
    set @sql='restore log '+@dbname
     +' from disk= '+@path
     +' with  NORECOVERY '
    
    
    EXEC msdb.dbo.sp_add_jobstep
            @job_id = @jobid,
            @step_name = N'Job name',
            @subsystem = 'TSQL', --Step Type
            @database_name=@dbname,
            @command = @sql
    
    --Create schedule
    EXEC msdb..sp_add_jobschedule
            @job_id = @jobid,
            @name = N'Schedule name',
            @freq_type=4,                -- Occurs daily
            @freq_interval=1,            -- Recurs daily 
            @freq_subday_interval=1,     -- Occurs every hour
            @active_start_date = NULL,   -- Start date
            @active_end_date = 99991231, -- End date
            @active_start_time = 00000,  -- Start time
            @active_end_time = 235959    -- End time

    Best Regards,
    Puzzle

     




    • Edited by Puzzle_Chen Friday, August 24, 2018 6:30 AM
    Wednesday, August 22, 2018 8:36 AM
  • What you are talking about is built in functionality call "Log Shipping".  It does all the work for you.

    I suggest you read this:

    https://docs.microsoft.com/en-us/sql/database-engine/log-shipping/about-log-shipping-sql-server?view=sql-server-2017

    Wednesday, August 22, 2018 12:51 PM
  • Excellent.

    When i run this code getting below errors:

    Msg 102, Level 15, State 1, Line 10
    Incorrect syntax near 'PRODUCE_'.
    Msg 156, Level 15, State 1, Line 15
    Incorrect syntax near the keyword 'set'.

    Having some doubts.

    1. How to modify above script to restore .trn on signle database (Given Database)?

    2. Where can i put .trn files location?

    example: E:\Logs

    3. In the above script what is: _I from ''_l.trn''

    Thursday, August 23, 2018 5:44 AM
  • Hi S.Vijay Kumar,

    If you want to restore .trn on given database, you could modify these part in the T-sql statement.
    set @sql='restore log'+@dbname    --You can specify a signle database by change @dbname
     +' from '+@path
     +' with  NORECOVERY

    The location of .trn files has no special needs. You can specify it by yourself.

    And in the script, ‘_1’ is just the serial number of my TRN file. It has no special meaning too. You can change it according to your TRN file name.

    Best Regards,
    Puzzle
    Thursday, August 23, 2018 7:16 AM
  • Hi When i run this query:

    --Create job
    DECLARE @jobid uniqueidentifier
    EXEC msdb.dbo.sp_add_job
            @job_name = N'job name',
            @job_id = @jobid OUTPUT
     
    --Create job steps
    DECLARE @sql nvarchar(400),@dbname sysname, @path nvarchar(260),@fname nvarchar(100)
    
    set @fname=''PRODUCE_''+convert(varchar(10),getdate(),112)+''_.trn''
    set @path=dbo.f_getdbpath(null)+'g:\ag'
    
    SELECT @dbname=DB_NAME(),
    
    set @sql='restore log'+'TestDB'
     +' from '+@path
     +' with  NORECOVERY '
    
    
    EXEC msdb.dbo.sp_add_jobstep
            @job_id = @jobid,
            @step_name = N'Job name',
            @subsystem = 'TSQL', --Step Type
            @database_name=@dbname,
            @command = @sql
    
    --Create schedule
    EXEC msdb..sp_add_jobschedule
            @job_id = @jobid,
            @name = N'Schedule name',
            @freq_type=4,                -- Occurs daily
            @freq_interval=1,            -- Recurs daily 
            @freq_subday_interval=1,     -- Occurs every hour
            @active_start_date = NULL,   -- Start date
            @active_end_date = 99991231, -- End date
            @active_start_time = 00000,  -- Start time
            @active_end_time = 235959    -- End time

    I am getting below error:

    Msg 102, Level 15, State 1, Line 10
    Incorrect syntax near 'PRODUCE_'.
    Msg 156, Level 15, State 1, Line 15
    Incorrect syntax near the keyword 'set'.

    Thursday, August 23, 2018 7:58 AM
  • Hi S.Vijay Kumar,

    You can modify your T-sql in this way.
    set @fname='PRODUCE_'+convert(varchar(10),getdate(),112)+'_.trn'
    set @path='g:\ag'+@fname         --The path would include your TRN file name
    
    set @sql='restore log'+'TestDB'  -- If you specify a single database, than you could delete the SELECT statement
     +' from '+@path
     +' with  NORECOVERY '

    Best Regards,
    Puzzle
    Thursday, August 23, 2018 8:59 AM
  • Hi Chen,

    Now i am able to create JOB. But while i running the job getting below error:

    Date 8/23/2018 11:17:59 AM
    Log Job History (job name)

    Step ID 1
    Server SERVER01
    Job Name job name
    Step Name SQL_TRN_Restore
    Duration 00:00:00
    Sql Severity 15
    Sql Message ID 319
    Operator Emailed
    Operator Net sent
    Operator Paged
    Retries Attempted 0

    Message
    Executed as user: USER01\svcMsSqlserv. Incorrect syntax near 'G:'. [SQLSTATE 42000] (Error 102)  Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon. [SQLSTATE 42000] (Error 319).  The step failed.

    Here the current t-sql in JOB:

    restore log ASPP from G:\ASPP\TRN\PRODUCE_20180823_.trn  with  NORECOVERY


    Thursday, August 23, 2018 5:21 PM
  • Hi S.Vijay Kumar,

    You can modify the T-sql in JOB in this way. It’s my bad to forget the keyword ‘DISK’.

    restore log ASPP from disk='G:\ASPP\TRN\PRODUCE_20180823_.trn' with NORECOVERY


    Best Regards,
    Puzzle

    Friday, August 24, 2018 3:15 AM
  • Hi Chen,

    This is the Code i modified in Job:

    restore log ASPP  from disk='G:\ASPP\TRN\PRODUCE_20180823_.trn' with NORECOVERY

    But getting below error while executing SQL Job

    Cannot open backup device 'G:\ASPP\TRN\PRODUCE_20180823_.trn'. Operating system error 2(The system cannot find the file specified.). [SQLSTATE 42000] (Error 3201)  RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013).  The step failed.

    I am sure that i am able access this path. I don't know why it giving the error

    Friday, August 24, 2018 5:36 AM
  • Hi Chen,

    After above error what is found is if given command like,

    restore log ASPP  from disk='G:\ASPP\TRN\ASPP_20180823104501.trn' with NORECOVERY

    It means that your query is not restoring .TRN files dynamically.

    Friday, August 24, 2018 5:43 AM
  • Hi S.Vijay Kumar,

    It seems that the system cannot find the TRN file. You would specify your TRN file name in the T-sql statements. And all the transaction logs must be restored in chronological order.

    Best Regards,
    Puzzle

    Friday, August 24, 2018 5:48 AM
  • Hi Chen,

    I just tested now until unless giving the full TRN file name like,

    restore log ASPP  from disk='G:\ASPP\TRN\ASPP_20180823104501.trn' with NORECOVERY

    The Job is failing.


    • Edited by VijayKSQL Friday, August 24, 2018 5:57 AM add
    Friday, August 24, 2018 5:53 AM
  • Hi S.Vijay Kumar,

    What's the error message now?
    Friday, August 24, 2018 5:57 AM
  • Now Job ran successfully

    Because i given complete TRN name like below,

    restore log ASPP  from disk='G:\ASPP\TRN\ASPP_20180823104501.trn' with NORECOVERY



    • Edited by VijayKSQL Friday, August 24, 2018 6:01 AM add
    Friday, August 24, 2018 6:00 AM
  • Hi S.Vijay Kumar,

    I am glad to know that your job ran successfully.

    If our solutions are helpful to you, please kindly mark them as answers. By doing so,  it will benefit all community members who are facing this similar issues. Your contribution is highly appreciated

    Best Regards,
    Puzzle

    Friday, August 24, 2018 6:19 AM
  • Hi Chen,

    Job ran successfully. But is we have to provide complete TRN name, other wise it is not restore dynamically.

    Friday, August 24, 2018 3:39 PM
  • Hi S.Vijay Kumar,

    It is necessary to provide TRN name in the T-sql statements otherwise it will not run successfully.


    Best Regards
    Puzzle
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by VijayKSQL Monday, September 17, 2018 5:31 PM
    Monday, August 27, 2018 1:10 AM