0

When I restore a database with the SQL Server Management Studio it works just fine. But when I restore it from the php ( using the function sqlsrv_query ), the database gets stuck on the "Restoring..." state.

Below is my PHP code: SP creation:

sqlsrv_configure("WarningsReturnAsErrors", 0);

$createSPwh = "CREATE OR ALTER PROCEDURE [dbo].[CreateEmptyDB_wh] 
@PackageName nvarchar(100)
AS
BEGIN
DECLARE @FullPackageName nvarchar(100)
SET @FullPackageName = @PackageName
DECLARE @PCKGNAMEDATE VARCHAR(100) = (SELECT LEFT(@FullPackageName, CHARINDEX('_', @FullPackageName) - 1) + --AS [Surname],
        REPLACE(SUBSTRING(@FullPackageName, CHARINDEX('_', @FullPackageName), LEN(@FullPackageName)), '.bak', '') AS [FirstName])
        
DECLARE @PCKGNAME VARCHAR(100) = (SELECT REPLACE(@PCKGNAMEDATE,RIGHT(@PCKGNAMEDATE, CHARINDEX('_', REVERSE(@PCKGNAMEDATE)) - 1),''))
SET @PCKGNAME = (SELECT LEFT(@PCKGNAME, LEN(@PCKGNAME) - 1) )
DECLARE @DiskDrive VARCHAR(100) = 'E:\' + @FullPackageName-- +'.bak'
DECLARE @PackageNameWH VARCHAR(100) = @PCKGNAME + '_wh'
DECLARE @dbdatwh VARCHAR(100) = 'h:\mssql\data\' + @PCKGNAME+ '_wh.mdf'
DECLARE @dblogwh VARCHAR(100) = 'S:\MSSQL\Logs\' + @PCKGNAME+ '_wh.ldf'

BEGIN TRY  

RESTORE DATABASE @PackageNameWH
FROM DISK = @DiskDrive
WITH RECOVERY
,file=4,NOUNLOAD,
MOVE 'DB_WH_DAT' TO @dbdatwh,
MOVE 'DB_WH_log' TO @dblogwh

END TRY 
BEGIN CATCH
    SELECT    ERROR_NUMBER() AS ErrorNumber,    ERROR_STATE() AS ErrorState,    ERROR_SEVERITY() AS ErrorSeverity,    ERROR_PROCEDURE() AS ErrorProcedure,    ERROR_LINE() AS ErrorLine,    ERROR_MESSAGE() AS ErrorMessage;
END CATCH
END";

create DB:

$stmtSPwh =sqlsrv_query($sqlconn,$createSPwh);

if($stmtSPwh === false) {
    echo "createSPwh";
    die(print_r(sqlsrv_errors(),true));
}

exe cmd:

$tsql_callSP_wh = "EXEC dbo.CreateEmptyDB_wh @PackageName = ?";

$PackageName = "WarehouseDB.bak";
$params = array(array(&$PackageName, SQLSRV_PARAM_IN));

$stmt3 = sqlsrv_prepare($sqlconn, $tsql_callSP_wh, $params);
if (!sqlsrv_execute($stmt3)) {
    die(print_r(sqlsrv_errors(),true));
}

After executing the above commands from PHP, it's not throwing any error but is stuck in the restored state.

And If try to access this DB, I am getting the following error: Array ( [0] => Array ( [0] => 42000 [SQLSTATE] => 42000 [1] => 5052 [code] => 5052 [2] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Access is not permitted while a database is in the Restoring state. [message]

I am using PHP : 7.4.26 I am referred many stack forums: SQL Server: Database stuck in "Restoring" state SQL Server: Database stuck in “Restoring” state only with PHP but not getting exact idea.

Can you please help me to find out the error?

Mani Karthi
  • 25
  • 6
  • You need to use the WITH RECOVERY option, with your database RESTORE command – ADyson Dec 05 '22 at 07:49
  • tried that too. – Mani Karthi Dec 05 '22 at 08:04
  • The connection could be left in a weird state if the stored procedure outputs any rowcount events that don't get consumed by PHP. Have you tried inserting `SET NOCOUNT ON;` inside your stored procedure code before any other statements? – AlwaysLearning Dec 05 '22 at 08:32
  • yes, I used set nocount on in the next statement, but the thing it's stuck in restoring in the CreateEmptyDB_wh SP, no other statement used before. @AlwaysLearning – Mani Karthi Dec 05 '22 at 10:01
  • 1
    @AlwaysLearning is right, you may try to fetch all informational messages. I posted an [answer](https://stackoverflow.com/questions/64018598/backup-sql-server-database-using-php/64041766#64041766) about a similar `BACKUP` issue, if you want you may try the solution. – Zhorov Dec 05 '22 at 10:55

0 Answers0