I had problems restoring a database backup from one server to another, and blogged about the problems here.

Further to this problem, I thought that I would 'finish off' the issue of restoring a database backup from one server to another.

As detailed in my previous post, I detailed how to select the backup file to restore.  Before you select OK to restore the databse, you need to also change a further couple of settings.  In Microsoft SQL Management Studio, the restore database dialog, once you have the "source for restore" selected, you should check a further few options.

Quite often when you are restoring a database from a different server, more than likely the database paths are different.  The backup file has the paths of the original source location contained within the file.  You therefore need to make sure that they map to the paths that are specific to the destination machine.  You therefore need to go to the "options" page and change these paths.

If you are restoring a database over an existing database, then it is probably also a good idea to "overwrite the existing database".  Then in the "Restore the database files as" section, change all of the paths to the destination machines database paths.

For example, if the backup file had paths of the following, I would assume that the SQL data was installed on the H drive.

H:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\<database name>.mdf

If the destination SQL server was installed on the C drive along with the data:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\<database name>.mdf

Obviously, if you do not set the correct paths, when you come to try and restore the database you will get the following style error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Restore failed for Server '<server name>'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

System.Data.SqlClient.SqlError: The tail of the log for the database "<database name>" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log. (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

I've been having trouble trying to restore a SQL 2005 database.

Background

The database already exists, and I wanted to restore a backup of the 'live' database to my local machine.  So, I copied the file to my local machine.  Then opened up Microsoft SQL Server Management Studio, selected the database and then Tasks > Restore > Database...

I obviously wanted to restore from a "device" (file), so I selected the "from device" radio button, and selected the "..." button to open the "Specify Backup" dialog.  Ensuring that the Backup media was set to file, I clcked on the "add" button and located the backup file.

However, the problem comes when I clicked on the OK button.  I get the following error:

The error

TITLE: Microsoft SQL Server Management Studio
------------------------------

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------
ADDITIONAL INFORMATION:

Cannot open backup device '<drive>:\<path>\<backup file>.bak'. Operating system error 5(error not found).
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3201)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=3201&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

The resolution

In my investigations, I have found out a couple things.  Firstly, the mapping text for the error is incorrect.  It should not be "error not found", it should in fact be "access denied".  This error text is the key, and since in my instance for what ever reason it is not being mapped correctly.  This therefore lead me down the route that allowed me to resolve this problem.

As the correctly mapped error text indicated it's an access problem.  Basically, the service that SQL is running under, needs to have security rights to the backup file.  So, simply adding these rights to the backup file resolved my issue!

Conclusion

It appears to be a reasonably common problem where mapping text does not map corretly.  Therefore, if you do get an error, do not rely on the error text, but the error number.  In this case '5'.  Further more, always make sure that if SQL is accessing files from other servers, that the service account has access to those files!