#sql-server #tsql
Вопрос:
У меня есть код, как показано ниже. Я пытаюсь восстановить несколько баз данных из файлов в папке, но с правильными логическими именами. Я застрял на этой линии insert into … он возвращает правильное имя файла, но с ошибкой, что ни одна кавычка не была закрыта. Мне это идет на пользу. Любые намеки высоко ценятся.
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 SeerverMSSQL13.SYMFONIA21MSSQLDATA' SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd)) '.mdf'', MOVE N''' SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd)) '_log'' TO N''C:Microsoft SQL ServerSQLINSTANCEMSSQLDATA' SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd)) '_log.ldf'', NOUNLOAD, STATS = 10' EXEC(@sqlRestore) FETCH NEXT FROM @FilesCmdshellCursor INTO @FilesCmdshellOutputCmd END Комментарии:
1. Ты пробовал
PRINT 'RESTORE FILELISTONLY FROM DISK=N''F:SQL_Server_Backup_Folder' @FilesCmdshellOutputCmd, Что ты видишь? Я предполагаю, что у переменной нет заключительной цитаты2. Добрый день @AlexB , (1) вместо использования xp_cmdshell для получения списка файлов вам следует использовать файловую систему sys.dm_os_enumerate_filesystem, которая была представлена в SQL Server 2017. (2) Вместо использования
EXECвы должны использоватьsp_executesql. (3) Как @Ник. Макдермэйд упомянул, что вы всегда должны печатать команду, которую хотите выполнить в первую очередь, и подтверждать, что она работает хорошо3. Зачем вообще нужен динамический SQL, учитывая, что
RESTOREон принимает параметры?4. (4) Вы ЗАЯВЛЯЕТЕ
@LocalBackupPath, но никогда не используете его. (5) Вы ОТБРАСЫВАЕТЕ таблицу#stage-gt; создаете ее aagin -gt;gt; и заполняете ее данными — это каждый раз делается внутри цикла, но вы никогда не используете эту таблицу. Вместо этого вы задаете значения переменной@sqlRestore, которые имеют смысл. Вы можете удалить часть, связанную с этой таблицей, в текущем коде (6) Самое важное !!! Вы можете предположить, что имя базы данных похоже на имя файла резервной копии, что, вероятно, в большинстве случаев далеко от истины… слишком много проблем в этом коде… начать с нуля может быть лучше или исправить, и мы продолжим
Ответ №1:
Вы пропустили заключительную цитату для имени файла.
Но RESTORE команда может быть полностью параметризована, так что на самом деле для этого вообще не требуется динамический SQL.
Я настоятельно рекомендую не выполнять доступ к файлам в T-SQL, но если вы действительно этого хотите, вы можете использовать
sys.dm_os_enumerate_filesystemвместо этого.
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 ServerMSSQL13.SYMFONIA21MSSQLDATA' @dbName '.mdf', @log sysname = @dbName '_log', @ldf nvarchar(255) = N'C:Microsoft SQL ServerSQLINSTANCEMSSQLDATA' @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; Ответ №2:
спасибо всем вам за ваши ответы. я проверю все, что вы мне здесь дали. мне нужно использовать файлы, потому что мне нужно переместить 190 баз данных, и все они хранятся в одном файле bak. Я не нашел другого способа сделать это. я могу легко создать резервную копию баз данных, но мне нужно найти правильный способ восстановить их на новом сервере
после всей проверки у меня есть рабочий код. спасибо за подсказки. это, конечно, все еще можно улучшить, но для меня работает так, как ожидалось. я публикую сообщения по аналогичным вопросам:
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 SeerverMSSQL13.SYMFONIA21MSSQLDATA' SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd)) '.mdf'', MOVE N''' SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd)) '_log'' TO N''C:Microsoft SQL ServerSQLINSTANCEMSSQLDATA' 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 SeerverMSSQL13.SYMFONIA21MSSQLDATA' @LogicalName '.mdf'', MOVE ''' @LogicalNameLog ''' TO ''F:MS SQL SeerverMSSQL13.SYMFONIA21MSSQLDATA' @LogicalName 'log.ldf'', RECOVERY, REPLACE, STATS = 10' EXEC(@sqlRestore) FETCH NEXT FROM @FilesCmdshellCursor INTO @FilesCmdshellOutputCmd END