Scripting the log shipping

AgaAga Posts: 5
edited January 14, 2007 8:31PM in SQL Backup Previous Versions
Is there a way to script the Log Shipping Wizard job? I am particularly interested to know how to restore a full backup to another server. We log ship between 2 different servers and I would like to script the process in case we need to restore all the databases on the destination server.
Thank you!
Aga
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    The log shipping wizard generates SQL Agent jobs to run on the live and standby servers.

    To script the job in SQL Server 2000, select your server in Enterprise Manager, open the node Management\SQL Server Agent\Jobs, select the relevant job on the right pane, right click, select All Tasks\Generate SQL Script.

    If using SQL Server 2005, open your server in Management Studio, open the node SQL Server Agent\Jobs\<your job>, right click, select Script Job as\CREATE to\.

    Note that SQL Backup only creates jobs to backup and restore the transaction log files. The backup and restore of the full backup is done only once, so it's not set up as a recurring job. The processes are performed using the SQL Backup extended stored procedures. You can view samples of the scripts in the Backup Wizard and Restore Wizard, on the last step.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • AgaAga Posts: 5
    Thank you very much for your reply.

    I am particularly interested in scripting the full restore job that is done once (I know how to do the backup). We are shipping logs from box1 to box2 for over 50 dbs. I would like to create a script to restore the db on box2 in case of failure that I could run from box1. I don’t know how to restore the backup on box2 running a script from box1. I would like to have a single script that I could run on box1 that would backup the db and restore it on box2. Is it possible?
    Thank you again! :D
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    You can get a script similar to the full restore job script by using the Restore Wizard on the standby server.

    Copy the full backup file to the standby server. On Step 2 of the Restore Wizard, select the 'Browse to backup files' option, and select that file. Enter the same options you used in the Log Shipping Wizard, and on Step 4 of the Restore Wizard, you can make a copy of the script that's generated.

    One difference is that the Log Shipping Wizard script will have a MOVETO option. You will need to add this manually to the script that is generated by the Restore Wizard.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
Sign In or Register to comment.