Restoring an SQL Database to a different server

[barrycork]

I am trying to copy a database to another server using the SQL Backup & Restore option. To do this I perform a 'Database - Complete' backup to file from enterprise manager.

I am unsure of the next step to perform on the new server. Do I simply create a new empty database and perform the restore option to this database from Enterprise Manager unsing the backup file created in step 1?

I am new to SQL Admin therefore I am keen to know how I can restore my backups to a different machine should I suffer a failure on the live server.

Thanks,

Barry

 

[btturner]

From source server/db query analyzer:

>> BACKUP DATABASE your_dbname TO DISK='c:\your_dbname.bak'

From target server/master db query analyzer:

>> RESTORE DATABASE your_dbname FROM DISK='c:\your_dbname.bak' WITH RECOVERY, REPLACE

 

[barrycork]

Thanks btturner.

I created the backup file from the source server using the Backup Database option in Enterprise Manager. I Take it this does the same as you Query Analyzer command above.

On target server I then used the query you supplied above however I get;

Server: Msg 5105, Level 16, State 2, Line 1
Device activation error. The physical file name 'D:\Microsoft SQL Server\MSSQL\data\GoldMine.mdf' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'GoldMine' cannot be restored to 'D:\Microsoft SQL Server\MSSQL\data\GoldMine.mdf'. Use WITH MOVE to identify a valid location for the file.
Server: Msg 5105, Level 16, State 1, Line 1
Device activation error. The physical file name 'D:\Microsoft SQL Server\MSSQL\data\GoldMine_log.LDF' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'GoldMine_log' cannot be restored to 'D:\Microsoft SQL Server\MSSQL\data\GoldMine_log.LDF'. Use WITH MOVE to identify a valid location for the file.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Do you have any suggestion?

 

[barrycork]

I got it to work. I had to create an empty database first and then ensure that the log file and mdf files were created with the same file names as on the source server. All now seems to be workin fine on the new server.

Thanks,

Barry