Shipping database to backup server fails
-
Hi,
I am shipping a database backup and restoring it on another server (both running SQLServer 2000 standard) . I have used the technique successfully with transaction logs, but seem to be running into problems with the database backup.
The backup job uses Xcopy to copy the file onto the target server, then calls a stored procedure on the server, sending it the backup filename.
CREATE PROCEDURE dbo.ct_RestoreDatabase
@filename VARCHAR(255)
AS
BEGIN
DECLARE @filepath VARCHAR(255)
SET @filepath= 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\'+@filename
RESTORE DATABASE BackupDB
FROM DISK = @filepath
WITH REPLACE,
MOVE 'BackupDB_Data' TO 'E:\MSSQL\MSSQL\Data\BackupDB_Data.MDF',
MOVE 'BackupDB_Index' TO 'E:\MSSQL\MSSQL\Data\BackupDB_Index_Data.NDF',
MOVE 'BackupDB_Log' TO 'E:\MSSQL\MSSQL\Data\BackupDB_Log.LDF',
STANDBY = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\undo.ldf'
RETURN
END
GO
The step that calls the stored procedure with the filename returns a successful execution, but when I apply the next transaction log, I get the following huge error message.
Executed as user: sa. Cannot associate files with different databases. [SQLSTATE 42000] (Error 5173) Device activation error. The physical file name 'E:\MSSQL\MSSQL\Data\BackupDB_Index_Data.NDF' may be incorrect. [SQLSTATE 42000] (Error 5105) Cannot associate files with different databases. [SQLSTATE 42000] (Error 5173) RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013) File 'BackupDB_Index' was only partially restored by a database or file restore. The entire file must be successfully restored before applying the log. [SQLSTATE 01000] (Error 4320) File 'BackupDB_Data' was only partially restored by a database or file restore. The entire file must be successfully restored before applying the log. [SQLSTATE 01000] (Error 4320) Log file 'E:\MSSQL\MSSQL\Data\BackupDB_Log.LDF' does not match the primary file. It may be from a different database or the log may have been rebuilt previously. [SQLSTATE 01000] (Error 5108). The step failed.
However, if I run the restore from Query Analyser on the backup server, using the same restore database syntax, it successfully restores the database and all the transaction logs subsequently load successfully.
I know one part of the error message above must be the significant one, but which?
David
If it ain't broke, don't fix it...
-
My guess is that you want to use NORECOVERY instead of STANDBY for the database restore, then NORECOVERY for each differential restore, then STANDBY for the log restores.
I do not have loads of experience with log shipping situations, BOL implies that STANDBY is for log restores, with no mention of a use for a database restore.
Andy
-
I thought standby could be used with either, certainly it doesn't cause any problems when I include it in the restore command run from query analyzer. I tried the command using NORECOVERY instead of STANDBY. The database doesn't appear in the list of databases, and the subsequent log restore reports that...
Executed as user: sa. RESTORE LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013) Could not find database ID 65535. Database may not be activated yet or may be in transition. [SQLSTATE 42000] (Error 913). The step failed.
I am just wondering if it is some sort of file access or security issue with the physical files. Why should a command run from QA but not from a stored procedure within the master database? Maybe it is to do with the account I am running the command as, yet the log restores work without a hitch.
David
If it ain't broke, don't fix it...
-
Hi,
STANDBY or NORECOVERY should work.
Try to put a WAITFOR DELAY '00:05:00' between database restore and first log restore. If that should work you can shorten the delay to the smallest possible amount...
regards karl
Best regards
karl
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply