Sign in
Microsoft.com
 
Resources for IT Professionals
 
 
 
Resources for IT Professionals > Forums Home > BizTalk Server Forums > BizTalk Server General > Backup Biztalk Server Job not working (linked server)
Ask a questionAsk a question
Search Forums:
 

AnswerBackup Biztalk Server Job not working (linked server)

  • Tuesday, November 03, 2009 9:44 PMBarrakoda Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     
    Sign In to Vote
    0
    Sign In to Vote
    I have a BizTalk Server 2006 R2 installation which is housed on one sole machine (named EDICORE1), which performs the IIS and Database tasks necessary for it to function.

    Everything is working great with this setup except for today when I wanted to enable the DB backup.

    I followed the instructions at http://msdn.microsoft.com/en-us/library/aa546765%28BTS.20%29.aspx

    I struggled with permission problems for a while but eventually got it (I think).

    When I run the job, step 1 works successfully [I think because I edited one of the stored procs, SP_BackupAllFull, which I've since un-done**], but step 2 gives an error.

    BackupFull = Executed as user EDICORE1\biztalkbkp. The step succeeded.

    MarkAndBackupLog = Executed as user EDICORE1\biztalkbkp. Could not connect to linked server 'EDICORE1' (OLE DB Provider 'SQLNCLI'). Enable delegation or use a remote SQL Server login for the current user. [SQLSTATE 42000](Error 7413). The step failed.

    I don't understand why its trying to connect to itself as a linked server?? So I tried to create the linked server using various settings, but I still cant get it to work.

    The other thing that I'm curious about, the backup file (EDICORE1_BizTalkMgmtDb_Full_BTS_2009_11_03_14_11_47_243.bak) step 1 created was just under 300mb. However, the MDF + LDF files for all the BizTalk<etc> DBs tally up to over 6 GB. I'm worried this backup is not full. (Together the BizTalkMgmtDb.mdf/ldf are only 55mb..)

    ** Edited sp_backupAllFull, then un-did it
    exec

 bts_SafeAddLinkedServer @BackupServer
    


set

 @tsql = '['

 + @BackupServer + '].['

 + @BackupDB + '].[dbo].[sp_BackupBizTalkFull]'



    --Replaced the above with this:<br/>



set

 @tsql = '[dbo].[sp_BackupBizTalkFull]'

    The following error appears in the Application Event Log.
    --UNRELATED ERROR REMOVED BY B.K.

    • Edited byBarrakoda Wednesday, November 04, 2009 3:09 PM
    •  
     

Answers

  • Wednesday, November 04, 2009 7:45 PMBarrakoda Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     Answer
    Sign In to Vote
    0
    Sign In to Vote
    I changed the owner of the backup job to be a system admin account and it works fine now. (After creating the linked server with sp_addlinkedserver and then setting the RPC option with:


    exec sp_serveroption 'EDICORE1','rpc out','ON'
    exec sp_serveroption 'EDICORE1','rpc','ON'


    Some of the other stored procs I did edit, basically just left the [databasename].[dbo].[sp_whatever], but running under the admin account fixed all the permission problems for the linked server and backup jobs.
    • Marked As Answer byBarrakoda Wednesday, November 04, 2009 7:45 PM
    •  
     

All Replies

  • Wednesday, November 04, 2009 4:29 AMvinay_kotagiri_hotmail.com Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     
    Sign In to Vote
    0
    Sign In to Vote
    Hi ,

    I faced a similar problem with BizTalk 2009, when all BizTalk Databases installed on one SQL Server.

    BizTalk will add a linked server to the same SQL Server.

    All the BizTalk SQL Job Steps are execcuted through Stored Procedures,

    Open the Stored Proc. that is throwing the error and remove the Server reference inside the Stored Proc. code, eg. remove the bold part from the code below, that should work.

    set
     @tsql = '['
     + @BackupServer + '].    ['
     + @BackupDB + '].[dbo].[sp_BackupBizTalkFull]'


    Other way is to resolve the linked server issue.


    vinay

     
  • Wednesday, November 04, 2009 3:09 PMBarrakoda Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     
    Sign In to Vote
    0
    Sign In to Vote
    I did that, which resolved the issue for step 1, I didn't think this was the right way to go about fixing the problem.

    I will dig down into the other stored procs for step 1 and 2 to find the linked server references and remove them.
     
  • Wednesday, November 04, 2009 7:45 PMBarrakoda Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     Answer
    Sign In to Vote
    0
    Sign In to Vote
    I changed the owner of the backup job to be a system admin account and it works fine now. (After creating the linked server with sp_addlinkedserver and then setting the RPC option with:


    exec sp_serveroption 'EDICORE1','rpc out','ON'
    exec sp_serveroption 'EDICORE1','rpc','ON'


    Some of the other stored procs I did edit, basically just left the [databasename].[dbo].[sp_whatever], but running under the admin account fixed all the permission problems for the linked server and backup jobs.
    • Marked As Answer byBarrakoda Wednesday, November 04, 2009 7:45 PM
    •