Restoring a template db to a new named db

SQL Server Administration
1

ALTER PROCEDURE [dbo].[instantiate_database]
@dbName nvarchar(50)
AS
BEGIN
DECLARE @sql NVARCHAR(MAX);

SET @sql = 'RESTORE DATABASE ' + QUOTENAME(@dbName) +
' FROM DISK = ''C:\Program Files\Microsoft SQL Server\MSSQL15.GLASTONBERRY\MSSQL\Backup\Template.bak'' ' +
' WITH REPLACE, RECOVERY, ' +
' MOVE ''Commercial'' TO ''C:\Program Files\Microsoft SQL Server\MSSQL15.GLASTONBERRY\MSSQL\Backup' + @dbName + '.mdf'', ' +
' MOVE ''Commercial_Log'' TO ''C:\Program Files\Microsoft SQL Server\MSSQL15.GLASTONBERRY\MSSQL\Backup' + @dbName + '.ldf'';';

EXEC sp_executesql @sql;

END

This procedure works when executed in SSMS. I am needing to call this procedure from a PHP form.

	$dbName = filter_input(INPUT_POST, 'Name');
	$sql = "{call paincommon.dbo.instantiate_database(?)}";
	$params = array($dbName); 

	if ($stmt = sqlsrv_prepare($aconn, $sql, $params)) {    
		if(sqlsrv_execute($stmt) === false) {
			die("Execution failed: " . print_r(sqlsrv_errors(), true));
		} else {
			$_SESSION['database_created'] = 'Database ' . $dbName . ' created';	
		}
	} else {      
		die("Preparation failed: " . print_r(sqlsrv_errors(), true));  
	}

This creates the database but the status shows as (Restoring....) and never completes.
ChatGPT gave me some suggestions, but I have not found an answer as of yet.

2

SQL Server 14.0.3381 running on a linux box

3

You seem to be using Windows paths. On linux you probably need to take the backup from something like /var/opt/mssql/backup and restore to something like /var/opt/mssql/data/ and /var/opt/mssql/log/.

4

My apologies, but I get the same results on Windows and Linux. Here is my Linux procedure.

ALTER PROCEDURE [dbo].[instantiate_database]
@dbName nvarchar(50)
AS
BEGIN
DECLARE @sql NVARCHAR(MAX);

SET @sql = 'RESTORE DATABASE ' + QUOTENAME(@dbName) +
           ' FROM DISK = ''/var/opt/mssql/data/Template.bak'' ' +
           ' WITH MOVE ''Commercial'' TO ''/var/opt/mssql/data/' + @dbName + '.mdf'',' +
           ' MOVE ''Commercial_Log'' TO ''/var/opt/mssql/data/' + @dbName + '.ldf'',' +
           ' REPLACE;';

EXEC sp_executesql @sql;

END

5

I have been told that it may have something to do with permissions and PHP. I am logging in with the same credentials that I use in SSMS.

6
  1. How are you checking the db is in the restoring state. If using SSMS you need to right-click to refresh the database list.
  2. You do not explicitly state RECOVERY in your SP so maybe the defaults are different when calling the SP from PHP. Try something like:
USE paincommon;
GO
SET ANSI_NULLS, QUOTED_IDENTIFIER ON;
GO
CREATE OR ALTER PROCEDURE [dbo].[instantiate_database]
	@dbname sysname
AS
SET NOCOUNT, XACT_ABORT ON;

IF @dbname LIKE '%[^a-zA-Z0-9_]%'
	RETURN;

DECLARE @sql nvarchar(MAX);

SET @sql = CONCAT(
N'RESTORE DATABASE ', QUOTENAME(@dbName) ,N'
FROM DISK = ''/var/opt/mssql/data/Template.bak''
WITH RECOVERY, REPLACE
	,MOVE ''Commercial'' TO ''/var/opt/mssql/data/', @dbName, N'.mdf''
	,MOVE ''Commercial_Log'' TO ''/var/opt/mssql/data/', @dbName, N'.ldf'';')

--print @sql;
EXEC sp_executesql @sql;
GO

With the limited information you have provided, this is as far as I can go. If running a proc like this you will need to be very careful with permissions to stop injection attacks.

7

Please tell me what information is lacking to help answer this request. Yes, I have right-clicked to refresh the database list.

8

image

9

image

10 
USE [master];
RESTORE DATABASE pt_109 WITH RECOVERY;
GO
11

Msg 4333, Level 16, State 1, Line 2
The database cannot be recovered because the log was not restored.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

Completion time: 2024-09-17T11:48:21.0962492-04:00

12

You need to do some divide an conquer problem solving to find out why the log was not restored. Maybe another online db has the same log file name so it cannot be overwritten. Maybe the backup is corrupt etc

13

Anything in the SQL error log?

14

The restore works correctly within SSMS so I believe that it is not file corruption related.

15

There is nothing of interest in the SQL Error Log