Copy db from one Server to Another

[CEG]

Please Help!

I have a Server Mars1 which has active databases, users logged on, reports request, etc.

Have been given another Server Mars2 on which to create a test bed environment.

Created an empty db on Mars2

Copied the Backup file (.BAK) from Mars1, to Mssql/Backup on Mars2

Message appears Device activation error. Use with Move to identify a valid location for the file.

Thanking you in anticipation.

 

[AWithers]

I think you should look at Data Transformation Services (DTS) in Enterprise Manager to create a copy of a database on Mars2 based on Mars1

The wizard is easy to use and you can choose various options e.g. to transfer all data as well as the database schema

Andy

 

[CEG]

Thanks Andy,

I have tried this, but it just does not complete. Is this because the db is updated using the Intranet, so there is always a job running?

Thanks

 

[Jakkels]

The problem is with the file locations for the .ldf and .mdf files. The path from the old server will be the default path for the new restore. You must change it to reflect this.

I.E On the old server
Data file = e:\MSSQL\Data\MyDB.mdf
Log file = e:\MSSQL\Log\MyDB_Log.ldf

New Server may not have an E drive. You must change the paths in the Options Tab of the Restore Database window. It will look some thing like this

MOVE TO PHYSICAL FILE NAME
c:\MSSQL7\DATA\MyDB.dbf

Change this to reflect a correct path.

I have this all the time when I am not concentrating

 

[CEG]

Thanks for your help.

DB is now functional.