June 24, 2019 at 7:47 am
Hi All,
Need some help in preparing a restore cmd dynamically using metadata which is stored inside a table. We need to read the file locations from the metadata table
and form the RESTORE commands.
-- metadata table
CREATE TABLE [dbo].[FilelocationsTBL](
[dbid] [smallint] NULL,
[dbname] [nvarchar](128) NULL,
[logicalname] [varchar](100) NOT NULL,
[filename] [varchar](300) NOT NULL
)
GO
INSERT FilelocationsTBL(dbid,dbname,logicalname,filename) VALUES (24,'DB2','cmx_ors_prm','H:\MSSQL\Data\DB2\DB2_prm.mdf')
INSERT FilelocationsTBL(dbid,dbname,logicalname,filename) VALUES (24,'DB2','cmx_ors_log','I:\MSSQL\Log\DB2\DB2_log.ldf')
INSERT FilelocationsTBL(dbid,dbname,logicalname,filename) VALUES (24,'DB2','cmx_ors_dat','F:\MSSQL\Data\DB2\DB2_dat.ndf')
INSERT FilelocationsTBL(dbid,dbname,logicalname,filename) VALUES (24,'DB2','cmx_ors_ind','H:\MSSQL\Data\DB2\DB2_ind.ndf')
SELECT * FROM FilelocationsTBL WHERE dbname = 'DB2';
-- I want to prepate below restore cmd using the above table
/*
RESTORE DATABASE [DB2] FROM DISK='J:\backup\cmx_FullBKP.bak'
WITH REPLACE,
MOVE 'cmx_ors_prm' TO 'H:\MSSQL\Data\DB2\DB2_prm.mdf',
MOVE 'cmx_ors_log' TO 'I:\MSSQL\Log\DB2\DB2_log.ldf',
MOVE 'cmx_ors_dat' TO 'F:\MSSQL\Data\DB2\DB2_dat.ndf',
MOVE 'cmx_ors_ind' TO 'H:\MSSQL\Data\DB2\DB2_ind.ndf'
go
*/
I tried but I am getting some errors. Can anyone help me out please.
--- inputs
declare @v_dbname varchar(100)
set @v_dbname = 'DB2';
declare @v_bkpfilename varchar(100)
set @v_bkpfilename = 'J:\backup\cmx_FullBKP.bak';
declare @stmt varchar(2000);
--set @stmt= 'RESTORE DATABASE '+@v_dbname+' FROM DISK = '''+@v_bkpfilename+''' WITH REPLACE,';
set @stmt= 'RESTORE DATABASE '+@v_dbname+' FROM DISK = '''+@v_bkpfilename+''' WITH REPLACE,'+' MOVE '''+logicalname+''' FROM FilelocationsTBL WHERE dbname = ''DB2''';
PRINT @stmt
-- ERROR MESSAGE
/*
Msg 207, Level 16, State 1, Line 47
Invalid column name 'logicalname'.
*/
Expected output
===================
-- I want to prepate below restore cmd using the above table
/*
RESTORE DATABASE [DB2] FROM DISK='J:\backup\cmx_FullBKP.bak'
WITH REPLACE,
MOVE 'cmx_ors_prm' TO 'H:\MSSQL\Data\DB2\DB2_prm.mdf',
MOVE 'cmx_ors_log' TO 'I:\MSSQL\Log\DB2\DB2_log.ldf',
MOVE 'cmx_ors_dat' TO 'F:\MSSQL\Data\DB2\DB2_dat.ndf',
MOVE 'cmx_ors_ind' TO 'H:\MSSQL\Data\DB2\DB2_ind.ndf'
go
*/
Thanks,
Sam
June 24, 2019 at 8:02 am
The error appears to be letting you the problem here:
Msg 207, Level 16, State 1, Line 47
Invalid column name 'logicalname'.
In your statement you have '...''' + logicalname + '''...' but you have no FROM. Where is logicalname coming from? Should it be a variable? Should there be a FROM?
Also, you really shouldn't be injecting raw string values into a dynamic SQL statement; you need to ensure you parametrise your queries or properly quote them. Here you need to quote them properly using QUOTENAMElsuch as N'...' + QUOTENAME(@v_dbname) + N'...' and N'...N' + QUOTENAME(@v_bkpfilename,'''') + N'...'. I cover how to do this in an article here, which'll be a better description than be explaining it all here.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 24, 2019 at 8:34 am
Thanks for the pointers for dynamic sql . it has to come from FROM clause.
June 24, 2019 at 11:09 am
Thanks for the pointers for dynamic sql . it has to come from FROM clause.
Then you need to change from statement from a SET to a SELECT and include a FROM (and likely a WHERE to limit the result set to 1 row).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
June 25, 2019 at 10:13 am
Okay. thanks Thom.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply