PowerShell Scripts Passing Multiple Variable to SQL Scripts Encounter Errors

• HKK

  SSC Enthusiast

  Points: 182

  More actions

  November 13, 2018 at 3:01 am

  #378603

  Hi experts,
  I'm new in SQL Server, I hope you can give me some guideline.
  I got issue when passing 2 variables from Powershell script to SQL Script. I really no idea what is went wrong. 
  The error message as below:

  Invoke-Sqlcmd : Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\Backup\"D:\SQL DB Backup\Test.bak"'. Operating system error 123(The filename,
directory name, or volume label syntax is incorrect.).
RESTORE FILELIST is terminating abnormally.
Error converting data type nvarchar to nvarchar.
At D:\Working\Calling Restore DB.ps1:5 char:1
+ Invoke-Sqlcmd -InputFile ('D:\Working\RestoreDB.sql') -ServerInstance ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  + CategoryInfo    : InvalidOperation: ( : ) [Invoke-Sqlcmd], SqlPowerShellSqlExecutionException
  + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShell.GetScriptCommand

  My Powershell scripts is very simple.

  Import-Module "sqlps" -DisableNameChecking
$SQLArray = 'BackupFile = "D:\SQL DB Backup\Test.bak"','DbName = "test123"'
Invoke-Sqlcmd -InputFile ('D:\Working\RestoreDB.sql') -ServerInstance 'kl_kokkeong\SQL2014' -Variable $SQLArray
  

  And my SQL Scripts:
  USE master
GO

  -- Declare the table to use for retrieving the logical names of the data files from the .bak file
DECLARE @FileList TABLE
  (
  LogicalName nvarchar(128) NOT NULL,
  PhysicalName nvarchar(260) NOT NULL,
  Type char(1) NOT NULL,
  FileGroupName nvarchar(128) NULL,
  Size numeric(20, 0) NOT NULL,
  MaxSize numeric(20, 0) NOT NULL,
  FileID bigint NULL,
  CreateLSN numeric(25,0) NULL,
  DropLSN numeric(25,0) NULL,
  UniqueID uniqueidentifier NULL,
  ReadOnlyLSN numeric(25,0) NULL ,
  ReadWriteLSN numeric(25,0) NULL,
  BackupSizeInBytes bigint NULL,
  SourceBlockSize int NULL,
  FileGroupID int NULL,
  LogGroupGUID uniqueidentifier NULL,
  DifferentialBaseLSN numeric(25,0)NULL,
  DifferentialBaseGUID uniqueidentifier NULL,
  IsReadOnly bit NULL,
  IsPresent bit NULL,
  TDEThumbprint varbinary(128) NULL
);

  -- Declare some variables to use in script.
DECLARE @RestoreStatement nvarchar(max);
DECLARE @BackupFile nvarchar(max);
DECLARE @logical_data nvarchar(max), @logical_log nvarchar(max), @logical_Ndata nvarchar(max);
DECLARE @dest_data_path nvarchar(max), @dest_log_path nvarchar(max);
DECLARE @DbName nvarchar(max);
DECLARE @FullDataFilePath nvarchar(max), @FullLogFilePath nvarchar(max), @FullNDataFilePath nvarchar(max);
DECLARE @NDataExists bit;

  -- Set variables to use in the script. Change these as needed....
SET @BackupFile = '$(BackupFile)'
SET @DbName = '$(DbName)'
SET @dest_data_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(name + '.mdf', LOWER(physical_name)) - 1)
        FROM master.sys.master_files
                     WHERE database_id = 1 AND file_id = 1)
SET @dest_log_path = (SELECT SUBSTRING(physical_name, 1, CHARINDEX(name + '.ldf', LOWER(physical_name)) - 1)
        FROM master.sys.master_files
                     WHERE database_id = 1 AND file_id = 2)

  IF EXISTS(SELECT name FROM sys.databases WHERE name = @DbName)
RAISERROR(15600,-1,-1,'Database already exists!');
ELSE
BEGIN
SET @NDataExists = 0
SET @RestoreStatement = N'RESTORE FILELISTONLY FROM DISK=N''' + @BackupFile + ''''
INSERT INTO @FileList EXEC(@RestoreStatement);
SET @logical_data = (select LogicalName from @FileList where Type = 'D' and FileID = 1)
SET @logical_log = (select LogicalName from @FileList where Type = 'L' and FileID = 2)
IF EXISTS(select LogicalName from @FileList where Type = 'D' and FileID = 3)
BEGIN
  SET @NDataExists = 1
  SET @logical_Ndata = (select LogicalName from @FileList where Type = 'D' and FileID = 3)
END

  SET @FullDataFilePath = (SELECT(CONCAT(@dest_data_path,@DbName,'.mdf')))
SET @FullLogFilePath = (SELECT(CONCAT(@dest_log_path,@DbName,'.ldf')))
IF @NDataExists = 1
  SET @FullNDataFilePath = (SELECT(CONCAT(@dest_data_path,@DbName,'.ndf')))

  IF @NDataExists = 0
BEGIN
  RESTORE DATABASE @DbName
  FROM DISK = @BackupFile
  WITH MOVE @logical_data TO @FullDataFilePath,
    MOVE @logical_log TO @FullLogFilePath
END
ELSE
BEGIN
RESTORE DATABASE @DbName
  FROM DISK = @BackupFile
  WITH MOVE @logical_data TO @FullDataFilePath,
    MOVE @logical_log TO @FullLogFilePath,
         MOVE @logical_Ndata TO @FullNDataFilePath
END
END;

  GO

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  November 13, 2018 at 6:19 am

  #2013306

  Check the permissions on that folder that the error identifies.   Be sure the security context under which that powershell script runs, has access to that folder.

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)