Log shipping all databases (automating setup) | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Log shipping all databases (automating setup)

Hi Setting up log-shipping can only be done at 1 database at a time. Is there any way of automating the Wizard in SS to setup it up for all databases on a server to another server using VB, c#, vbs, wmi, DMO or SMO ? Am I missing something real easy or doing sommin wrong ? Regards
Hi,<br /><br />I imagine this is possible using T-SQL. I’ll outline the steps you need to perform to set up log shipping using T-SQL. And then I guess it’s a case of iterating through each database.<br /><br />The folder locations (destination and source) are assumed to already exist so you’d need to take care of that in your code somehow.<br /><br />There are three parts to this method, each of which run on a different server (unless the monitoring server happens to be the same as the primary or secondary for some reason).<br /><br />Part 1 – runs on the primary server<br />Part 2 – runs on the secondary server<br />Part 3 – runs on the monitoring server<br /><br />You’d have to figure out how to manage running each portion of code on the different servers but at first glance I’d suggest that a .net app could handle this pretty easily. You’d need to check whether the database you’re planning to log ship is in full recovery mode – and no doubt there are various other checks you’d need to perform.<br /><br />Obviously this isn’t a full solution to your needs but hopefully it will be enough to get you started.<br /><br /><font face="Verdana"><font size="1"><br /><font color="blue">PART 1</font id="blue"><br />1) Set up a transaction log backup job maint plan. See xp_sqlmaint and sqlmaint.exe. Here’s an article that might help:<a target="_blank" href=http://www.fawcette.com/Archives/premier/mgznarch/vbpj/2001/10oct01/sqlpro0110/jf0110/jf0110-1.asp>http://www.fawcette.com/Archives/premier/mgznarch/vbpj/2001/10oct01/sqlpro0110/jf0110/jf0110-1.asp</a><br /><br />2) Make sure the owner of the tran log backup job (created by step 1) is sa.<br /><br />3) Specify that the database is going to be log shipped, using the plan_id obtained in step 1, above:<br /><br />EXEC msdb..sp_add_log_shipping_database<br />@db_name = ‘&lt;enter db Name&gt;’,<br />@maintenance_plan_id = ‘BF90BFE4-E7C8-4F6F-84A1-8E5A9150B8A3’ –example plan_id<br /><br />4) Define the log shipping monitor by executing the following procedure:<br /><br />EXEC msdb..sp_define_log_shipping_monitor <br />@monitor_name = ‘&lt;enter server name for monitoring server&gt;’,<br />@logon_type = 1, — 1=NT Logon, 2=SQL Logon<br />@delete_existing = 1 –delete existing users?<br /><br />–<font color="blue">PART 2: THE SECONDARY COMPONENT: (all of this takes place on the secondary server)</font id="blue"><br /><br />5) Create a log shipping plan with the following procedure:<br /><br />EXEC msdb..sp_add_log_shipping_plan<br />@plan_name = ‘&lt;name of the plan&gt;’,<br />@description = null,<br />@source_server = ‘&lt;enter name of source server&gt;’,<br />@source_dir = ‘&lt;enter UNC path pointing to tran log backup files on source&gt;’, <br />@destination_dir = ‘&lt;enter LOCAL path for destination of backup files&gt;’,<br />@history_retention_period = 2880, –number of minutes to retain history <br />@file_retention_period = 2880, — number of minutes to retain files <br /> @copy_frequency = 5, –minutes<br />@restore_frequency = 5 — minutes<br /><br /><br />6) Obtain the plan_id for the above log shipping plan from msdb..log_shipping_plans.<br /><br />7) Define a log shipping plan database by executing the following procedure, using the plan_id obtained in step 6, above:<br /><br />EXEC msdb..sp_add_log_shipping_plan_database<br />@plan_id = ‘&lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />lan_id obtained in step 6&gt;’,<br />@plan_name = ‘&lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />lan name used in step 5&gt;’,<br />@source_database = ‘&lt;name of source database&gt;’,<br />@destination_database = ‘&lt;name of destination database&gt;’,<br />@load_delay = 1,<br />@load_all = 1,<br />@copy_enabled = 1,<br />@load_enabled = 1,<br />@recover_db = 0,<br />@terminate_users = 1<br /><br /><img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> Change the owner of the copy and restore jobs (created by the above step) to sa.<br /><br />9) Define the log shipping monitor by executing the following procedure:<br /><br />EXEC msdb..sp_define_log_shipping_monitor <br />@monitor_name = ‘&lt;monitor server&gt;’, — must be the same monitoring server as defined on the primary server, in step 4<br />@logon_type = 1, — 1=NT Logon, 2=SQL Logon<br />@delete_existing = 1<br /><br /><font color="blue">PART3: THE MONITORING COMPONENT: (all of this takes place on the monitoring server as defined in step 4 and 7, above)</font id="blue"><br /><br />10) Define the log_shipping_primary server using the following procedure:<br /><br />EXEC msdb..sp_add_log_shipping_primary<br />@primary_server_name = ‘&lt;name of primary server&gt;’,<br />@primary_database_name = ‘&lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />rimary database name&gt;’,<br />@maintenance_plan_id = ‘BF90BFE4-E7C8-4F6F-84A1-8E5A9150B8A3’, — plan_id for the tran log maintenance plan obtained in step 1<br />@backup_threshold = 30, –minutes<br />@threshold_alert = 14420, –error number for RAISERROR<br />@threshold_alert_enabled = 1<br /><br />11)Obtain the primary_id, created above, from msdb..log_shipping_primaries.<br /><br />12)Define the log_shipping_secondary using the following procedure, using the primary_id obtained from step 11:<br /><br />EXEC msdb..sp_add_log_shipping_secondary<br />@primary_id = 576, — primary_id obtained from step 12<br />@secondary_server_name = ‘&lt;secondary server name&gt;’,<br />@secondary_database_name = ‘&lt;secondary database name&gt;’,<br />@secondary_plan_id = ‘2C67497E-2B8B-497F-BFC9-59FAFEDF3826’, –the plan_id from msdb..log_shipping_plans on the secondary server obtained in step 6, above<br />@copy_enabled = 1,<br />@load_enabled = 1,<br />@out_of_sync_threshold = 45, –minutes<br />@threshold_alert = 14421, –error number for RAISERROR<br />@threshold_alert_enabled = 1</font id="size1"></font id="Verdana"><br /><br />Hope that helps,<br /><br /><br />Karl Grambow<br /><br />www.sqldbcontrol.com