redouard
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_Dat a'' TO ''E:\data\' + SPACE (1)+ @DB + '.MDF'',
MOVE ''kings_plus_reference_Dat a_1'' TO ''E:\data\'+ SPACE (1)+ @DB + '_1.MDF'',
MOVE ''kings_plus_reference_Dat a_2'' TO ''E:\data\'+ SPACE (1)+ @DB + '_2.MDF'',
MOVE ''kings_plus_reference_Dat a_3'' TO ''E:\data\'+ SPACE (1)+ @DB + '_3.MDF'',
MOVE ''kings_plus_reference_Dat a_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?
"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
WITH MOVE ''kings_plus_reference_Dat
MOVE ''kings_plus_reference_Dat
MOVE ''kings_plus_reference_Dat
MOVE ''kings_plus_reference_Dat
MOVE ''kings_plus_reference_Dat
MOVE ''kings_plus_reference_Log
exec sp_executesql @s
Any thoughts?
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_Dat a'' TO ''E:\data\' + SPACE (1)+ @DB + '.MDF'',
MOVE ''kings_plus_reference_Dat a_1'' TO ''E:\data\'+ SPACE (1)+ @DB + '_1.MDF'',
MOVE ''kings_plus_reference_Dat a_2'' TO ''E:\data\'+ SPACE (1)+ @DB + '_2.MDF'',
MOVE ''kings_plus_reference_Dat a_3'' TO ''E:\data\'+ SPACE (1)+ @DB + '_3.MDF'',
MOVE ''kings_plus_reference_Dat a_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
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
WITH MOVE ''kings_plus_reference_Dat
MOVE ''kings_plus_reference_Dat
MOVE ''kings_plus_reference_Dat
MOVE ''kings_plus_reference_Dat
MOVE ''kings_plus_reference_Dat
MOVE ''kings_plus_reference_Log
SELECT @s
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you are missing the ' ' around the value..
set @s = ' master..sqlbackup .... '