RESTORE LOG from different location being denied
-
I'm implementing the SimpleLogShipper found in the SQL2K resource kit. Right now I'm just doing some testing replicating Pubs from one server to another; unfortunately the two databases are in different locations on the servers. No problem, right? Just use "WITH MOVE". Not working.
Command:
RESTORE LOG pubs
FROM DISK='\\SERVER2\LogShipping\pubs_20040903_1403_LOG.BAK'
WITH STANDBY='E:\LogShipping\LogShip_Undo.dat',
MOVE 'pubs' TO 'E:\Program Files\Microsoft SQL Server\MSSQL\data\pubs.mdf',
MOVE 'pubs_log' TO 'E:\Program Files\Microsoft SQL Server\MSSQL\data\pubs_log.ldf'
(the files are currently under the same path, but on D: )
Error message:
Server: Msg 4330, Level 16, State 4, Line 1
The log in this backup set cannot be applied because it is on a recovery path inconsistent with the database.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.
Like I said, you'd think that WITH MOVE would take care of this, but is there something I'm doing wrong here??
Thanks for responding...
-
Did you change the location of the data/log files on the originating server between the time you performed the initial full database backup and the subsequent log backups?
SQL BAK Explorer - read SQL Server backup file details without SQL Server.
Supports backup files created with SQL Server 2005 up to SQL Server 2017. -
That's a good point. I'll make sure I applied the correct full backup too
-
If you are restoring the logs you shouldn't specify the mdf file or the MOVE command. The move is only done for the Full Backup restore. Also I have the 'undo' file as a .ldf file rather than a .dat file but that probably isn't important. Below is the restore proc I got from an article on this site.
CREATE PROCEDURE sp_restore_log_backups
@database as varchar(50),
@backup_file as varchar(150)
AS
declare @undo_file varchar(150)
declare @Physical_log_file as varchar(150)
declare @logical_log_file as varchar(150)
set @undo_file = 'c:\Program Files\Microsoft SQL Server\MSSQL\backup\undo_' + @database + '.ldf'
set @physical_log_file = 'c:\Program Files\Microsoft SQL Server\MSSQL\data\' + @database + '_log.ldf'
set @logical_log_file = @database + '_log'
RESTORE log @database
FROM DISK = @backup_file
WITH
STANDBY = @undo_file,
MOVE @logical_log_file TO @physical_log_file
WAITFOR DELAY '00:00:05'
EXEC sp_dboption @database, 'single user', true
GO
The proc for the full restore is:
CREATE PROCEDURE sp_restore_database_backups
@database as varchar(50),
@backup_file as varchar(150)
AS
declare @undo_file varchar(150)
declare @physical_data_file as varchar(150), @Physical_log_file as varchar(150)
declare @logical_data_file as varchar(150), @logical_log_file as varchar(150)
set @undo_file = 'c:\Program Files\Microsoft SQL Server\MSSQL\backup\undo_' + @database + '.ldf'
set @physical_data_file = 'c:\Program Files\Microsoft SQL Server\MSSQL\data\' + @database + '_data.mdf'
set @physical_log_file = 'c:\Program Files\Microsoft SQL Server\MSSQL\data\' + @database + '_log.ldf'
set @logical_data_file = @database + '_data'
set @logical_log_file = @database + '_log'
RESTORE DATABASE @database
FROM DISK = @backup_file
WITH
REPLACE,
STANDBY = @undo_file,
MOVE @logical_data_file TO @physical_data_file,
MOVE @logical_log_file TO @physical_log_file
WAITFOR DELAY '00:00:05'
EXEC sp_dboption @database, 'single user', true
GO
Setting the database to 'single user' is just because it is a warm start server and isn't used for anything else. Also the restore fails if there are any active connections to the database when you start the retore, so setting the database to single user helps (I think).
Hope this helps
Peter
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply