August 5, 2011 at 5:31 pm
Hi,
Actually i'm deploying a logshipping strategy using sql 2008 std r2 64 bits.
Log shipping allows me to automatically send transaction log backups from a primary database on a primary server instance to one or more secondary databases on separate secondary server instances. The transaction log backups are applied to each of the secondary databases individually. The monitor server which would be in this case the primary server, will record the history and status of backup and restore operations and, optionally, raise alerts if these operations fail to occur as scheduled.
I run this script in my primary server, but i returns me this error message:
Error message:
Msg 32053, Level 16, État 1, Procedure sp_MSprocesslogshippingjob, Line 31
The server name, gave by'@@servername', has a Null value.
The testlogship database does'nt exist as a principal logshipping server.
Runned script:
/* INSTALLATION LOG SHIPPING FOR PRIMARY DATA BASE*/
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'testlogship'
,@backup_directory = N'C:\Backup\testlogship_LogShipping'
,@backup_share = N'C:\Backup\testlogship_LogShipping'
,@backup_job_name = N'LSBackup_testlogship'
,@backup_retention_period = 10080 --In minutes = 1 week
,@monitor_server = N'MONITORSERVER\SQL2008'
,@monitor_server_security_mode = 1
,@backup_threshold = 60
,@threshold_alert_enabled = 1
,@history_retention_period = 10080 --In minutess = 1 week
,@backup_job_id = @LS_BackupJobId OUTPUT
,@primary_id = @LS_PrimaryId OUTPUT
,@overwrite = 1
/* the stocked procedure has created 2 Job. Now we have to add schedules*/
IF (@@ERROR = 0 AND @SP_Add_RetCode = 0)
BEGIN
DECLARE @LS_BackUpScheduleUID As uniqueidentifier
DECLARE @LS_BackUpScheduleID AS int
-- Schedule for the backup task
EXEC msdb.dbo.sp_add_schedule
@schedule_name =N'LSBackupSchedule_testlogship'
,@enabled = 1
,@freq_type = 4
,@freq_interval = 1
,@freq_subday_type = 4
,@freq_subday_interval = 30 --Delai in minutes between backups
,@freq_recurrence_factor = 0
,@active_start_date = 20090519
,@active_end_date = 99991231
,@active_start_time = 0
,@active_end_time = 235900
,@schedule_uid = @LS_BackUpScheduleUID OUTPUT
,@schedule_id = @LS_BackUpScheduleID OUTPUT
EXEC msdb.dbo.sp_attach_schedule
@job_id = @LS_BackupJobId
,@schedule_id = @LS_BackUpScheduleID
END
--Add the secondary DB in primary server
EXEC master.dbo.sp_add_log_shipping_primary_secondary
@primary_database = N'testlogship'
, @secondary_server = N'SECONDARYSERVER\SQL2008'
, @secondary_database = N'testlogship'
/* END onf script */
If a run this same script in a lab server, with the same sql 2008 std r2 64 bits, it works perfectly. But when i try it in my production server, i gives me the message error above.
Any ideas about what could it be.
Thanks a lot for helping.
Pablo
August 6, 2011 at 12:11 pm
Hi Pablo,
Error :
The testlogship database does'nt exist as a principal logshipping server.
The above error say database testlogship does not exist.
You have mentioned this script does not run on production server.
Please check if you have DB name testlogship on your production SQL server.
Thanks,
Shree
August 6, 2011 at 1:43 pm
Thanks for helping Shree. Yes, the DB was attached in the production server.
I've resolve the problem. The error message was because the production server instance has been renamed.
What i did is run the command:
SELECT @@SERVERNAME
GO
and it returned NUll value. I trying to find out more about the procedure (sys.sp_MSprocesslogshippingjob) and i realize that when you drop dow the server and you forgot to give a value , it returns error message when installing loggshipping.
a part of the code in the procedure sys.sp_MSprocesslogshippingjob:
if @@servername is null
028 begin
029 raiserror(32053, 16, 1)
030 return 1
031 end
The command to drop down the server we've use was:
EXEC sp_dropserver 'old instance'
GO
EXEC sp_addserver 'new instance'
GO
But the 'local' command was missing. I re-run it with the local command this time:
EXEC sp_dropserver 'old instance'
GO
EXEC sp_addserver 'new instance', 'local'
GO
I run again the script in my production server and it works perfect.
What i've learned about all this staff is when you change the intances names or change the name of your local server, be sure to use the sp_addserver with 'local' command to make sure that a value is put in your server name. In this case, my new instance name.
thanks again
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply