Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

Restoring a database from the production server to a disaster recovery server

I'm trying to restore a database from the production server to our disaster recovery server. Both are running SQL Server 2000. The backup sets are stored on the production server.

When I try to run the restore on the d/r server using a mapped drive on the production server, I get an error message stating 'Cannot open backup device.' The Microsoft Knowledge Base points to this being a permissions problem, but the drive is mapped using the SQL Service accounts' credentials.

Both instances of SQL Server are using the same domain user account for the SQL Service. The acl's for the directory and file both show the SQL Service account having full rights. The destination server's error log has an entry 'Operating system error = 3(The system cannot find the path specified).' If, within the restore script, I copy the file from the mapped drive to a local drive the restore works. Any solutions or hints greatly appreciated.

Based on the information you provided I am guessing that the restore is being done using Query Analyzer. If this is the case I would suggest running the following command to see if you can get a directory listing of the files on the mapped drive.

xp_cmdshell "dir {drive:\path}"  
i.e.  xp_cmdshell "dir e:\productionbackup\"

If you do not get the expected list of files back then the account you are using does not have access to the mapped drive.

To map the drive within query analyzer use the following command:
xp_cmdshell "net use {driveletter:} {servername\share}  
i.e. xp_cmdshell "net use e: \\ProdServer\e$"

Rerun the following command to make sure you get the file list.
xp_cmdshell "dir {drive:\path}"  
i.e.  xp_cmdshell "dir e:\productionbackup\"

If the directory list is complete, then issue the restore command using the mapped drive letter you created:
   FROM DISK = 'e:\productionbackup\ProdDB.bak'
   WITH MOVE 'ProdDB' TO 'c:\ DrDB.mdf',
   MOVE 'ProdDB_log' TO 'c:\DrDB.ldf'

Dig Deeper on SQL Server Backup and Recovery

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.