我有如下代碼。我正在嘗試從檔案夾中的檔案中恢復多個資料庫，但具有正確的邏輯名稱。我被這條線卡住了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 的備份，但我需要找到正確的方法將它們還原到新服務器
在所有檢查之后，我有一個作業代碼。感謝您的提示。它肯定仍然可以改進，但 ffor me 可以按預期作業。我正在為類似的問題發帖：
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/325204.html
標籤：sql-server 查询语句