SQL Server Performance Forum – Threads Archive
restore db problem
Hi,<br /><br />On the serveur1 I have an application that uses a sql database.<br />If any problem, I want to change server 1 with server 2. For that it is necessary to have always the last modifications on the second server.<br /> Because I don#%92t like to make a restore over network, each day I make a backup on the first server, I copy the backup on the second server and I want to make a restore. My code for the restore is here and the error message is: <br /><br />Server: Msg 137, Level 15, State 2, Line 3<br />Must declare the variable ‘@restore_file’.<br /><br />My code:<br />declare @dirConst nvarchar(60)<br />declare @strConst nvarchar(80)<br />declare @x nvarchar(80)<br /><br />create table #Files<br />(<br />coutput nvarchar(2000)<br />)<br /><br />create table #Dirs<br />(<br />coutput nvarchar(2000)<br />)<br /><br />set @dirConst= ‘D:SQLDATAMSSQLTEST'<br />set @strConst= ‘dir ‘ + @dirConst + ‘*.bak'<br /><br />insert into #Files execute xp_cmdshell @strConst<br /><br /><br />delete #files from (Select top 5 * from #Files) as b where #files.coutput=b.coutput<br /><br />update #files set coutput = rtrim(ltrim(right(coutput, Charindex(char(32), reverse(rtrim(coutput))))))<br /><br />select @x= coutput <br />from #Files <br />where coutput NOT IN (‘NULL’, ‘bytes’, ‘free’)<br /><br />declare @restore_file varchar(12<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /><br />set @restore_file= @dirConst + @x<br /><br />ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE<br />GO<br /><br />restore database test<br />from disk = @restore_file<br />with replace<br />go<br />drop table #Files<br /><br />Can anybody help me, please?<br /><br />This is because of the GO in your code. Everything upto GO will be considered as 1 batch and will be executed at once. You need to remove the GO, for the restore command to be included into this batch.
]]>