Refresh DEV from Production

• cbrammer1219

  SSCarpal Tunnel

  Points: 4476

  More actions

  October 14, 2016 at 1:25 pm

  #330929

  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,

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  October 14, 2016 at 2:28 pm

  #1906703

  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