SQLTeam.com | Weblogs | Forums

Restoring db with xp_executesql

restore
tsql
sql2012

#1

Good Morning, I am new to SQL DBA duties and I am trying to edit previous DBA's sp_executesql script to refresh DEV with PROD and I am finding it difficult to specify a different server to refresh dev to.

SET @TSQL = 'ALTER DATABASE ['+@NewDBName+'] SET MULTI_USER WITH ROLLBACK IMMEDIATE;
RESTORE DATABASE ['+@NewDBName+']
FROM DISK = N''\wv-vmfp01\sqltest$\SQLBackups\DEVELOPMENT_REFRESH'+@DBName+'_'+@TimeStamp+'.bak''
WITH MOVE N''' + @filename + '_data'' TO N''\wv-vmfp01\sqltest$\SQLBackups\DEVELOPMENT_REFRESH\DB_Data'+@NewDBName+'.mdf''
, MOVE N''' + @filename + '_log'' TO N''\wv-vmfp01\sqltest$\SQLBackups\DEVELOPMENT_REFRESH\DB_Log'+@NewDBName+'.ldf''
, NOUNLOAD
, REPLACE
, STATS = 5;'
EXEC sp_executesql @TSQL


#2

AFAIK you need to run the Restore ON the target server, thus I don;t think you can use a RESTORE command to cause a restore to a remote server.