asked on

Invalid column prefix 'master.': No table name specified

I'm getting the following error message when I parse the below script:

"Invalid column prefix 'master.': No table name specified"

Here is my sample code:

DECLARE @DB VARCHAR (50)
declare @s nvarchar(3000)
SET @DB = 'PUBS'
--set @s = 'ALTER DATABASE'+ space(1) + @DB + space(1) + 'SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE'
set @s = 'ALTER DATABASE'+ space(1) + @DB + space(1) + 'SET MULTI_USER'

--restore reporting database
set @s = master..sqlbackup N'-SQL "RESTORE DATABASE ['+ @DB + ']
FROM DISK = ''\\ykcm1s1\y$\fromYKCMOS7\\Plandata_<DATETIME yyyymmdd>.sqb''
WITH MOVE ''kings_plus_reference_Data'' TO ''E:\data\' + SPACE (1)+ @DB + '.MDF'',
MOVE ''kings_plus_reference_Data_1'' TO ''E:\data\'+ SPACE (1)+ @DB + '_1.MDF'',
MOVE ''kings_plus_reference_Data_2'' TO ''E:\data\'+ SPACE (1)+ @DB + '_2.MDF'',
MOVE ''kings_plus_reference_Data_3'' TO ''E:\data\'+ SPACE (1)+ @DB + '_3.MDF'',
MOVE ''kings_plus_reference_Data_4'' TO ''E:\data\'+ SPACE (1)+ @DB + '_4.MDF'',
MOVE ''kings_plus_reference_Log'' TO ''F:\log\'+ SPACE (1)+ @DB + '_Log.LDF'', REPLACE"'"

exec sp_executesql @s

Any thoughts?

Guy Hengel [angelIII / a3]

>set @s = master..sqlbackup

you are missing the ' ' around the value..

set @s = ' master..sqlbackup .... '

Aneesh

DECLARE @DB VARCHAR (50)
declare @s nvarchar(3000)
SET @DB = 'PUBS'
--set @s = 'ALTER DATABASE'+ space(1) + @DB + space(1) + 'SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE'
set @s = 'ALTER DATABASE'+ space(1) + @DB + space(1) + 'SET MULTI_USER'

--restore reporting database
set @s = 'master..sqlbackup -SQL "RESTORE DATABASE ['+ @DB + ']
FROM DISK = ''\\ykcm1s1\y$\fromYKCMOS7\\Plandata_<DATETIME yyyymmdd>.sqb''
WITH MOVE ''kings_plus_reference_Data'' TO ''E:\data\' + SPACE (1)+ @DB + '.MDF'',
MOVE ''kings_plus_reference_Data_1'' TO ''E:\data\'+ SPACE (1)+ @DB + '_1.MDF'',
MOVE ''kings_plus_reference_Data_2'' TO ''E:\data\'+ SPACE (1)+ @DB + '_2.MDF'',
MOVE ''kings_plus_reference_Data_3'' TO ''E:\data\'+ SPACE (1)+ @DB + '_3.MDF'',
MOVE ''kings_plus_reference_Data_4'' TO ''E:\data\'+ SPACE (1)+ @DB + '_4.MDF'',
MOVE ''kings_plus_reference_Log'' TO ''F:\log\'+ SPACE (1)+ @DB + '_Log.LDF'', REPLACE"'

SELECT @s

redouard

ASKER

Thank you for your response. I would like to schedule the above script via sql. How can I do that with the format you suggested?

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial