Move database from one computer to another

[Database Administrator]

Need help with moving a SQL Server Express 2005 database from one computer
to another.

I used SSMSE to backup the database on the source server.

Then on the target computer I created a new datbase with the same name and
tried to restore the backup - it failed saying this backup is not for the
current database.

I also tried to restore without putting in a name so that I was hoping it
would create a new one - no luck.

The question is how do I create a new database form the backup?

Or what is the best method to use to copy a database from one computer to
the other.

Thak You

[Database Administrator]

Hi,

From the Management studio,Query window execute below:-

1. Using Restore filelistonly command identify the logical file names of the
database backup file

RESTORE FILELISTONLY from disk='c:\x.bak'

2. With the output of the above query use RESTORE database
RESTORE DATABASE from disk='c:\backup\x.bak'
WITH move 'logical_mdf_filename' to 'new physical name with path',
move 'logical_ldf_filename' to 'new physical log name with Path'


Change the database name and file names based on the backup and database
name you have.


Thanks
Hari
SQL Server MVP

"Hemang Shah" wrote in message
news:uwfcmkcuGHA.4460@TK2MSFTNGP04.phx.gbl...
> Need help with moving a SQL Server Express 2005 database from one computer
> to another.
>
> I used SSMSE to backup the database on the source server.
>
> Then on the target computer I created a new datbase with the same name and
> tried to restore the backup - it failed saying this backup is not for the
> current database.
>
> I also tried to restore without putting in a name so that I was hoping it
> would create a new one - no luck.
>
> The question is how do I create a new database form the backup?
>
> Or what is the best method to use to copy a database from one computer to
> the other.
>
> Thak You
>
>
>

[Database Administrator]

Hi,

From the Management studio,Query window execute below:-

1. Using Restore filelistonly command identify the logical file names of the
database backup file

RESTORE FILELISTONLY from disk='c:\x.bak'

2. With the output of the above query use RESTORE database
RESTORE DATABASE from disk='c:\backup\x.bak'
WITH move 'logical_mdf_filename' to 'new physical name with path',
move 'logical_ldf_filename' to 'new physical log name with Path'


Change the database name and file names based on the backup and database
name you have.


Thanks
Hari
SQL Server MVP

"Hemang Shah" wrote in message
news:uwfcmkcuGHA.4460@TK2MSFTNGP04.phx.gbl...
> Need help with moving a SQL Server Express 2005 database from one computer
> to another.
>
> I used SSMSE to backup the database on the source server.
>
> Then on the target computer I created a new datbase with the same name and
> tried to restore the backup - it failed saying this backup is not for the
> current database.
>
> I also tried to restore without putting in a name so that I was hoping it
> would create a new one - no luck.
>
> The question is how do I create a new database form the backup?
>
> Or what is the best method to use to copy a database from one computer to
> the other.
>
> Thak You
>
>
>

[Database Administrator]

Hello Hari

Thanks for the response.

This is the error I get:

Msg 3634, Level 16, State 1, Line 1

The operating system returned the error '5(Access is denied.)' while
attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program
Files\Microsoft SQL Server\Data\ClientManagement.mdf'.

Msg 3156, Level 16, State 5, Line 1

File 'ClientManagement_dat' cannot be restored to 'C:\Program
Files\Microsoft SQL Server\Data\ClientManagement.mdf'. Use WITH MOVE to
identify a valid location for the file.

Msg 3634, Level 16, State 1, Line 1

The operating system returned the error '5(Access is denied.)' while
attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program
Files\Microsoft SQL Server\Data\ClientManagement.ldf'.

Msg 3156, Level 16, State 5, Line 1

File 'ClientManagement_log' cannot be restored to 'C:\Program
Files\Microsoft SQL Server\Data\ClientManagement.ldf'. Use WITH MOVE to
identify a valid location for the file.

Msg 3119, Level 16, State 1, Line 1

Problems were identified while planning for the RESTORE statement. Previous
messages provide details.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.



This is the command I typed:



RESTORE DATABASE ClientManagement from disk='c:\ClientManagement.bak'

WITH move 'ClientManagement_dat'

to 'C:\Program Files\Microsoft SQL Server\Data\ClientManagement.mdf',

move 'ClientManagement_log'

to 'C:\Program Files\Microsoft SQL Server\Data\ClientManagement.ldf'





Thanks



"Hari Prasad" wrote in message
news:ec9CAEduGHA.5044@TK2MSFTNGP05.phx.gbl...
> Hi,
>
> From the Management studio,Query window execute below:-
>
> 1. Using Restore filelistonly command identify the logical file names of
> the database backup file
>
> RESTORE FILELISTONLY from disk='c:\x.bak'
>
> 2. With the output of the above query use RESTORE database
> RESTORE DATABASE from disk='c:\backup\x.bak'
> WITH move 'logical_mdf_filename' to 'new physical name with path',
> move 'logical_ldf_filename' to 'new physical log name with Path'
>
>
> Change the database name and file names based on the backup and database
> name you have.
>
>
> Thanks
> Hari
> SQL Server MVP
>
> "Hemang Shah" wrote in message
> news:uwfcmkcuGHA.4460@TK2MSFTNGP04.phx.gbl...
>> Need help with moving a SQL Server Express 2005 database from one
>> computer to another.
>>
>> I used SSMSE to backup the database on the source server.
>>
>> Then on the target computer I created a new datbase with the same name
>> and tried to restore the backup - it failed saying this backup is not for
>> the current database.
>>
>> I also tried to restore without putting in a name so that I was hoping it
>> would create a new one - no luck.
>>
>> The question is how do I create a new database form the backup?
>>
>> Or what is the best method to use to copy a database from one computer to
>> the other.
>>
>> Thak You
>>
>>
>>
>
>

[Database Administrator]

Hello Hari

Thanks for the response.

This is the error I get:

Msg 3634, Level 16, State 1, Line 1

The operating system returned the error '5(Access is denied.)' while
attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program
Files\Microsoft SQL Server\Data\ClientManagement.mdf'.

Msg 3156, Level 16, State 5, Line 1

File 'ClientManagement_dat' cannot be restored to 'C:\Program
Files\Microsoft SQL Server\Data\ClientManagement.mdf'. Use WITH MOVE to
identify a valid location for the file.

Msg 3634, Level 16, State 1, Line 1

The operating system returned the error '5(Access is denied.)' while
attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program
Files\Microsoft SQL Server\Data\ClientManagement.ldf'.

Msg 3156, Level 16, State 5, Line 1

File 'ClientManagement_log' cannot be restored to 'C:\Program
Files\Microsoft SQL Server\Data\ClientManagement.ldf'. Use WITH MOVE to
identify a valid location for the file.

Msg 3119, Level 16, State 1, Line 1

Problems were identified while planning for the RESTORE statement. Previous
messages provide details.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.



This is the command I typed:



RESTORE DATABASE ClientManagement from disk='c:\ClientManagement.bak'

WITH move 'ClientManagement_dat'

to 'C:\Program Files\Microsoft SQL Server\Data\ClientManagement.mdf',

move 'ClientManagement_log'

to 'C:\Program Files\Microsoft SQL Server\Data\ClientManagement.ldf'





Thanks



"Hari Prasad" wrote in message
news:ec9CAEduGHA.5044@TK2MSFTNGP05.phx.gbl...
> Hi,
>
> From the Management studio,Query window execute below:-
>
> 1. Using Restore filelistonly command identify the logical file names of
> the database backup file
>
> RESTORE FILELISTONLY from disk='c:\x.bak'
>
> 2. With the output of the above query use RESTORE database
> RESTORE DATABASE from disk='c:\backup\x.bak'
> WITH move 'logical_mdf_filename' to 'new physical name with path',
> move 'logical_ldf_filename' to 'new physical log name with Path'
>
>
> Change the database name and file names based on the backup and database
> name you have.
>
>
> Thanks
> Hari
> SQL Server MVP
>
> "Hemang Shah" wrote in message
> news:uwfcmkcuGHA.4460@TK2MSFTNGP04.phx.gbl...
>> Need help with moving a SQL Server Express 2005 database from one
>> computer to another.
>>
>> I used SSMSE to backup the database on the source server.
>>
>> Then on the target computer I created a new datbase with the same name
>> and tried to restore the backup - it failed saying this backup is not for
>> the current database.
>>
>> I also tried to restore without putting in a name so that I was hoping it
>> would create a new one - no luck.
>>
>> The question is how do I create a new database form the backup?
>>
>> Or what is the best method to use to copy a database from one computer to
>> the other.
>>
>> Thak You
>>
>>
>>
>
>

[Database Administrator]

Hi,

Can create a new folder in C drive of the server machine named C:\MSSQL\Data\
and then point the MDF and LDF to this partcular folder. All you have to do
is

1. Create a folder in C Drive (mssql and inside that craete a subfolder data)
2. Change the Restore database command. In the MOVE part give the new
directory created.

Thanks
Hari
SQL Server MVP

Does

"Hemang Shah" wrote:

> Need help with moving a SQL Server Express 2005 database from one computer
> to another.
>
> I used SSMSE to backup the database on the source server.
>
> Then on the target computer I created a new datbase with the same name and
> tried to restore the backup - it failed saying this backup is not for the
> current database.
>
> I also tried to restore without putting in a name so that I was hoping it
> would create a new one - no luck.
>
> The question is how do I create a new database form the backup?
>
> Or what is the best method to use to copy a database from one computer to
> the other.
>
> Thak You
>
>
>
>

[Database Administrator]

Hi,

Can create a new folder in C drive of the server machine named C:\MSSQL\Data\
and then point the MDF and LDF to this partcular folder. All you have to do
is

1. Create a folder in C Drive (mssql and inside that craete a subfolder data)
2. Change the Restore database command. In the MOVE part give the new
directory created.

Thanks
Hari
SQL Server MVP

Does

"Hemang Shah" wrote:

> Need help with moving a SQL Server Express 2005 database from one computer
> to another.
>
> I used SSMSE to backup the database on the source server.
>
> Then on the target computer I created a new datbase with the same name and
> tried to restore the backup - it failed saying this backup is not for the
> current database.
>
> I also tried to restore without putting in a name so that I was hoping it
> would create a new one - no luck.
>
> The question is how do I create a new database form the backup?
>
> Or what is the best method to use to copy a database from one computer to
> the other.
>
> Thak You
>
>
>
>

[Database Administrator]

"Hemang Shah" wrote in message
news:uwfcmkcuGHA.4460@TK2MSFTNGP04.phx.gbl...
> Need help with moving a SQL Server Express 2005 database from one computer
> to another.

1) Detach database
2) Copy MDF & LDF to new location
3) Attach MDF & LDF to new server

[Database Administrator]

"Hemang Shah" wrote in message
news:uwfcmkcuGHA.4460@TK2MSFTNGP04.phx.gbl...
> Need help with moving a SQL Server Express 2005 database from one computer
> to another.

1) Detach database
2) Copy MDF & LDF to new location
3) Attach MDF & LDF to new server