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

Article translations Article translations
Article ID: 950891 - View products that this article applies to.
Expand all | Collapse all

On This Page

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.

WORKAROUND

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

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.

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.

STATUS

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

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.)

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
For more information about the RESTORE statement, visit the following MSDN Web site:
http://msdn.microsoft.com/en-us/library/ms186858.aspx

Properties

Article ID: 950891 - Last Review: February 20, 2009 - Revision: 1.0
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
Keywords: 
kbexpertiseadvanced kbtshoot kbprb KB950891

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com