April 4, 2002 at 12:37 pm
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 in Query Analyzer:
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.
In the error log I get this 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
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+'''')
When trying to debug I replaced the EXEC w/ print and I get the following:
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'
Before I attempt the restore I kill all users and put the database in single user mode.
Any help would be appreciated.
Bill Stevenson
MCSE, MCDBA
April 4, 2002 at 1:01 pm
Is this a valid path on the server? THe with MOVE is to restore from a different path than that the backup was made with. This applies to different servers as well.
Steve Jones
April 4, 2002 at 1:18 pm
The paths should be valid.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
Bill Stevenson
MCSE, MCDBA
Bill Stevenson
MCSE, MCDBA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply