Restore with new name

[Andrzej]

Hello

I'd like to make a copy of database with new name. The name must be set in the run time. I mean
data: 2008/MM/DD it means that the name should be set test_2008
data: 2009/MM/DD it means that the name should be set test_2009
and so on ...

I have prepared sql batch file as follows.


[1]USE ABBMeasureHistory
[2]GO
[1]DECLARE @y varchar(10)
[2]SET @y = SUBSTRING(CONVERT(char, GETDATE(), 102),0,5)
[3]SELECT @y
[4]RESTORE FILELISTONLY
[5] FROM DISK = 'c:\sql\ABBMeasureHistory.bak';
[6]RESTORE DATABASE 'ABBMeasureHistory_' + @y
[7] FROM DISK = 'c:\sql\ABBMeasureHistory.bak'
[8] WITH RECOVERY,
[9] MOVE 'ABBMeasureHistory_dat' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\ABBMeasureHistory_' + @y + '.mdf',
[10] MOVE 'ABBMeasureHistory_log' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\ABBMeasureHistory_' + @y + 'Log.ldf'
[11]GO


Unfortnately ther is a bug. The system returns a fault message as follows:
1> 2> 1> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> Msg 170, Level 15, State 1, Server TEST, Line 6
Line 6: Incorrect syntax near 'ABBMeasureHistory_'.
1>

Could you please help me to solve this issue

Thanks i nadvance

[Andrzej]

I've just found a bug

it is not allowed to asign variable as follows

...
@v = 'test' + @y
...

insted of this it should be

...
SET @v = 'test' + @y
...