none
Device activation error - while restoring database

    Question

  • Hi all,

    I have taken a full database backup from a Server and I tried to restore it in another server. SQL Server 2000 is installed in both of them

    Things I did
    1. The database is very huge(about 65GB), so I truncated the log by following http://support.microsoft.com/default.aspx/kb/272318
    2. Created a backup of the database, through UI with default options
    3. Copied the .bak file to the new server and while restoring the backup I am getting the following error:
        
    Device activation error. The physical file name 'c:\dv1data3\dv1data3.ndf' may be incorrect.

    And in my eventlog, I find the following error:
    17204:
    FCB::Open failed: Could not open device c:\dv1data3\dv1data3.ndf for virtual device number(VDN) 4

    The restoration process fails only after some 10-15 mins.

    Any help would be highly appreciated.

    Thanks,
    Jeevitha
    • Edited by Jeevitha - MSFT Wednesday, May 20, 2009 9:30 AM Adding some more info
    Wednesday, May 20, 2009 9:28 AM

Answers

  • If you backup a database from SQL server A and then restore it to a database on SQL server B, probably the GUI can't help you with the restore process.  You have to use the SQL command to do the restore. Because the original database, which may consists of several data files(.ndf, .mdf and .ldf files), requires that the SQL Server B has exact the same file locations. If any path on Server A doesn't exist on Server B it will give you an error. You can use FILELISTONLY to display how many files are included in the original backup. Then use MOVE TO to assign each of the database file to a new location on SQL Server B. Here is an example

    --View how many files are in the current database backup
    RESTORE FILELISTONLY
    FROM disk= 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Northwind.bak'

    --Assign each SQL Server file to a new location on Server B
    RESTORE database Northwind
    FROM disk= 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Northwind.bak'
    with replace,
    move 'Northwind' to 'd:\Northwind.mdf',
    move 'Northwind_Log' to 'd:\Northwind_Log.ldf'
    --Make sure there is no d:\Northwind.mdf, d:\Northwind_Log.ldf exist on Server B before running the restore command.
    Monday, June 01, 2009 11:19 AM
  • Thanks a lot for all your responses!

    The problem was resolved. I am not so sure if my master database was corrupted or something else. I reinstalled SQL server on the same machine and tried the restoration and it worked.

    Thanks,
    Jeevitha
    Saturday, June 06, 2009 3:50 AM

All replies

  • Is c:\dv1data3\ a vaid path on the machine where you perform the restore? Does the SQL Server service account have permission on that share? Do you want to specify some other physical filename? If so, use the MOVE option of the RESTORE DATABASE command. It is a good idea to investigate a backupfile using RESTORE HEADERONLY and RESTORE FILELISTONLY before you perform a restore.
    Tibor Karaszi
    Wednesday, May 20, 2009 11:43 AM
    Moderator
  • Thanks for the response!

    I verified the backup file by calling RESTORE HEADERONLY and FILELISTONLY and found no problems.

    I am getting another error in the eventlogviewer:

    17207 :

    udopen: 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:\DV1DATA3\DV1DATA3.ndf.

    along with the one specified above.

    Any clues?

    Wednesday, May 27, 2009 9:48 AM
  • Hi Jeevitha

    A few things to look out for:

    1. Is there sufficient space on C: to do the restore?
    2. Does the file C:\DV1DATA3\DV1DATA3.ndf already exist?


    Ewan

    If you have found this post helpful, please click the 'Vote as Helpful' link under the star. If this post answers your question, click the 'Mark As Answered' link below. It helps others who experience the same issue in future to find the solution.
    Wednesday, May 27, 2009 10:42 AM
  • If you backup a database from SQL server A and then restore it to a database on SQL server B, probably the GUI can't help you with the restore process.  You have to use the SQL command to do the restore. Because the original database, which may consists of several data files(.ndf, .mdf and .ldf files), requires that the SQL Server B has exact the same file locations. If any path on Server A doesn't exist on Server B it will give you an error. You can use FILELISTONLY to display how many files are included in the original backup. Then use MOVE TO to assign each of the database file to a new location on SQL Server B. Here is an example

    --View how many files are in the current database backup
    RESTORE FILELISTONLY
    FROM disk= 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Northwind.bak'

    --Assign each SQL Server file to a new location on Server B
    RESTORE database Northwind
    FROM disk= 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Northwind.bak'
    with replace,
    move 'Northwind' to 'd:\Northwind.mdf',
    move 'Northwind_Log' to 'd:\Northwind_Log.ldf'
    --Make sure there is no d:\Northwind.mdf, d:\Northwind_Log.ldf exist on Server B before running the restore command.
    Monday, June 01, 2009 11:19 AM
  • Jeevitha,

    Here you have to set default location for the New database.

    See this KB article


    Lets know if it helps...

    Thanks,
    Nimit
    Monday, June 01, 2009 3:01 PM
  • Thanks a lot for all your responses!

    The problem was resolved. I am not so sure if my master database was corrupted or something else. I reinstalled SQL server on the same machine and tried the restoration and it worked.

    Thanks,
    Jeevitha
    Saturday, June 06, 2009 3:50 AM