cbrammer1219 (10/14/2016)
I am new to DBA duties and I have taken over for the previous DBA. He has a stored procedure that refreshes a dev db from production database, which these are on the same server. I have tried to edit the code with declaring the @sourceserver and @destserver and adding it to the script but does not work. The script below is from the original sp. Can this even be done through script? Or can I do it with powershell?
SET @TSQL = 'ALTER DATABASE ['+@NewDBName+'] SET MULTI_USER WITH ROLLBACK IMMEDIATE;RESTORE DATABASE ['+@NewDBName+']
FROM DISK = N''\\lmprod\sqltest$\sqlbus\DEVELOPMENT_REFRESH\'+@DBName+'_'+@TimeStamp+'.bak''
WITH MOVE N''' + @filename + '_data'' TO N''\\lmprod\sqltest$\sqlbus\DEVELOPMENT_REFRESH\DB_Data\'+@NewDBName+'.mdf''
, MOVE N''' + @filename + '_log'' TO N''\\lmprod\sqltest$\sqlbus\DEVELOPMENT_REFRESH\DB_Log\'+@NewDBName+'.ldf''
, NOUNLOAD
, REPLACE
, STATS = 5;'
EXEC sp_executesql @TSQL,
Yes it can definitely be done with a script. I would stay with a standard T-SQL script for this since it's a fairly common thing. Since you only have partial code here, it's difficult to guess what your issues could be. I would start by changing the EXEC sp_executesql to just Print @TSQL. Then you can see the code and play with what you are dynamically generating and try to figure out what's wrong.
Sue