SQL Server 2005 Restore Error

Trying to restore SQL Server 2000 database backup to SQL Server 2005.

Machine 1:
SQL Server 2005 on Windows 2003 SBS.

Machine 2:
SQL Server 2000 on Windows 2003 Server

1. Create (Complete) backup on Machine 2
2. Restore on Machine 1

Result of Restore attempt:

Error Dialog:

Restore failed for Server 'IDSC31057'.  (Microsoft.SqlServer.Smo)

System.Data.SqlClient.SqlError: Directory lookup for the file "C:\Program
Files\Microsoft SQL  Server\MSSQL\data\Ho
spital_Data.MDF" failed with the
operating system error 3(error not found). (Microsoft.SqlServer.Smo)

Can anybody tell me where to start?

OK, We found out the path you BACKED UP must exist on the TARGET machine.

Does this mean you can ONLY restore to the same path you BACKUP ?


"GeoffAtDatagaard" <geoff@datagaard.com.au> wrote in message
news:utvyySEFHHA.4080@TK2MSFTNGP02.phx.gbl...
> Trying to restore SQL Server 2000 database backup to SQL Server 2005.
>
> Machine 1:
> SQL Server 2005 on Windows 2003 SBS.
>
> Machine 2:
> SQL Server 2000 on Windows 2003 Server
>
> 1. Create (Complete) backup on Machine 2
> 2. Restore on Machine 1
>
> Result of Restore attempt:
>
> Error Dialog:
>
> Restore failed for Server 'IDSC31057'.  (Microsoft.SqlServer.Smo)
>
> System.Data.SqlClient.SqlError: Directory lookup for the file "C:\Program
> Files\Microsoft SQL  Server\MSSQL\data\Ho
spital_Data.MDF" failed with the
> operating system error 3(error not found). (Microsoft.SqlServer.Smo)
>
> Can anybody tell me where to start?
>
>
>
>
>
>
>

> Can anybody tell me where to start?

Don't trust the GUI, use the RESTORE command. Next step is to study the MOVE
 option of the RESTORE
command.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www. solidqualitylearning
.com/


"GeoffAtDatagaard" <geoff@datagaard.com.au> wrote in message
news:utvyySEFHHA.4080@TK2MSFTNGP02.phx.gbl...
> Trying to restore SQL Server 2000 database backup to SQL Server 2005.
>
> Machine 1:
> SQL Server 2005 on Windows 2003 SBS.
>
> Machine 2:
> SQL Server 2000 on Windows 2003 Server
>
> 1. Create (Complete) backup on Machine 2
> 2. Restore on Machine 1
>
> Result of Restore attempt:
>
> Error Dialog:
>
> Restore failed for Server 'IDSC31057'.  (Microsoft.SqlServer.Smo)
>
> System.Data.SqlClient.SqlError: Directory lookup for the file "C:\Program 
Files\Microsoft SQL
>  Server\MSSQL\data\Ho
spital_Data.MDF" failed with the operating system erro
r 3(error not found).
> (Microsoft.SqlServer.Smo)
>
> Can anybody tell me where to start?
>
>
>
>
>
>
>

Hi there,

You can use the nice UI to restore the database to any location you
want. I think its on the second page of the restore database GUI. It's
under the heading of Options or something.

HTH

Simon

Hi,

As Tibor pointed out; Use the RESTORE DATABASE WITH MOVE option to move the
physical files to an alternate directory.
Take a look about the RESTORE DATABASE command in books online.

Thanks
Hari

"GeoffAtDatagaard" <geoff@datagaard.com.au> wrote in message
news:eznOLjEFHHA.1252@TK2MSFTNGP02.phx.gbl...
> OK, We found out the path you BACKED UP must exist on the TARGET machine.
>
> Does this mean you can ONLY restore to the same path you BACKUP ?
>
>
> "GeoffAtDatagaard" <geoff@datagaard.com.au> wrote in message
> news:utvyySEFHHA.4080@TK2MSFTNGP02.phx.gbl... 
>
>