Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. 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

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

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.

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.

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

↑ Back to the top

Keywords: kb, kbprb, kbtshoot, kbexpertiseadvanced, kbsql2005error

↑ Back to the top

Article Info
Article ID : 950891
Revision : 5
Created on : 8/19/2020
Published on : 8/20/2020
Exists online : False
Views : 87