Restore database fails in some situations

audun

Hi,

I am in the process of restoring many databases from sql2000 onto a sql2005 system. This is done running a script looping through a folder with backup files and restoring.

Most restores works fine while some fail with

Server: Msg 3013
RESTORE DATABASE is terminating abnormally.
Server: Msg 3119
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Server: Msg 3156
File 'p17225355_log' cannot be restored to 'm:\log\p17225355.ldf'. Use WITH MOVE to identify a valid location for the file.
Server: Msg 3634
The operating system returned the error '5(error not found)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'm:\log\p17225355.ldf'.

Server: Msg 3156
File 'p17225355_dat' cannot be restored to 's:\data\p17225355.mdf'. Use WITH MOVE to identify a valid location for the file.
Server: Msg 3634
The operating system returned the error '5(error not found)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 's:\data\p17225355.mdf'.

SQL Backup exit code: 1100
SQL error code: 3634


All files are restored to the same folder (both working and non-working).

All failing restores have in common that the database files already exists in the targetfolders. They have all previously been attached and detached.
Now, renaming the files is ok (no process is holding the files).

Running filemon (sysinternals.com) gives me numbers of:

08:55:27 sqlservr.exe:3924 IRP_MJ_CREATE S:\data\p17225355.mdf ACCESS DENIED LON\24645-sql1


If I convert the original sqlbackup file to .bak file and do a "normal" restore, it all works fine.

The LON\24645-sql1 domain user that both the sql service and the sqlbackup service is running under, is added to the administrators group.

Any clue what the reason for this might be?

Regards,
Audun Jensen

petey

Do the 'm:\log' and 's:\data' paths exist?

audun

Yes, they exist.

I have script running doing this; reading the backupfile from a source, restoring to a target. We are talking thousands of databases here.
(Most of these (90%) will be detached as we have a routine that will attach active databases and detach inactive ones.)

All restores issued from this script are working ok except for the ones who have already been attached and detached (==> there are already a .mdf and .ldf in place). All folders exist.

Regards,

petey

I am trying to reproduce your error, but I need to know exactly what is being done.

Is this what you are doing:

- detach a SQL 2000 database
- attach the database files to a SQL 2005 instance
- detach the database from the SQL 2005 instance
- restore backups (full, diff ?) made from the SQL 2000 instance

Also, what is the restore command that is being used?

Thanks.

SethP

I'm having exactly the same problem. Here's what I'm doing:
- on the sql 2000 server, I do a standard backup to a sqb file
- I copy that file to the local drive on the sql 2005 server (they are completely different computers)
- I open sql backup on the 2005 computer, and click restore
- I select the file from the 2000 server
- I select either an existing or new database, it doesn't matter
- restore fails every time. It says, "Use WITH MOVE to identify a valid location for the file." There are no permission issues of any kind.

I did a quick backup from the 2005 db and a restore into a new db on the same server with no problems.

I am unable to restore any backups from sql 2000 to 2005.

Thank you.

Seth

petey

When you restore a database on to a different server, SQL Server will create the database files (mdf and ldf) in the same folder as the originating server. If this folder does not exist, you need to perform the restore using the MOVE option. This is a SQL Server requirement.

On Step 3 of the Restore Wizard, click on the 'Database files ...' button. In that dialog, you can change the physical file names to valid locations on your machine.

If your database already exists, then you do not need to use the MOVE option.