none
Configure Log shipping Between 2 servers as Disaster Recovery Solution (DR)

    Question

  • Hi All,

    Description :

    We have One server with 2 instances with 75 DB's.All the Databases size is 135 GB.We would like to setup  new Disaster Recovery Server for this existing sever.So i need to build new server as DR Server and install SQL SERVER like Primary server.I need to take full of backup of all Primary server , need to restore full backups in DR Server . Once initial Synchronization is completed  i need to configure LogShipping as DR Solution. So  For this we are going  to configure Logshipping as DR Solution between servers.

    Problem :

    I need to configure Log shipping for all 75 Db's in primary server to all 75 db's in secondary server.

    I dont want to configure logshipping for all these 75 db's by using SSMS. I want to configure logshipping By using Scripts.

    My Question :

    We want to use logshipping as DR Recovery between primary server and secondary server.

    1. I want single script for taking Tlog Backup of all these 75 DB's in primary server.

    2. I want single script for copying all these Tlog backup files to secondary server (DR Server) ?

    3. I want single script for restore all these TLOG Backup files in secondary server (Newly DR Server)

    4. I want one more script for alerts like restore is completed successfully.

    Could you please provide me the Scripts ASAP.As I want to configure logshipping using Scripts only

    Regards,

    Venkat S THOTA
    Sunday, September 01, 2013 11:18 AM

Answers

All replies

  • Hi ,

    First, we could back up all the databases in one instance to the shared folder, and then restore the files to the secondary server(The same steps for the other instance).To get each database from instance, we could use cursor to select record from sys.databases, and specify the backup directory for storing the backup files. Second, we could restore the backup files to the other server by using sys.xp_cmdshell to get each “.bak” file from the shared folder. For the detailed, please refer to the first two articles.

    After the above procedure finished, we could using logshipping to configure the corresponding backup jobs, restore jobs and alert jobs in scripts. The general  configure logshipping steps are as following: 
    1. Initialize the secondary database by restoring a full backup of the primary database on the secondary server. (For this part, please refer to the first two articles)
    2. On the primary server, execute sp_add_log_shipping_primary_database to add a primary database. The stored procedure returns the backup job ID and primary ID.
    3. On the primary server, execute sp_add_jobschedule to add a schedule for the backup job.
    4. On the monitor server, execute sp_add_log_shipping_alert_job to add the alert job. Please refer to the articles below.

    For the detailed procedures, please refer to the articles below.
     
    Simple script to backup all SQL Server databases
    http://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/
    Auto generate SQL Server restore script from backup files in a directory
    http://www.mssqltips.com/sqlservertip/1584/auto-generate-sql-server-restore-script-from-backup-files-in-a-directory/
    Configure Log Shipping (SQL Server)
    http://technet.microsoft.com/en-us/library/ms190640.aspx
    Scripting Log Shipping Automation
    http://stackoverflow.com/questions/4127668/scripting-log-shipping-automation

    Thanks,
    Candy Zhou

    Tuesday, September 03, 2013 6:56 AM
  • Hi All,

    Description :

    We have One server with 2 instances with 75 DB's.All the Databases size is 135 GB.We would like to setup  new Disaster Recovery Server for this existing sever.So i need to build new server as DR Server and install SQL SERVER like Primary server.I need to take full of backup of all Primary server , need to restore full backups in DR Server . Once initial Synchronization is completed  i need to configure LogShipping as DR Solution. So  For this we are going  to configure Logshipping as DR Solution between servers.

    Problem :

    I need to configure Log shipping for all 75 Db's in primary server to all 75 db's in secondary server.

    I dont want to configure logshipping for all these 75 db's by using SSMS. I want to configure logshipping By using Scripts.

    My Questions :

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

    We want to configure logshipping as DR Recovery between primary server and secondary server by using Scripts only

    Q1). I want single script for taking Tlog Backup of all these 75 DB's in primary server?

    Q2). I want single script for copying all these Tlog backup files to secondary server (DR Server) ?

    Q3). I want single script for restore all these TLOG Backup files in secondary server (Newly DR Server) ?

    Q4). I want one more script for alerts like restore is completed successfully?

    Could you please provide me the Scripts ASAP.As I want to configure logshipping using Scripts only

    Regards,

    Venkat S THOTA

     

    Tuesday, September 03, 2013 6:58 AM
  • Could you please provide me the Scripts ASAP.

    Hello Venkat,

    Do you really expect that we do your work and that "ASAP"? If you have an issue with one of the task, feel free to come back with your questions.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Tuesday, September 03, 2013 7:15 AM
  • To add to Olaf ..

    In this forum lot of people put their time and effort to help each other.So please appropriate their effort  

    Any way there is no way you can do what ever you asking for using single script..  Please read ..

    http://technet.microsoft.com/en-us/library/ms190640.aspx#TsqlProcedure 

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Tuesday, September 03, 2013 7:26 AM
  • Hello Moderators

    Please merge the below thread to this one both are same.

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/a10c10c3-507e-4397-8ed2-cdefac47a250/configure-log-shipping-between-2-servers-as-disaster-recovery-solution-dr

    Hello Venkat,

    I suppose Candy gave you some links in above thread but seems you did not had time to go through these.


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers


    • Edited by Shanky_621 Tuesday, September 03, 2013 8:50 AM typo
    Tuesday, September 03, 2013 8:50 AM
  • Hi All,

    Description :

    We have One server with 2 instances with 75 DB's.All the Databases size is 135 GB.We would like to setup  new Disaster Recovery Server for this existing sever.So i need to build new server as DR Server and install SQL SERVER like Primary server.I need to take full of backup of all Primary server , need to restore full backups in DR Server .

    Once initial Synchronization is completed  I need to configure LogShipping as DR Solution. So  For this we are going  to configure Logshipping as DR Solution between servers.

    The schedule time is 4 hours.That means For every 4 hours the TLOG Backups should be taken,copied and Restored in secondary server(Using scripts only)

    Problem :

    I need to configure Log shipping for all 75 Db's in primary server to all 75 db's in secondary server.

    I have the requirement to  configure logshipping for all these 75 db's by using Scripts only. I am not supposed to configure logshipping By using SSMS for all these 75 db's.

    My Questions :

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

    We want to configure logshipping as DR Recovery between primary server and secondary server by using Scripts only

    Q1). I want single script for taking Tlog Backup of all these 75 DB's in primary server for every 4 hours .(Schedule Time : For every 4 hours Tlog Backups of all DB's should be taken to shared folder or local folder by using single Script )?

    Q2). I want single script for copying all these Tlog backup files to secondary server (DR Server)(By using this script the TLOG Backup files should be copied to seconadry server for every 4 hours) ?

    Q3). I want single script for restore all these TLOG Backup files in secondary server (Newly DR Server)(For every 4 hours , the TLOG Backups should be restored in secondary server) ?

    Q4). I want one more script for alerts like restore is completed successfully?

    Could you please provide me the Scripts as I want to configure logshipping using Scripts only

    Regards,

    Venkat


    Venkat Thota

    Thursday, September 05, 2013 5:28 AM
  • here is your perfect solution.

    http://sirsql.net/blog/2012/6/19/speedy-log-shipping-setup.html


    Ramesh Babu Vavilla MCTS,MSBI

    Thursday, September 05, 2013 5:45 AM
  •  

    Hi All,

    Description :

    We have One server with 2 instances with 75 DB's.All the Databases size is 135 GB.We would like to setup new Disaster Recovery Server for this existing sever.So I need to build new server as DR Server in some other location and install SQL SERVER like Primary server.I need to take full backup of all Primary server DB's , need to restore full backups in DR Server . Once initial Synchronization is completed I need to configure LogShipping as DR Solution. So For this we are going to configure Logshipping as DR Solution between servers.

    The schedule time is 4 hours.That means For every 4 hours the TLOG Backups should be taken,copied and Restored in secondary server(Using scripts only)

    Problem :

    I need to configure Log shipping for all 75 Db's in primary server to all 75 db's in secondary server.

    I have the requirement to configure logshipping for all these 75 db's by using Scripts only. I am not supposed to configure logshipping By using SSMS for all these 75 db's.

    My Questions :

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

    We want to configure logshipping as DR Recovery between primary server and secondary server by using Scripts only

    Q1). I want single script for taking Tlog Backup of all these 75 DB's in primary server for every 4 hours .(Schedule Time : For every 4 hours Tlog Backups of all DB's should be taken to shared folder or local folder by using single Script )?

    Q2). I want single script for copying all these Tlog backup files to secondary server (DR Server)(By using this script the TLOG Backup files should be copied to seconadry server for every 4 hours) ?

    Q3). I want single script for restore all these TLOG Backup files in secondary server (Newly DR Server)(For every 4 hours , the TLOG Backups should be restored in secondary server) ?

    Q4). I want one more script for alerts like restore is completed successfully?

    Q5) Can we do this activity by using SQLCMD mode in sqlserver ?

    Could you please provide me the Scripts as I want to configure logshipping using Scripts only

    Regards,

    Subrahmanyam

    Thursday, September 05, 2013 6:40 AM
  • Thursday, September 05, 2013 6:43 AM
  • Hi Venkat,

    You post the same question 3 times.Please avoid this practice on Forum, I  have merged the other threads  into this thread.

    Thanks for you understanding.


    Sofiya Li
    TechNet Community Support

    Friday, September 06, 2013 2:22 AM
    Moderator