xp_cmdshell for restore db

Question

xp_cmdshell for restore db

dallas13

SSCertifiable

Points: 7491
More actions
July 14, 2016 at 7:40 am

#309194

When I just run this command from query windows it works fine.
restore database test from DISK='\\share\dumps\test.bak ' with replace
Now I am trying,
DECLARE @sqlcmd nvarchar(1000)
DECLARE @Result int
SET @sqlcmd = 'restore database test from DISK='\\share\dumps\test.bak ' with replace'
PRINT @sqlcmd
EXEC @Result = xp_cmdshell @sqlcmd ;
And getting error as 'restore' is not recognized as an internal or external command,
I tried various options with quotes around restore database but no luck. I need help with syntax and quotes. Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

RESTORE is a SQL command, not a command shell command. Use [font="Courier New"]EXEC (@sqlcmd)[/font] instead. And don't forget to escape those enclosed quotes by doubling them up.

John

dallas13 SSCertifiable Points: 7491 More actions · Answer 2

Thanks John. Here is my SP,

CREATE PROCEDURE p_dba_executesqlcmd

@sqlcmd nvarchar(1000)

AS

BEGIN

SET NOCOUNT ON;

EXEC(@sqlcmd) ;

END

GO

Now when I execute it from query it works,

DECLARE@return_value int

EXEC@return_value = [dbo].[p_dba_executesqlcmd]

@sqlcmd = N'restore database test from DISK= ''\\share\dumps\test.bak'' with replace'

SELECT'Return Value' = @return_value

GO

When I try it from SSIS 2014, EXEC p_dba_execute_sqlcmd @sqlcmd = ?

gives me error, I understand I have to use with result sets clause but What are my result sets in this case from this sp??

EXEC p_dba_executesqlcmd @sqlcmd = ?

WITH RESULT SETS

((

?????????????????????

));

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

I don't think you need to use WITH RESULTS SETS. There is no result set. You could use WITH RESULT SETS NONE if you wanted, but I can't see why it's necessary. Just do it like this:EXEC p_dba_execute_sqlcmd @sqlcmd = ? or, if you're interested in the return value as well, do it like this with the return value as an output parameter:EXEC ? = p_dba_execute_sqlcmd @sqlcmd = ?

John

dallas13 SSCertifiable Points: 7491 More actions · Answer 4

Once again Thanks a lot John. WITH RESULT SETS NONE it worked.

So I used this command inside "OLE DB Command" component of SSIS and now I am testing how it handles errors,

IF we click on advanced Editor of "OLE DB Command" the last and 4th tab is Input and Output Properties --> OLE DB Command Error Output and there we can see Output columns 1) ErrorCode and 2) ErrorColumn Now on right side of Error code and Error column the datatype selected is four byte signed integer (DT I4) and I can not change these datatypes.

Hence when I try to test error handling, it gives me ErrorCode = -1073704166 and Error Column =0

When I searched this error code, it says,

https://msdn.microsoft.com/en-us/library/ms345164.aspx

-1073704166 The command execution generated errors. but this is not a specific error. I did not put backup file and it should give me below error, but its not happening.

Msg 3201, Level 16, State 2, Line 3

Cannot open backup device '\\localhost\dumps\test.bak'. Operating system error 2(The system cannot find the file specified.).

Msg 3013, Level 16, State 1, Line 3

RESTORE DATABASE is terminating abnormally.

Can we change these datatypes?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

Please will you explain what your whole package is doing? I don't think the OLE DB Command is the right component to use. It belongs in a data flow, and as far as I can tell, you're not doing any ETL here. If you're running a query to loop through a list of databases to restore, do that with an Execute SQL task, direct the result set to a variable, and loop through that with a For Each loop.

John