What are the challenges you face when working across database platforms? Take the survey
Options

Restore database fails in some situations

audunaudun Posts: 17
edited January 8, 2007 10:05PM in SQL Backup Previous Versions
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

Comments

  • Options
    peteypetey Posts: 2,358 New member
    Do the 'm:\log' and 's:\data' paths exist?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    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,
  • Options
    peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    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
  • Options
    peteypetey Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.