Refresh DEV from Production

  • 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,

  • 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

Viewing 2 posts - 1 through 1 (of 1 total)

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