October 14, 2016 at 1:25 pm
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,
October 14, 2016 at 2:28 pm
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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy