Log Shipping Errors

Dec 19, 2007

I am trying to set up log shipping in SQL 2005.
The transaction log copy job is failing with the following error.

2007-12-19 11:33:33.93*** Error: Could not retrieve copy settings for secondary ID 'd8d9b7cf-0f36-4446-bdbb-488dfdc1f6fe'.(Microsoft.SqlServer.Management.LogS hipping) ***
2007-12-19 11:33:33.95*** Error: Failed to connect to server SCDSSLSQL2.(Microsoft.SqlServer.ConnectionInfo) ***
2007-12-19 11:33:33.95*** Error: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)(.Net SqlClient Data Provider) ***

Any help would be appreciated.

vkumar

View 3 Replies

Log Shipping Errors

Feb 28, 2008

I have 2 SQL 2005 SP2 machines that i am configuring for log shipping. The primary machine has a large database on it that i want duplicated on another machine. I did a full backup and restore onto machine 2. I configured the log shipping per the BOL and white papers. Everything seams to work correctly except for the restore i get the following error:

The restore operation cannot proceed because the secondary database 'B' is not in NORECOVERY/STANDBY mode.

The jobs are copying the files correctly but i cannot get them restored. I am trying to leave the "B" database readable for reporting but that is all i need it for. Thanks in advance

View 3 Replies View Related

Log Shipping Configuration Errors

May 6, 2015

I am trying to configure log shipping on same server with different instances/I am facing the below error//Cannot open backup device.Operating system error 67(The network name cannot be found)Restore filelist is terminating abnormally (Microsoft sql server Error 3201)

View 11 Replies View Related

Transaction Log Shipping Script Errors

Dec 10, 2007

Greetings:
When I script out my log shipping configuration from the GUI and subsequently drop the log shipping and try to recreate it with the created script, the backup and restore functions do not seem to be working; please see script below. Is there an additional step (or steps) that the SSMS GUI does not output when it creates the script for log shipping? I noticed in the GUI after I run the script that the destination folder for copied files is blank as well.

Example error from backup/restore job - Error: The path is not of a legal form.(mscorlib)

-- Execute the following statements at the Primary to configure Log Shipping

-- for the database [rdevsql2].[SymbolLookUp],

-- The script needs to be run at the Primary in the context of the [msdb] database.

-------------------------------------------------------------------------------------

-- Adding the Log Shipping configuration

-- ****** Begin: Script to be run at Primary: [rdevsql2] ******

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'SymbolLookUp'

,@backup_directory = N'm:ackups'

,@backup_share = N'\rdevsql2m$ackups'

,@backup_job_name = N'LSBackup_SymbolLookUp'

,@backup_retention_period = 60

,@monitor_server = N'RDEVSQL1'

,@monitor_server_security_mode = 1

,@backup_threshold = 60

,@threshold_alert_enabled = 1

,@history_retention_period = 60

,@backup_job_id = @LS_BackupJobId OUTPUT

,@primary_id = @LS_PrimaryId OUTPUT

,@overwrite = 1

,@ignoreremotemonitor = 1

IF (@@ERROR = 0 AND @SP_Add_RetCode = 0)

BEGIN

DECLARE @LS_BackUpScheduleUID As uniqueidentifier

DECLARE @LS_BackUpScheduleID AS int

EXEC msdb.dbo.sp_add_schedule

@schedule_name =N'LSBackupSchedule_rdevsql21'

,@enabled = 1

,@freq_type = 4

,@freq_interval = 1

,@freq_subday_type = 4

,@freq_subday_interval = 1

,@freq_recurrence_factor = 0

,@active_start_date = 20071207

,@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

EXEC msdb.dbo.sp_update_job

@job_id = @LS_BackupJobId

,@enabled = 1

END

EXEC master.dbo.sp_add_log_shipping_primary_secondary

@primary_database = N'SymbolLookUp'

,@secondary_server = N'RDEVSQL1'

,@secondary_database = N'SymbolLookUp'

,@overwrite = 1

-- ****** End: Script to be run at Primary: [rdevsql2] ******

-- ****** Begin: Script to be run at Monitor: [RDEVSQL1] ******

EXEC rdevsql1.msdb.dbo.sp_processlogshippingmonitorprimary

@mode = 1

,@primary_id = N'4d80db8c-e090-4dc0-8af6-d5f5802c4207'

,@primary_server = N'rdevsql2'

,@monitor_server = N'RDEVSQL1'

,@monitor_server_security_mode = 1

,@primary_database = N'SymbolLookUp'

,@backup_threshold = 60

,@threshold_alert = 14420

,@threshold_alert_enabled = 1

,@history_retention_period = 60

-- ****** End: Script to be run at Monitor: [RDEVSQL1] ******

-- Execute the following statements at the Secondary to configure Log Shipping

-- for the database [RDEVSQL1].[SymbolLookUp],

-- the script needs to be run at the Secondary in the context of the [msdb] database.

-------------------------------------------------------------------------------------

-- Adding the Log Shipping configuration

-- ****** Begin: Script to be run at Secondary: [RDEVSQL1] ******

DECLARE @LS_Secondary__CopyJobId AS uniqueidentifier

DECLARE @LS_Secondary__RestoreJobId AS uniqueidentifier

DECLARE @LS_Secondary__SecondaryId AS uniqueidentifier

DECLARE @LS_Add_RetCode As int

EXEC @LS_Add_RetCode = rdevsql1.master.dbo.sp_add_log_shipping_secondary_primary

@primary_server = N'rdevsql2'

,@primary_database = N'SymbolLookUp'

,@backup_source_directory = N'\rdevsql2m$ackups'

,@backup_destination_directory = N''

,@copy_job_name = N''

,@restore_job_name = N''

,@file_retention_period = 4320

,@monitor_server = N'RDEVSQL1'

,@monitor_server_security_mode = 1

,@overwrite = 1

,@copy_job_id = @LS_Secondary__CopyJobId OUTPUT

,@restore_job_id = @LS_Secondary__RestoreJobId OUTPUT

,@secondary_id = @LS_Secondary__SecondaryId OUTPUT

IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)

BEGIN

DECLARE @LS_SecondaryCopyJobScheduleUID As uniqueidentifier

DECLARE @LS_SecondaryCopyJobScheduleID AS int

EXEC rdevsql1.msdb.dbo.sp_add_schedule

@schedule_name =N'DefaultCopyJobSchedule'

,@enabled = 1

,@freq_type = 4

,@freq_interval = 1

,@freq_subday_type = 4

,@freq_subday_interval = 15

,@freq_recurrence_factor = 0

,@active_start_date = 20071207

,@active_end_date = 99991231

,@active_start_time = 0

,@active_end_time = 235900

,@schedule_uid = @LS_SecondaryCopyJobScheduleUID OUTPUT

,@schedule_id = @LS_SecondaryCopyJobScheduleID OUTPUT

EXEC rdevsql1.msdb.dbo.sp_attach_schedule

@job_id = @LS_Secondary__CopyJobId

,@schedule_id = @LS_SecondaryCopyJobScheduleID

DECLARE @LS_SecondaryRestoreJobScheduleUID As uniqueidentifier

DECLARE @LS_SecondaryRestoreJobScheduleID AS int

EXEC rdevsql1.msdb.dbo.sp_add_schedule

@schedule_name =N'DefaultRestoreJobSchedule'

,@enabled = 1

,@freq_type = 4

,@freq_interval = 1

,@freq_subday_type = 4

,@freq_subday_interval = 15

,@freq_recurrence_factor = 0

,@active_start_date = 20071207

,@active_end_date = 99991231

,@active_start_time = 0

,@active_end_time = 235900

,@schedule_uid = @LS_SecondaryRestoreJobScheduleUID OUTPUT

,@schedule_id = @LS_SecondaryRestoreJobScheduleID OUTPUT

EXEC rdevsql1.msdb.dbo.sp_attach_schedule

@job_id = @LS_Secondary__RestoreJobId

,@schedule_id = @LS_SecondaryRestoreJobScheduleID

END

DECLARE @LS_Add_RetCode2 As int

IF (@@ERROR = 0 AND @LS_Add_RetCode = 0)

BEGIN

EXEC @LS_Add_RetCode2 = rdevsql1.master.dbo.sp_add_log_shipping_secondary_database

@secondary_database = N'SymbolLookUp'

,@primary_server = N'rdevsql2'

,@primary_database = N'SymbolLookUp'

,@restore_delay = 0

,@restore_mode = 0

,@disconnect_users = 0

,@restore_threshold = 45

,@threshold_alert_enabled = 1

,@history_retention_period = 60

,@overwrite = 1

END

IF (@@error = 0 AND @LS_Add_RetCode = 0)

BEGIN

EXEC rdevsql1.msdb.dbo.sp_update_job

@job_id = @LS_Secondary__CopyJobId

,@enabled = 1

EXEC rdevsql1.msdb.dbo.sp_update_job

@job_id = @LS_Secondary__RestoreJobId

,@enabled = 1

END

-- ****** End: Script to be run at Secondary: [RDEVSQL1] ******

help is much appreciated,
Derek

View 1 Replies View Related

Log-shipping Monitor Instance Authentication By Proxy Results In Errors

Oct 9, 2007

We have set-up log shipping in both our development and production environments. The difference between the two is that development is using SQL 2005 Developer Edition SP2 and production is using SQL 2005 Enterprise Edition SP2. As well, the production environment runs using 64-bit 3-node failover cluster set-up for the source, whereas the development source server environment is 32-bit and not clustered. Also, our development environment destination/monitor instance is located within the same geographic location mapped to the same domain controller. The production environment destination/monitor instance is located off-site, and although is part of the same domain, uses a different domain controller which is synched-up with the primary domain controller used for the source server and entire development environment. Other than that, both environments run using Windows 2003 Server Enterprise Edition SP1.

Originally, both environments were configured to use Monitor connections "By impersonating the proxy account of the job (usually the SQL Server Agent service account of the server instance where the job runs)". This presented no problems in the development environment, but in the production environment, this results in the following error whenever the source server tries to update the monitor instance with the backup alert status:

Error: 18456, Severity: 14, State: 11.
Login failed for user 'NT AUTHORITYANONYMOUS LOGON'. [CLIENT: XXX.XX.XX.XX]

This also results in the log-ship alert job falsely reporting that backup jobs are "out-of-synch", since the source server cannot write log information to the log ship tables on the destination/monitor instance.

Now, according to BOL, when choosing to impersonate the proxy account, this is supposed to default to the SQL Server Agent Service account, which in our systems (both development and production), is a Windows domain account with full administrator priviledges and a SQL system adminstrator on both source and destination/monitor instances.

Upon trying to open a case with Microsoft originally when we were running on SQL 2005 SP1, and spending several hours ensuring there were no duplicate SPNs and linked servers were properly configured, they had come to the conclusion that this was the result of a known SP1 issue (http://support.microsoft.com/kb/925843), and would be solved by applying SP2. We are now running SP2 + hotfix (9.0.3152), but are still receiving this error.

The only way I have currently of fixing this issue is by changing the Monitor connection from authenticating via proxy account to using a SQL Server login account which has system admin priviledges.

I have limited knowledge of how security is applied across different domain controllers within the same domain. Any help would be greatly appreciated.

View 4 Replies View Related

Log Shipping - Switching Recovery Model In Log Shipping

May 13, 2007

Hi

I could not able to find Forums in regards to 'Log Shipping' thats why posting this question in here. Appriciate if someone can provide me answers depends on their experience.

Can we switch database recovery model when log shipping is turned on ?

We want to switch from Full Recovery to Bulk Logged Recovery to make sure Bulk Insert operations during the after hours load process will have some performance gain.

Is there any possibility of loosing data ?

Thanks

View 1 Replies View Related

Log Shipping: How To Failback After A Failover Log Shipping?

Jun 8, 2006

Hi,

I 'm sure I am missing something obvious, hopefully someone could point it out. After a failover log shipping, I want to fail back to my inital Primary server database; however, my database is marked as loading. How can I mark it as normal?

I did the failover as follow:

I did a failover log shipping from the 2 server Sv1 (Primary) and Sv2 (Secondary) by doing the following

1) Stop the primary database by using sp_change_primary_role (Sv1)

2) Change the 2nd server to primary server by running sp_change_secondary_role (Sv2)

3) Change the monitor role by running sp-change_monitor_role (Sv2)

4) Resolve the log ins - (Sv2)

5) Now I want to fail back - I copy the TRN files to Sv1 - use SQL Ent to restore the database at point in time. The task is done; however, the database is still mark as loading. I could not use sp_dboption.

I appreciate any suggestion.

Thanks in advance

View 5 Replies View Related

SQL Server 2008 :: How To Make Sproc Return Errors For Underlying Table Errors

Jul 1, 2015

I recently updated the datatype of a sproc parameter from bit to tinyint. When I executed the sproc with the updated parameters the sproc appeared to succeed and returned "1 row(s) affected" in the console. However, the update triggered by the sproc did not actually work.

The table column was a bit which only allows 0 or 1 and the sproc was passing a value of 2 so the table was rejecting this value. However, the sproc did not return an error and appeared to return success. So is there a way to configure the database or sproc to return an error message when this type of error occurs?

View 1 Replies View Related

Parent Package Reports Failure On Errors, But No Errors In Log

Jul 31, 2006

I have a parent package that calls child packages inside a For Each container. When I debug/run the parent package (from VS), I get the following error message: Warning: The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

It appears to be failing while executing the child package. However, the logs (via the "progress" tab) for both the parent package and the child package show no errors other than the one listed above (and that shows in the parent package log). The child package appears to validate completely without error (all components are green and no error messages in the log). I turned on SSIS logging to a text file and see nothing in there either.

If I bump up the MaximumErrorCount in the parent package and in the Execute Package Task that calls the child package to 4 (to go one above the error count indicated in the message above), the whole thing executes sucessfully. I don't want to leave the Max Error Count set like this. Is there something I am missing? For example are there errors that do not get logged by default? I get some warnings, do a certain number of warnings equal an error?

Thanks,

Lee

View 5 Replies View Related

How To Solve 0 Allocation Errors And 1 Consistency Errors In

Apr 20, 2006

Starwin writes "when i execute DBCC CHECKDB, DBCC CHECKCATALOG
I reveived the following error.
how to solve it?

Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID -2093955965, index ID 711, page ID (3:2530). The PageId in the page header = (34443:343146507).
. . . .
. . . .

CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID -1635188736)' (object ID -1635188736).
CHECKDB found 0 allocation errors and 1 consistency errors in table '(Object ID -1600811521)' (object ID -1600811521).

. . . .
. . . .

Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID -8748568, index ID 50307, page ID (3:2497). The PageId in the page header = (26707:762626875).
Server: Msg 8909, Level 16, State 1, Line 1 Table error: Object ID -7615284, index ID 35836, page ID (3:2534). The PageId in the page heade"