CODEWITHSUNDEEP
t-sqldatabase-restorelog-shipping
Aeden Maddok
Aeden Maddok

Reputation: 1

The database is already fully recovered. RESTORE DATABASE is terminating abnormally

I've recently inherited the responsibilities of a DBA without the benefit of having any DBA training. We have a database (omitted name for security reasons) that is essentially setup to run in Standby mode to be used as a read-only database for secondary systems. This database is updated by log shipping from a vendor of ours so we essentially have an almost up-to-date copy of their database at all times.

However, over the weekend the automated process started failing on step 5 of the procedure. We are getting the error message "The database is already fully recovered. [SQLSTATE 42000] (Error 3153) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed."

I need help to understand what is failing and how I can fix it. Any help you can provide would be greatly appreciated. Below is a script of the automated job broken out step-by-step.

/*  Step 1 - Map Network Drive */
BEGIN
    EXEC xp_cmdshell 'net use /persistent:no';
    
    EXEC xp_cmdshell 'net use * /d /Y';
    
    EXEC xp_cmdshell 'net use y: \\<FtpServer>\<folder> /user:<UserName> "<Password>"';
END

/*  Step 2 - MOVE Files from network share to working folder  */
BEGIN
    EXEC xp_cmdshell  'if exist y:\*.trn move y:\*.trn y:\working';
END

/*  Step 3 - Kill any exiting connections to the database  */
BEGIN
    USE [master];
    
    DECLARE @kill varchar(8000) = '';  
    
    SELECT @kill = @kill + 'kill ' + CONVERT(varchar(5), session_id) + ';'  
    FROM sys.dm_exec_sessions
    WHERE database_id  = db_id('<DatabaseName>')
    
    EXEC(@kill);
END

/*  Step 4 - Restore Transaction Logs  */
BEGIN
    EXEC dbo.sp_DatabaseRestore 
             @Database = '<DatabaseName>', 
             @BackupPathFull = 'G:\Base Backup\TWHSQL2014FCluster$TWHSQL2014F_<DatabaseName>_FULL_20210305_213505\', 
             @BackupPathLog = 'y:\working\', 
             @RestoreDatabaseName = '<DatabaseName>', 
             @ContinueLogs = 1, 
             @RunRecovery = 0;
END

/*  Step 5 - Change DB to STANDBY mode  */
BEGIN
    Restore Database [<DatabaseName>] With Standby = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\<DatabaseName>_RollBackUndo.bak'
END

/*  Step 6 - MOVE Applied Logs to archive  */
BEGIN
    EXEC xp_cmdshell  'move y:\working\*.* y:\archived';
END

/*  Step 7 - Archive Undo File  */
BEGIN
    EXEC xp_cmdshell  'if exist "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\*.bak" copy "C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\*.bak" "G:\LogShipping Undo File" /y';
END

Upvotes: 0

Views: 1374

Answers (1)

Aeden Maddok
Aeden Maddok

Reputation: 1

This problem has been resolved. The cause of the problem was that a log file did not get restored, but did get moved to the archived folder and so we were attempting to skip a log file in the restoration process. Still, I would have expected the error message to state something akin to the log file doesn't have the correct LSN value or something else to indicate that there was a log missing.

Upvotes: 0

Related Questions