时间戳

首页 > 解决方案 > 用于恢复数据库的 SQL Server T-SQL 脚本问题

sql-server - 用于恢复数据库的 SQL Server T-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

标签： sql-servertsql

解决方案

您缺少文件名的结束引号。

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

推荐阅读