asked on

Error when restoring a backup of transaction log in a linked server

hi,

i am trying to restore a transaction log backup file to a linked server. this is in connection with the simple log shipper in the resource kit cd of ms sql 2000. error is:

Backing up ServiceCenterMapped to DISK=\\sydsql1\sydsql1log\\ServiceCenterMapped_20050708_1554_LOG.BAK
BACKUP LOG successfully processed 0 pages in 0.108 seconds (0.000 MB/sec).
Execute [SYDSQL1].master.dbo.sp_ApplyStandByLog N'ServiceCenterMapped',N'\\sydsql1\sydsql1log\\ServiceCenterMapped_20050708_1554_LOG.BAK',N'e:\sydsql1log\undo_servicecentermapped.dat'
Executing RESTORE LOG ServiceCenterMapped FROM DISK='\\sydsql1\sydsql1log\\ServiceCenterMapped_20050708_1554_LOG.BAK' WITH STANDBY='e:\sydsql1log\undo_servicecentermapped.dat
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Server: Msg 2754, Level 16, State 1, Line 1
Error severity levels greater than 18 can only be specified by members of the sysadmin role, using the WITH LOG option.
Server: Msg 50000, Level 19, State 1, Procedure sp_ShipLog, Line 86
sp_ShipLog Error 2754 occurred on database ServiceCenterMapped to server SYDSQL1 using backup file \\sydsql1\sydsql1log\\ServiceCenterMapped_20050708_1554_LOG.BAK

any ideas?

regards.

ann

wala_lang

ASKER

but when i execute the restore command in the actual server (sydsql1) it works fine. i cant seem to execute it remotely. i have linked the server to the primary server and even set up a domain account that will be used by the 2 servers. i know that the id is working because i can see the list of tables of the database from the linked server.

arbert

make sure that you're not using the remotedatabase when you use the linked server.

I would avoid the crappy implementation of Logshipping from the resource CD and configure it yourself. This is a very good link on configuring it manually:

http://www.sql-server-performance.com/sql_server_log_shipping.asp

wala_lang

ASKER

thanks for the links. i have learned a lot from them. just a bit of a question though. based on the article i have to run a job that will backup the DATABASE. from what i've been reading on log shipping, i thought i should just:

1. create an INITIAL backup/restore of my database in the primary server to my secondary server. and i only have to do it once.
2. create a scheduled job (every 5 minutes, as recommended by MS site) that will create a backup of the transaction log, store that log backup in the secondary server and restore that file in the secondary server. this will go on forever until its time to failover to the secondary server.

am i right? or do i need to restore DB backup too? dont i need to synchronize them at the very first time that i set up my secondary server? but i need to do it just once?

ann

ASKER CERTIFIED SOLUTION

arbert

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

wala_lang

ASKER

thanks! that certainly puts my mind at ease.
thanks for all your time.

ann