Problem with SQL Server T-SQL script to restore database-CodePudding

I have code like below. I am trying to restore multiple databases from files in folder but with proper logical names. I am stuck with the line insert into ... it returns the proper file name but with error that no quotation mark was not closed. It looks good for me. Any hints are highly appreciated.

 DECLARE @FilesCmdshell TABLE (
    outputCmd NVARCHAR (255)
)   

DECLARE @FilesCmdshellCursor CURSOR 
DECLARE @FilesCmdshellOutputCmd AS NVARCHAR(255)
DECLARE @LocalBackupPath AS NVARCHAR(255) = 'F:\SQL_Server_Backup_Folder'
INSERT INTO @FilesCmdshell (outputCmd) EXEC master.sys.xp_cmdshell 'dir /B  F:\SQL_Server_Backup_Folder\*.bak'    
SET @FilesCmdshellCursor = CURSOR FOR SELECT outputCmd FROM @FilesCmdshell

OPEN @FilesCmdshellCursor
FETCH NEXT FROM @FilesCmdshellCursor INTO @FilesCmdshellOutputCmd
WHILE @@FETCH_STATUS = 0
BEGIN   
drop table if exists #stage
CREATE TABLE #stage
(
 LogicalName VARCHAR(50),
 PhysicalName VARCHAR(255),
 Type CHAR(1),
 FileGroupName  VARCHAR(50),
 [Size]VARCHAR(50),
 [MaxSize]VARCHAR(50)
)

----Identify a Logical and a Physical Name file's name  of the database
INSERT INTO #stage EXEC('RESTORE FILELISTONLY FROM DISK=N''F:\SQL_Server_Backup_Folder\'  @FilesCmdshellOutputCmd)

    DECLARE @sqlRestore NVARCHAR(MAX) = 'RESTORE DATABASE ['   SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd))   '] FROM  DISK = N''F:\SQL_Server_Backup_Folder\'   SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd))   '.bak'' WITH  FILE = 1,  MOVE N'''   SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd))   ''' TO N''F:\MS SQL Seerver\MSSQL13.SYMFONIA21\MSSQL\DATA\'   SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd))   '.mdf'',  MOVE N'''   SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd))   '_log'' TO N''C:\Microsoft SQL Server\SQLINSTANCE\MSSQL\DATA\'   SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd))   '_log.ldf'', NOUNLOAD,  STATS = 10'
    EXEC(@sqlRestore)

    FETCH NEXT FROM @FilesCmdshellCursor INTO @FilesCmdshellOutputCmd
END

CodePudding user response：

You were missing a closing quote for the filename.

But the RESTORE command can be fully parameterized, so this doesn't actually need dynamic SQL at all.

I strongly recommend not doing file access in T-SQL, but if you really do want to, you can use sys.dm_os_enumerate_filesystem instead.

DECLARE @FilesCmdshell TABLE (
    full_path nvarchar(255),
    filename  nvarchar(255)
);   

DECLARE @LocalBackupPath AS NVARCHAR(255) = 'F:\SQL_Server_Backup_Folder';

INSERT INTO @FilesCmdshell
  (full_path, filename)
SELECT
  full_filesystem_path,
  file_or_directory_name
FROM sys.dm_os_enumerate_filesystem(@LocalBackupPath, '*.bak');

DECLARE @FilesCmdshellCursor CURSOR;
DECLARE @filepath nvarchar(255), @filename nvarchar(255);
SET @FilesCmdshellCursor =
    CURSOR FAST_FORWARD FOR
    SELECT full_path, filename
    FROM @FilesCmdshell;

OPEN @FilesCmdshellCursor;
FETCH NEXT FROM @FilesCmdshellCursor
    INTO @filepath, @filename;

WHILE @@FETCH_STATUS = 0
BEGIN

    DECLARE @dbName sysname = SUBSTRING(@filename, 0, CHARINDEX('.', @filename));

    DECLARE
        @mdf nvarchar(255) = N'F:\MS SQL Server\MSSQL13.SYMFONIA21\MSSQL\DATA\'   @dbName   '.mdf',
        @log sysname = @dbName   '_log',
        @ldf nvarchar(255) = N'C:\Microsoft SQL Server\SQLINSTANCE\MSSQL\DATA\'   @dbName   '_log.ldf';

    RESTORE DATABASE @dbName FROM DISK = @filepath
      WITH 
        MOVE @dbName TO @mdf,
        MOVE @log TO @ldf,
        NOUNLOAD,
        STATS = 10;

FETCH NEXT FROM @FilesCmdshellCursor
    INTO @filepath, @filename;
END;

CodePudding user response：

thank you all for your replies. i will check all what you gave me here. i need to use the files because i need to move 190 dbs and all of them are stored in single bak files. I didn't find other way to do that. i can easily create the backup of the dbs but i need to find proper way to restore them to new server

after all the checking I have a working code. thanks for the hints. it for sure can still be improved but ffor me works as expected. i am posting for similar questions:

DECLARE @FilesCmdshell TABLE (
    outputCmd NVARCHAR (255)
)   
DECLARE @FilesCmdshellCursor CURSOR 
DECLARE @FilesCmdshellOutputCmd AS NVARCHAR(255)
DECLARE @LocalBackupPath AS NVARCHAR(255) = 'F:\SQL_Server_Backup_Folder'
DECLARE @LogicalName VARCHAR(50)
DECLARE @LogicalNameLog VARCHAR(50)
INSERT INTO @FilesCmdshell (outputCmd) EXEC master.sys.xp_cmdshell 'dir /B  F:\SQL_Server_Backup_Folder\*.bak'    
SET @FilesCmdshellCursor = CURSOR FOR SELECT outputCmd FROM @FilesCmdshell
OPEN @FilesCmdshellCursor
FETCH NEXT FROM @FilesCmdshellCursor INTO @FilesCmdshellOutputCmd
WHILE @@FETCH_STATUS = 0
BEGIN   
drop table if exists #stage
CREATE TABLE #stage
(
LogicalName nvarchar(128)
,PhysicalName nvarchar(260)
,Type char(1)
,FileGroupName nvarchar(128)
,Size numeric(20,0)
,MaxSize numeric(20,0),
Fileidtiny int,
CreateLSN numeric(25,0),
DropLSN numeric(25, 0),
UniqueID uniqueidentifier,
ReadOnlyLSN numeric(25,0),
ReadWriteLSN numeric(25,0),
BackupSizeInBytes bigint,
SourceBlocSize int,
FileGroupId int,
LogGroupGUID uniqueidentifier,
DifferentialBaseLSN numeric(25,0),
DifferentialBaseGUID uniqueidentifier,
IsReadOnly bit,
IsPresent bit,
alast nvarchar(128),
last nvarchar(128)
)

----Identify a Logical and a Physical Name file's name  of the database
--PRINT 'RESTORE FILELISTONLY FROM DISK=N''F:\SQL_Server_Backup_Folder\'  @FilesCmdshellOutputCmd ''''
INSERT INTO #stage EXEC('RESTORE FILELISTONLY FROM DISK=N''F:\SQL_Server_Backup_Folder\'  @FilesCmdshellOutputCmd '''')
SELECT @LogicalName=LogicalName from #stage where type = 'D'
SELECT @LogicalNameLog=LogicalName from #stage where type = 'L'
PRINT @LogicalName
    --DECLARE @sqlRestore NVARCHAR(MAX) = 'RESTORE DATABASE ['   SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd))   '] FROM  DISK = N''F:\SQL_Server_Backup_Folder\'   SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd))   '.bak'' WITH  FILE = 1,  MOVE N'''   SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd))   ''' TO N''F:\MS SQL Seerver\MSSQL13.SYMFONIA21\MSSQL\DATA\'   SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd))   '.mdf'',  MOVE N'''   SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd))   '_log'' TO N''C:\Microsoft SQL Server\SQLINSTANCE\MSSQL\DATA\'   SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd))   '_log.ldf'', NOUNLOAD,  STATS = 10'
    DECLARE @sqlRestore NVARCHAR(MAX) = 'RESTORE DATABASE ['   @LogicalName   '] FROM  DISK = N''F:\SQL_Server_Backup_Folder\'   SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd))   '.bak'' WITH  FILE = 1,  MOVE '''   @LogicalName   ''' TO ''F:\MS SQL Seerver\MSSQL13.SYMFONIA21\MSSQL\DATA\'   @LogicalName   '.mdf'', MOVE '''   @LogicalNameLog   ''' TO ''F:\MS SQL Seerver\MSSQL13.SYMFONIA21\MSSQL\DATA\'   @LogicalName   'log.ldf'', RECOVERY, REPLACE, STATS = 10'
                                        
    EXEC(@sqlRestore)

    FETCH NEXT FROM @FilesCmdshellCursor INTO @FilesCmdshellOutputCmd
END