Copying database between servers

19 Dec 2005 9:23 AM

Bigalexx

I need to move a SQL 2005 database between 2 servers.
But the 2 servers have different path structure due to language difference.
One server has the databse installed under "c:\Program Files ..." and the
other under "C:\Programmer..." It is not possible to restore the database,
and present the error Directory lookup for the file" C:\Programmer\Microsoft
SQL Server\MSSQL\Data\xx_Data.MDF" failed with the operating system error
The path was not fould.

How can I manually manage the backup and Restore to accomodate the different
path setup on the 2 servers?

19 Dec 2005 10:11 AM

Hank Arnold

I've used Enterprise Manager to export the original database and import it
to the new server. As I recall, I got an error popup and was given the
opportunity to change the location.

One approach I've used is to create the directory structure I wanted on the
new server. I then detached the database (in Enterprise Manager) and copied
the MDF file to where I want it. Then attach the database and change the
location of the MDF file. Verify it works and delete the old copy. You can
do the same for the LDF (log) file...

--
Regards,
Hank Arnold

Show quote

"Bigalexx" <Bigalexx@community.nospam> wrote in message
news:%23sv3s3HBGHA.2040@TK2MSFTNGP14.phx.gbl...
>I need to move a SQL 2005 database between 2 servers.
> But the 2 servers have different path structure due to language
> difference. One server has the databse installed under "c:\Program Files
> ..." and the other under "C:\Programmer..." It is not possible to restore
> the database, and present the error Directory lookup for the file"
> C:\Programmer\Microsoft SQL Server\MSSQL\Data\xx_Data.MDF" failed with the
> operating system error The path was not fould.
>
> How can I manually manage the backup and Restore to accomodate the
> different path setup on the 2 servers?
>

19 Dec 2005 10:26 AM

Tibor Karaszi

You need to use the MOVE option for the backup command. This is documented in Books Online (BACKUP
DATABASE). This is also exposed in Management Studio, options page, "Restore As" column

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

Show quote

"Bigalexx" <Bigalexx@community.nospam> wrote in message
news:%23sv3s3HBGHA.2040@TK2MSFTNGP14.phx.gbl...
>I need to move a SQL 2005 database between 2 servers.
> But the 2 servers have different path structure due to language difference. One server has the
> databse installed under "c:\Program Files ..." and the other under "C:\Programmer..." It is not
> possible to restore the database, and present the error Directory lookup for the file"
> C:\Programmer\Microsoft SQL Server\MSSQL\Data\xx_Data.MDF" failed with the operating system error
> The path was not fould.
>
> How can I manually manage the backup and Restore to accomodate the different path setup on the 2
> servers?
>