restore db problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

restore db problem

Author

Topic

joanne
Starting Member

46 Posts

Posted - 2004-10-28 : 16:54:53

Hi,

On the serveur1 I have an application that uses a sql database.
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.
Because I don’t 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:

Server: Msg 137, Level 15, State 2, Line 3
Must declare the variable '@restore_file'.

My code:
declare @dirConst nvarchar(60)
declare @strConst nvarchar(80)
declare @x nvarchar(80)

create table #Files
(
coutput nvarchar(2000)
)

create table #Dirs
(
coutput nvarchar(2000)
)

set @dirConst= 'D:\SQLDATA\MSSQL\TEST\'
set @strConst= 'dir ' + @dirConst + '*.bak'

insert into #Files execute xp_cmdshell @strConst

delete #files from (Select top 5 * from #Files) as b where #files.coutput=b.coutput

update #files set coutput = rtrim(ltrim(right(coutput, Charindex(char(32), reverse(rtrim(coutput))))))

select @x= coutput
from #Files
where coutput NOT IN ('NULL', 'bytes', 'free')

declare @restore_file varchar(128)
set @restore_file= @dirConst + @x

ALTER DATABASE test SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

restore database test
from disk = @restore_file
with replace
go
drop table #Files

Can anybody help me, please?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-10-28 : 16:57:42

It's because of the GO statement. Once the GO runs, all variables above are no longer declared.

Tara

joanne
Starting Member

46 Posts

Posted - 2004-10-28 : 18:06:05

Thanks very much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources