SQLServerT-SQL腳本還原資料庫的問題

我有如下代碼。我正在嘗試從檔案夾中的檔案中恢復多個資料庫，但具有正確的邏輯名稱。我被這條線卡住了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 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

uj5u.com熱心網友回復：

您缺少檔案名的結束引號。

但是該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 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;

uj5u.com熱心網友回復：

謝謝大家的回復。我會檢查你在這里給我的所有東西。我需要使用這些檔案，因為我需要移動 190 個 dbs，并且所有這些檔案都存盤在單個 bak 檔案中。我沒有找到其他方法來做到這一點。我可以輕松創建 dbs 的備份，但我需要找到正確的方法將它們還原到新服務器

標籤：sql-server 查询语句

上一篇：撰寫一個查詢，其中的匯率在這些日期之間沒有變化

下一篇：使用SQL按答案分組問題