error in restore script

I have attempted to create a script to do a backup and restore that would be useable for several different database servers. The script works fine on SQL 2000 but on SQL 7 I get the following error.

Server: Msg 3156, Level 16, State 2, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]The file 'C:\temp\test_log.ldf ' cannot be used by RESTORE. Consider using the WITH MOVE option to identify a valid location for the file.
Server: Msg 3013, Level 16, State 1, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation terminating abnormally.

The piece of code in question is:

EXEC('RESTORE DATABASE '+@.targetdb+'
FROM DISK = '''+@.sourcedb_backupdir+'''
WITH REPLACE, RECOVERY,
MOVE '''+@.source_restore_mdf_name+''' TO '''+@.target_restore_mdf_dir+''',
MOVE '''+@.source_restore_ldf_name+''' TO '''+@.target_restore_ldf_dir+'''')

Any help would be appreciated

The whole script (version SQL 7) is attached if that would help as well.I have gotten this message when I was restoring a dump that was from a different database. I see that you do use the MOVE, have you verified the physical and logical names? Instead of doing the EXEC, how about doing a PRINT, for debugging and see what the command is.|||This is what I get when I use the print statement:


RESTORE DATABASE test
FROM DISK = 'C:\temp\Northwind.bak'
WITH REPLACE, RECOVERY,
MOVE 'Northwind' TO 'c:\temp\test.mdf',
MOVE 'Northwind_log' TO 'C:\temp\test_log.ldf'

I get the following error in Query Analyzer:

Server: Msg 3156, Level 16, State 2, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]The file 'c:\temp\test.mdf' cannot be used by RESTORE. Consider using the WITH MOVE option to identify a valid location for the file.
Server: Msg 3013, Level 16, State 1, Line 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Backup or restore operation terminating abnormally.

When I look in the error log I get this error message:

2002-04-04 11:57:16.41 kernel BackupFileDesc::VerifyCreatability: Operating system error 32(The process cannot access the file because it is being used by another process.) during the creation/opening of physical device C:\temp\test_log.ldf

2002-04-04 13:28:15.00 kernel BackupFileDesc::VerifyCreatability: Operating system error 32(The process cannot access the file because it is being used by another process.) during the creation/opening of physical device c:\temp\test.mdf. 0

This error is confusing because before I start the restore I kill all users and put the db in single user mode.|||When you run the RESTORE command are you excuting it from MASTER? Is the logical names for database Northwind correct 'Northwind' for database file and 'Northwind_log' for transaction log? Also are the physical files for Test 'c:\temp\test.mdf' for data and 'C:\temp\test_log.ldf' for transaction log.
Just looking at the physical names they look like they would be the default names SQL Server generates <database name>_log.ldf and <database name>_data.mdf . The only thing is that if this is true then your physical name should be 'c:\temp\test_data.mdf'|||I am useing the master db.

If I run sp_helpdb for Northwind I get the following:

Northwind, 1, C:\MSSQL\DATA\northwnd.mdf, PRIMARY, 4864 KB, Unlimited, 10%, data only

Northwind_log, 2, C:\MSSQL7\DATA\northwnd.ldf, NULL, 1024 KB, Unlimited, 10%, log only

If I run sp_helpdb for test I get the following:

Northwind, 1, c:\temp\test.mdf, PRIMARY, 4864 KB, Unlimited, 10%, data only

Northwind_log, 2, C:\temp\test_log.ldf, NULL, 1024 KB, Unlimited, 10%, log only

When I set my variables I use the following:

CREATE TABLE #db_sysfiles
(
name VARCHAR(50),
filename VARCHAR(255),
dbname VARCHAR(50)
)

INSERT INTO #db_sysfiles EXEC ('SELECT a.name, a.filename, b.name FROM '+@.sourcedb+'..sysfiles a, master..sysdatabases b WHERE b.name = '''+@.sourcedb+'''')

INSERT INTO #db_sysfiles EXEC ('SELECT a.name, a.filename, b.name FROM '+@.targetdb+'..sysfiles a, master..sysdatabases b WHERE b.name = '''+@.targetdb+'''')

SELECT @.source_restore_mdf_name = RTRIM(name)
FROM #db_sysfiles
WHERE filename
LIKE '%.mdf%'
AND dbname = @.sourcedb

SELECT @.source_restore_ldf_name = RTRIM(name)
FROM #db_sysfiles
WHERE filename LIKE '%.ldf%'
AND dbname = @.sourcedb

SELECT @.target_restore_mdf_name = RTRIM(name)
FROM #db_sysfiles
WHERE filename
LIKE '%.mdf%'
AND dbname = @.targetdb

SELECT @.target_restore_ldf_name = RTRIM(name)
FROM #db_sysfiles
WHERE filename
LIKE '%.ldf%'
AND dbname = @.targetdb

SELECT @.target_restore_mdf_dir = RTRIM(filename)
FROM #db_sysfiles
WHERE filename
LIKE '%.mdf%'
AND dbname = @.targetdb

SELECT @.target_restore_ldf_dir = RTRIM(filename)
FROM #db_sysfiles
WHERE filename
LIKE '%.ldf%'
AND dbname = @.targetdb

SELECT @.source_restore_mdf_dir = RTRIM(filename)
FROM #db_sysfiles
WHERE filename
LIKE '%.mdf%'
AND dbname = @.sourcedb

SELECT @.source_restore_ldf_dir = RTRIM(filename)
FROM #db_sysfiles
WHERE filename
LIKE '%.ldf%'
AND dbname = @.sourcedb