Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2002
    Location
    Greenville, SC
    Posts
    5

    Unanswered: 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.

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    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.
    MCDBA

  3. #3
    Join Date
    Apr 2002
    Location
    Greenville, SC
    Posts
    5
    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.

  4. #4
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    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'
    MCDBA

  5. #5
    Join Date
    Apr 2002
    Location
    Greenville, SC
    Posts
    5
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •