November 1, 2010 at 10:25 pm
I have created a script to do the log backup for multiple databases instead of using the log-shipping wizzard. This is just so i have less number of jobs when I open the SQL Agent. Just one job that does the log-backup with the COPYTO option (Using Redgate) to handle the copy function. Another script is also written out in the target or secondary server. Basically, it is log-shipping in action. However, using this method, the tables in msdb that is used for log shipping is not populated. (log_shipping_primary_databases)
Is that by design, or is there a way that msdb is aware of the log-shipping eventhough the scripts were not from the wizard?
Thanks a lot!!!
November 2, 2010 at 5:48 am
SqlN00bie (11/1/2010)
I have created a script to do the log backup for multiple databases instead of using the log-shipping wizzard. This is just so i have less number of jobs when I open the SQL Agent. Just one job that does the log-backup with the COPYTO option (Using Redgate) to handle the copy function. Another script is also written out in the target or secondary server. Basically, it is log-shipping in action. However, using this method, the tables in msdb that is used for log shipping is not populated. (log_shipping_primary_databases)Is that by design, or is there a way that msdb is aware of the log-shipping eventhough the scripts were not from the wizard?
Thanks a lot!!!
I think I'm right in assuming that all you have done is just create a set of T-SQL scripts that backup and restore databases.
A log shipping configuration is created\monitored by using the system stored procedures below (Do the scripts contain any of these SP's???)
sp_add_log_shipping_alert_job
sp_add_log_shipping_primary_database
sp_add_log_shipping_primary_secondary
sp_add_log_shipping_secondary_database
sp_add_log_shipping_secondary_primary
sp_change_log_shipping_primary_database
sp_change_log_shipping_secondary_database
sp_change_log_shipping_secondary_primary
sp_cleanup_log_shipping_history
sp_delete_log_shipping_alert_job
sp_delete_log_shipping_primary_database
sp_delete_log_shipping_primary_secondary
sp_delete_log_shipping_secondary_database
sp_delete_log_shipping_secondary_primary
sp_help_log_shipping_alert_job
sp_help_log_shipping_monitor_primary
sp_help_log_shipping_monitor_secondary
sp_help_log_shipping_primary_database
sp_help_log_shipping_primary_secondary
sp_help_log_shipping_secondary_database
sp_help_log_shipping_secondary_primary
sp_refresh_log_shipping_monitor
sp_resolve_logins
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 2, 2010 at 8:05 am
Perry, you are absolutely right in your assumptions.
All i did was really write TSQL scripts that mimics the actions of the log-shipping. None of the scripts used was using any of the SP's you have listed. And I honestly am not sure where to incorporate it.
With master.dbo.sp_add_log_shipping_primary_database, it creates a backup job that is disabled, and it seems like it is done per database, and with that it can create multiple backup job, or can I use it, and supply a common backup job name for all the databases that participates in log-shipping?
Consequently, i should be able to use master.dbo.sp_add_log_shipping_primary_secondary right?
November 2, 2010 at 9:04 am
From BOL
To enable log shipping
➡ Initialize the secondary database by restoring a full backup of the primary database on the secondary server.
➡ 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.
➡ On the primary server, execute sp_add_jobschedule to add a schedule for the backup job using.
➡ On the monitor server, execute sp_add_log_shipping_alert_job to add the alert job.
➡ On the primary server, enable the backup job.
➡ On the secondary server, execute sp_add_log_shipping_secondary_primary supplying the details of the primary server and database. This stored procedure returns the secondary ID and the copy and restore job IDs.
➡ On the secondary server, execute sp_add_jobschedule to set the schedule for the copy and restore jobs.
➡ On the secondary server, execute sp_add_log_shipping_secondary_database to add a secondary database.
➡ On the primary server, execute sp_add_log_shipping_primary_secondary to add the required information about the new secondary database to the primary server.
➡ On the secondary server, enable the copy and restore jobs.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply