Options
Restore database fails in some situations
audun
Posts: 17
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
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
Comments
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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,
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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
- 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
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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8