Tuesday, November 23, 2010

SQL Server Restore error working with backup file (.bak) – Operating system error 21 (The device is not ready)

When I create backup database (.bak) from production database server and restore in development server. An error may occur when I restore it using the SQL Server management studio.
The error is like this: "System.Data.SqlClient.SqlError: Directory lookup for the file "...rap.mdf" failed with the operating system error 21(The device is not ready.). (Microsoft.SqlServer.Smo)"

The backups store the path of each file that was backed up, by default a restore will restore these files to the same path. The trouble comes when restoring on a different machine where this path does not exists.

We should check following options before going further:

  • Should create a new database at known location (of ldf & mdf files)
  • make  the overwriting = true when restore
  • In restore as option - provide correct file paths(same as of  created ldf & mdf files)