none
What should I give instead of null to restore 2 databases only?? RRS feed

  • Question

  • Hi,
     
    exec bts_ConfigureBizTalkLogShipping @nvcDescription = '<MyLogShippingSolution>',
        @nvcMgmtDatabaseName = 'BizTalkMgmtDb',
        @nvcMgmtServerName = 'SQL2\INS3',
       @SourceServerName = null, -- null indicates that this destination server restores all databases
       @fLinkServers = 1 -- 1 automatically links the server to the management database

    Instead of giving null to the parameter @SourceServerName (which restore all databases), I would like to restore only to databases called SSODB & BizTalkRulesengine. What should I give instead of null to restore these 2 databases only??

    and these 2 databases are not on SQL2\INS3 instance. They are on SQL2\INS1..

    thanks

    Monday, September 21, 2009 3:27 PM

Answers

All replies

  • From MSDN (http://msdn.microsoft.com/en-us/library/aa560961(BTS.10).aspx):

    If you have more than one source server, you can restore each source server to its own destination server. On each destination server, in the @SourceServerName = null parameter, replace null with the name of the appropriate source server, surrounded by single quotes (for example, @SourceServerName = 'MySourceServer',).

    So, you run the sp twice with the server Name = your source server and SSODB, BizTalkRuleEngine as the DB name argument.   
    Monday, September 21, 2009 4:09 PM
  • Hi ALex,


    I have already mention the source server instance in @nvcMgmtServerName = 'SQL2\INS3'.
    and again I have to give @SourceServerName = 'MySourceServer' as
                                         @SourceServerName = 'SQL2\INS3'  ??

    Monday, September 21, 2009 6:29 PM
  • @nvcMgmtServerName is your destination server, the one you are copying to
    @SourceServerName is the server you are copying from
    Monday, September 21, 2009 6:34 PM
  • So, it looks like SQL2\INS1 to me.  You know your environment better.
    Monday, September 21, 2009 6:35 PM
  • Hi Alex,

    Here is the complete scenario:

    SOURCE:

    We have one BizTalk application Server and have 3 SQL server instances on same server for BizTalk Application on Source\Production as below:

    SQL1\INS1 has BizTalkMsgBoxDb database

    SQL1\INS2 has BizTalkDTADb database

    SQL1\INS3 has BizTalkMgmtDb and SSODB databases

    SQL1\INS2 has the built-in backup job Backup BizTalk Server (BizTalkMgmtDb) and have enabled it & configured the parameters for the job steps and it backing up all the databases from all the 3 SQL instances.

    DESTINATION:

    We have one BizTalk application Server and have 3 SQL server instances on same server on Destination\Stand-by same as Source Source\Production as below:

    SQL2\INS1 has BizTalkMsgBoxDb database

    SQL2\INS2 has BizTalkDTADb database

    SQL2\INS3 has BizTalkMgmtDb and SSODB databases

    Next step, I’m configuring BizTalk Logshipping. For this I did the following:

    On Destination:

    STEP1:I have Installed 3 SQL server instances of Server 2005.

    SQL2\INS1

    SQL2\INS2

    SQL2\INS3

    STEP2:Installed BizTalk server 2006 R2 and it created databases on all of the 3 instances same as in Source Server (Primary/Live SQL Sevrer)

    STEP3: Connected to SQL2\INS3 and executed the scripts 1)LogShipping_Destination_Schema.sql & 2)LogShipping_Destination_Logic.sql

    STEP4:Connected to SQL2\INS3 & executed the below script:

    exec bts_ConfigureBizTalkLogShipping @nvcDescription = ”,
    @nvcMgmtDatabaseName = ‘BizTalkMgmtDb’,
    @nvcMgmtServerName = ‘SQL1\INS3′, (why SQL1\INS3? because it is the instance where Backup job is there on Source SQL Server)
    @SourceServerName = null, — null indicates that this destination server restores all databases
    @fLinkServers = 1 — 1 automatically links the server to the management database

    here all the 4 databases are restoring to SQL2\INS3 and in other 2 instances SQL2\INS1 & SQL2\INS2 nothing is happening. But I want to have as below:

    1. SQL1\INS1 has BizTalkMsgBoxDb database and it should restore to SQL2\INS1

    2.SQL1\INS2 has BizTalkDTADb database and it should restore to SQL2\INS2

    3.SQL1\INS3 has BizTalkMgmtDb and SSODB databases and it should restore to SQL2\INS3.

    To acheive the above, what should I change in the below script and Do I need to run all the 3 scripts 1)LogShipping_Destination_Schema.sql & 2)LogShipping_Destination_Logic.sql
    in other 2 instances, SQL2\INS1 & SQL2\INS2 too

    exec bts_ConfigureBizTalkLogShipping @nvcDescription = ”,
    @nvcMgmtDatabaseName = ‘BizTalkMgmtDb’,
    @nvcMgmtServerName = ‘SQL1\INS3′, (why SQL1\INS3? because it is the instance where Backup job is there on Source SQL Server)
    @SourceServerName = null, — null indicates that this destination server restores all databases
    @fLinkServers = 1 — 1 automatically links the server to the management database

    what should I change in below to lines instaead of @SourceServerName = null & @fLinkServers = 1

    @SourceServerName = null, — null indicates that this destination server restores all databases
    @fLinkServers = 1 — 1 automatically links the server to the management database

     

    So please advice..



    Monday, September 21, 2009 7:14 PM
  • I'd say:

    Run it 4 times with the following arguments (DBName, ServerName, SourceServerName):
     
    BizTalkMsgBoxDb, SQL\INS1, SQL\INS1
    BizTalkDTADb, SQL\INS2, SQL\INS2
    BizTalkMgmtDb, SQL\INS3, SQL\INS3
    SSODB, SQL\INS3, SQL\INS3
    • Proposed as answer by Alex Krotov Tuesday, September 22, 2009 5:08 PM
    Monday, September 21, 2009 7:25 PM
  • Did it work?
    • Proposed as answer by Alex Krotov Tuesday, September 22, 2009 5:08 PM
    Monday, September 21, 2009 9:43 PM
  • Do you want me to run the below 3 scripts in each Stand-by SQL instance as below:

    1)

    STEP3: Connected to SQL2\INS3 and executed the scripts 1)LogShipping_Destination_Schema.sql & 2)LogShipping_Destination_Logic.sql

    STEP4:Connected to SQL2\INS3 & executed the below script:

    exec bts_ConfigureBizTalkLogShipping @nvcDescription = 'Logshipping',
    @nvcMgmtDatabaseName = ‘BizTalkMgmtDb’,
    @nvcMgmtServerName = ‘SQL2\INS3′,
    @SourceServerName = SQL2\INS3, — null indicates that this destination server restores all databases
    @fLinkServers = 1 — 1 automatically links the server to the management database

     

    2)

    STEP3: Connected to SQL2\INS2 and executed the scripts 1)LogShipping_Destination_Schema.sql & 2)LogShipping_Destination_Logic.sql

    STEP4:Connected to SQL2\INS2 & executed the below script:

    exec bts_ConfigureBizTalkLogShipping @nvcDescription = 'Logshipping',
    @nvcMgmtDatabaseName = ‘SSODB’,
    @nvcMgmtServerName = ‘SQL1\INS3′,
    @SourceServerName = SQL1\INS3, — null indicates that this destination server restores all databases
    @fLinkServers = 1 — 1 automatically links the server to the management database

     

    3)

    STEP3: Connected to SQL2\INS1 and executed the scripts 1)LogShipping_Destination_Schema.sql & 2)LogShipping_Destination_Logic.sql

    STEP4:Connected to SQL2\INS1 & executed the below script:

    exec bts_ConfigureBizTalkLogShipping @nvcDescription = 'Logshipping',
    @nvcMgmtDatabaseName = ‘BizTalkMsgBoxDb’,
    @nvcMgmtServerName = ‘SQL1\INS1′,
    @SourceServerName = SQL1\INS1, — null indicates that this destination server restores all databases
    @fLinkServers = 1 — 1 automatically links the server to the management database

     

    4)

    STEP3: Connected to SQL2\INS2 and executed the scripts 1)LogShipping_Destination_Schema.sql & 2)LogShipping_Destination_Logic.sql

    STEP4:Connected to SQL2\INS2 & executed the below script:

    exec bts_ConfigureBizTalkLogShipping @nvcDescription = 'Logshipping',
    @nvcMgmtDatabaseName = ‘BizTalkMsgBoxDb’,
    @nvcMgmtServerName = ‘SQL1\INS2′,
    @SourceServerName = SQL1\INS2, — null indicates that this destination server restores all databases
    @fLinkServers = 1 — 1 automatically links the server to the management database

     

    But this variable always asking for BizTalkmanagement database name right?

    @nvcMgmtDatabaseName = ‘BizTalkMgmtDb’,

     

    • Proposed as answer by Alex Krotov Tuesday, September 22, 2009 5:07 PM
    Monday, September 21, 2009 11:36 PM
  • Hi Alex,
    The above process worked....

    thanks
    Tuesday, September 22, 2009 5:50 AM
  • Glad it was helpful.  Please marked as Answered.
    • Marked as answer by Garyreeds Tuesday, September 22, 2009 5:31 PM
    Tuesday, September 22, 2009 5:08 PM
  • Hello Gari/ Alex,

    I am in the same boat as you. In my case, we have 2 sql instances (Messagebox database on 1 instance and rest of the biztalk databases on the 2nd  instance) and I have to setup a disaster recovery (logshipping) environment that has 2 instances similar to production setup. I have gone through your steps above...you have set the values for parameters @nvcMgmtServerName, @SourceServerName the same (for example @nvcMgmtServerName = ‘SQL1\INS2′,@SourceServerName = SQL1\INS2) which I couldn't understand. Can you please clarify?

    Monday, October 14, 2013 11:28 PM