Restore Procedure not working

Question

Restore Procedure not working

Hall of Fame

Points: 3242

December 19, 2024 at 8:13 pm

#4502029

I created a small procedure that reads the backup file and restores the DB daily. But when I execute the procedure Exec RestoreDatabaseFromLatestBackup @BackupPath = 'D:\Backup', @DatabaseName = 'TestDB', I get the following error

Error occurred: The name 'Restore Database TestDB from Disk = 'D:\Backup\Backup.bak' With Replace, keep_cdc' is not a valid identifier

But when I copy the error and run it in a different window, I am able to restore the DB successfully. Can someone help me troubleshoot the issue?

The code is attached with this question.

CREATE PROCEDURE RestoreDatabaseFromLatestBackup 
    @BackupPath NVARCHAR(500),  
    @DatabaseName NVARCHAR(128) 
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @LatestBackupFile NVARCHAR(500);
    DECLARE @cmd NVARCHAR(MAX);
    DECLARE @RestoreSQL NVARCHAR(MAX);

    BEGIN TRY
        
        IF @BackupPath IS NULL OR @BackupPath = ''
        BEGIN
            RAISERROR('Backup path is not provided.', 16, 1);
            RETURN;
        END

        CREATE TABLE #BackupFiles (FileName NVARCHAR(500));

        SET @cmd = 'DIR "' + @BackupPath + '\*.bak" /B';

        INSERT INTO #BackupFiles (FileName)
        EXEC xp_cmdshell @cmd;

        SELECT TOP 1 @LatestBackupFile = FileName
        FROM #BackupFiles
        WHERE FileName IS NOT NULL
          AND FileName <> ''
          AND FileName LIKE '%.bak';

        DROP TABLE #BackupFiles;

        IF @LatestBackupFile IS NULL
        BEGIN
            RAISERROR('No backup file found in the specified directory.', 16, 1);
            RETURN;
        END

        SET @LatestBackupFile = CONCAT(@BackupPath, '\', @LatestBackupFile);

        SET @RestoreSQL = N'
            RESTORE DATABASE [' + @DatabaseName + N']
            FROM DISK = ''' + @LatestBackupFile + N'''
            WITH REPLACE, Keep_cdc';

        EXEC(@RestoreSQL);

        
    END TRY
    BEGIN CATCH
        PRINT 'Error occurred: ' + ERROR_MESSAGE();
    END CATCH
END;
GO

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18854 More actions · Answer 1

This smells like a permissions issue for the SP. I am not anywhere I can test this but I suspect you will need to sign the SP and then have a certificate login in the master database that is a member of dbcreator.

Chitown Hall of Fame Points: 3242 More actions · Answer 2

Chitown

Hall of Fame

Points: 3242

December 19, 2024 at 9:28 pm

#4502045

I have admin rights on the test server.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 3

For starters, change this...

DECLARE @cmd NVARCHAR(MAX);

... to this ...

DECLARE @cmd NVARCHAR(4000);

Then see the following for why...

https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-cmdshell-transact-sql#arguments

This reply was modified 2 hours, 53 minutes ago by Jeff Moden. Reason: Correct to suggestion. Op used NVARCHAR, not VARCHAR

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)