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, 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. The folder locations (destination and source) are assumed to already exist so you’d need to take care of that in your code somehow. 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). Part 1 – runs on the primary server Part 2 – runs on the secondary server Part 3 – runs on the monitoring server 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. Obviously this isn’t a full solution to your needs but hopefully it will be enough to get you started. PART 1 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> 2) Make sure the owner of the tran log backup job (created by step 1) is sa. 3) Specify that the database is going to be log shipped, using the plan_id obtained in step 1, above: EXEC msdb..sp_add_log_shipping_database @db_name = ‘<enter db Name>’, @maintenance_plan_id = ‘BF90BFE4-E7C8-4F6F-84A1-8E5A9150B8A3’ –example plan_id 4) Define the log shipping monitor by executing the following procedure: EXEC msdb..sp_define_log_shipping_monitor @monitor_name = ‘<enter server name for monitoring server>’, @logon_type = 1, — 1=NT Logon, 2=SQL Logon @delete_existing = 1 –delete existing users? –PART 2: THE SECONDARY COMPONENT: (all of this takes place on the secondary server) 5) Create a log shipping plan with the following procedure: EXEC msdb..sp_add_log_shipping_plan @plan_name = ‘<name of the plan>’, @description = null, @source_server = ‘<enter name of source server>’, @source_dir = ‘<enter UNC path pointing to tran log backup files on source>’, @destination_dir = ‘<enter LOCAL path for destination of backup files>’, @history_retention_period = 2880, –number of minutes to retain history @file_retention_period = 2880, — number of minutes to retain files @copy_frequency = 5, –minutes @restore_frequency = 5 — minutes 6) Obtain the plan_id for the above log shipping plan from msdb..log_shipping_plans. 7) Define a log shipping plan database by executing the following procedure, using the plan_id obtained in step 6, above: EXEC msdb..sp_add_log_shipping_plan_database @plan_id = ‘&lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />lan_id obtained in step 6>’, @plan_name = ‘&lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />lan name used in step 5>’, @source_database = ‘<name of source database>’, @destination_database = ‘<name of destination database>’, @load_delay = 1, @load_all = 1, @copy_enabled = 1, @load_enabled = 1, @recover_db = 0, @terminate_users = 1 <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. 9) Define the log shipping monitor by executing the following procedure: EXEC msdb..sp_define_log_shipping_monitor @monitor_name = ‘<monitor server>’, — must be the same monitoring server as defined on the primary server, in step 4 @logon_type = 1, — 1=NT Logon, 2=SQL Logon @delete_existing = 1 PART3: THE MONITORING COMPONENT: (all of this takes place on the monitoring server as defined in step 4 and 7, above) 10) Define the log_shipping_primary server using the following procedure: EXEC msdb..sp_add_log_shipping_primary @primary_server_name = ‘<name of primary server>’, @primary_database_name = ‘&lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />rimary database name>’, @maintenance_plan_id = ‘BF90BFE4-E7C8-4F6F-84A1-8E5A9150B8A3’, — plan_id for the tran log maintenance plan obtained in step 1 @backup_threshold = 30, –minutes @threshold_alert = 14420, –error number for RAISERROR @threshold_alert_enabled = 1 11)Obtain the primary_id, created above, from msdb..log_shipping_primaries. 12)Define the log_shipping_secondary using the following procedure, using the primary_id obtained from step 11: EXEC msdb..sp_add_log_shipping_secondary @primary_id = 576, — primary_id obtained from step 12 @secondary_server_name = ‘<secondary server name>’, @secondary_database_name = ‘<secondary database name>’, @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 @copy_enabled = 1, @load_enabled = 1, @out_of_sync_threshold = 45, –minutes @threshold_alert = 14421, –error number for RAISERROR @threshold_alert_enabled = 1 Hope that helps, Karl Grambow www.sqldbcontrol.com

]]>