======Configuring Primary======
Be sure that both primary and target destination have access to the shared location which you will use for the backup. Alternatively, you can backup directly on the target server in case you have a space:
=====Disable Already existing Backup=====
If you have any scheduled backup either via job or maintanence plan, you have to disable them before adding other jobs on the primary which will require the transaction log.
=====Create a schedule=====
Schedule can be created in two ways:
* Using GUI - Too boring to show.
* Using the command line - Not boring to show :)
This will create a schedule which can run jobs each 5 minutes daily:
EXEC sp_add_schedule
@schedule_name = N'Every5Minutes' ,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=5,
@active_start_time=000000,
@active_end_time=235959 ;
=====Create jobs=====
Once we have the schedule, we have to create and add jobs to it as follows:
DECLARE @LS_BackupJobId AS uniqueidentifier
DECLARE @LS_PrimaryId AS uniqueidentifier
DECLARE @SP_Add_RetCode As int
EXEC @SP_Add_RetCode = master.dbo.sp_add_log_shipping_primary_database
@database = N'%{database_name}'
,@backup_directory = N'\\shared_server_location_ip\path\%{database_name}'
,@backup_share = N'\\shared_server_location_ip\path\%{database_name}'
,@backup_job_name = N'LSBackup_%{database_name}'
,@backup_retention_period = 4320
,@backup_threshold = 60
,@threshold_alert_enabled = 1
,@history_retention_period = 5760
,@backup_job_id = @LS_BackupJobId OUTPUT
,@primary_id = @LS_PrimaryId OUTPUT
,@overwrite = 1
IF (@@ERROR = 0 AND @SP_Add_RetCode = 0)
BEGIN
EXEC msdb.dbo.sp_attach_schedule
@job_id = @LS_BackupJobId
,@schedule_name = N'Every5Minutes' <- Schedule we just created.
EXEC msdb.dbo.sp_update_job
@job_id = @LS_BackupJobId
,@enabled = 1
END
EXEC master.dbo.sp_add_log_shipping_alert_job
EXEC master.dbo.sp_add_log_shipping_primary_secondary
@primary_database = N'%{database_name}'
,@secondary_server = N'secondary_server_hostname'
,@secondary_database = N'%{database_name}'
,@overwrite = 1
GO
xp_cmdshell N'mkdir \\shared_server_location_ip\path\%{database_name}', NO_OUTPUT
GO
=====Perform initial backup=====
The backup will be done on shared location OR on the target server(in case you are rich and have money for storage :) ) Since we are poor people, let's do it on shared storage:
BACKUP DATABASE [%{database_name}] TO DISK= N'\\shared_server_location_ip\path\%{database_name}\%{database_name}.bak' WITH COMPRESSION
====Remove of the configuration====
If for any reason you mistake the configuration, you can remove it as follows:
sp_delete_log_shipping_primary_database [ @secondary_database = ] 'tainops'