Article ID: 950891 - Last Review: February 20, 2009 - Revision: 1.0

You cannot restore a database from a backup file when you use a SQL Server login ID that has the dbcreator server role to connect to an instance of SQL Server 2005

View products that this article applies to.
System TipThis article applies to a different operating system than the one you are using. Article content that may not be relevant to you is disabled.

On This Page

Expand all | Collapse all

SYMPTOMS

Consider the following scenario:
  • In SQL Server Management Studio, you use a SQL Server login ID that has the dbcreator server role to connect to an instance of Microsoft SQL Server 2005.
  • You try to restore a database from a database backup file.
  • In the Specify Backup dialog box, you specify File as the backup media, and then you click Add.

    You receive an error message that resembles the following:
    C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup
    Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.

    If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Locate dialog box.
  • You click OK to dismiss the error message. In the Locate Backup File dialog box, you specify the database backup and file path, and then you click OK.
In this scenario, you receive the following error message:
The Database Engine service could not resolve the specified file location. Either the location does not exist, or the current login account does not have access to it. Verify that the file location exists, and that the login account has permissions on it.
Back to the top

WORKAROUND

To work around this problem, use the RESTORE statement to restore the database. To do this, use one of the following methods.
Back to the top

Method 1: Run the RESTORE statement in SQL Server Management Studio

  1. Start SQL Server Management Studio, and then connect to an instance of SQL Server 2005.
  2. Click New Query.
  3. Run the restore statement. To do this, use one of the following methods:
    • To restore the entire database from the database backup file, use a statement that resembles the following:
      RESTORE DATABASE <DBName> FROM DISK = '<BackupFilePath>\<BackupFileName>'
GO
      Note In this statement, <DBName> represents the name of the database that you want to restore to, <BackupFilePath> represents the path of the database backup file, and <BackupFileName> represents the name of the backup file.
    • To restore a specific file from the database backup file, use a statement that resembles the following:
      RESTORE DATABASE <DBName> FILE = <FileName> FROM DISK = '<BackupFilePath>\<BackupFileName>'
GO
      Note In this statement, <FileName> represents the name of the file that you want to restore.
Back to the top

Method 2: Use the Sqlcmd.exe utility to run the RESTORE statement

  1. Click Start, click Run, type cmd, and then click OK.
  2. Run a command that resembles the following:
    sqlcmd /S<ServerName>\<InstanceName> –U<LoginName> -P<LoginPassword>

    Note In this statement, <ServerName> represents the name of the server that is running the instance of SQL Server 2005; <InstanceName> represents the name of the instance of SQL Server 2005; <LoginName> is the name of the SQL Server login ID that you use to connect to the instance of SQL Server 2005; and <LoginPassword> represents the password of the SQL Server login ID.
  3. Run the RESTORE statement. To do this, use one of the following methods:
    • To restore the entire database from the database backup file, use a statement that resembles the following:
      RESTORE DATABASE <DBName> FROM DISK = '<BackupFilePath>\<BackupFileName>'
GO
      Note In this statement, <DBName> represents the name of the database that you want to restore to, <BackupFilePath> represents the path of the database backup file, and <BackupFileName> represents the name of the backup file.
    • To restore a specific file from the database backup file, use a statement that resembles the following:
      RESTORE DATABASE <DBName> FILE = <FileName> FROM DISK = '<BackupFilePath>\<BackupFileName>'
GO
      Note In this statement, <FileName> represents the name of the file that you want to restore.
Back to the top

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Back to the top

MORE INFORMATION

To open the Specify Backup dialog box, follow these steps:
  1. Open SQL Server Management Studio, and then connect to an instance of SQL Server 2005.
  2. Right-click Databases, and then click Restore Database or Restore Files and Filegroups.
  3. In the Restore Database dialog box or in the Restore Files and Filegroups dialog box, click From device, and then click the ellipsis button. (The ellipsis button contains three dots.)
Back to the top

REFERENCES

For more information about the Sqlcmd.exe utility, visit the following Microsoft Developer Network (MSDN) Web site:
http://msdn.microsoft.com/en-us/library/ms162773.aspx (http://msdn.microsoft.com/en-us/library/ms162773.aspx)
For more information about the RESTORE statement, visit the following MSDN Web site:
http://msdn.microsoft.com/en-us/library/ms186858.aspx (http://msdn.microsoft.com/en-us/library/ms186858.aspx)
Back to the top
APPLIES TO
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Workgroup Edition
Back to the top
Keywords: 
kbexpertiseadvanced kbtshoot kbprb KB950891
Back to the top