RESTORE DB FROM ANOTHER SERVER BACKUP

[password99]

I need to restore db to a server other than the one from which the backup file was made. both the server have identical db, users, security etc. The only different thing. I would like to get the data from the other server DB to this server DB.

PLEASE HELP

So here is what I did:
ALTER DATABASE testdb SET SINGLE_USER WITH ROLLBACK IMMEDIATE
use master
RESTORE DATABASE testdb FROM DISK = 'H:\test' WITH REPLACE
ALTER DATABASE testdb SET MULTI_USER

I get these errors:
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Server: Msg 3270, Level 16, State 1, Line 3
An internal consistency error occurred. Contact Technical Support for assistance.
Server: Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.
Server: Msg 927, Level 14, State 2, Line 4
Database 'testdb' cannot be opened. It is in the middle of a restore.
Server: Msg 5069, Level 16, State 1, Line 4
ALTER DATABASE statement failed.

 

[password99]

I do not have access to Enterprise Manager. I need to do everything using Transact-SQL.

 

[mutley1]

Hi password,

I assume you're getting a backup (.BAK file) then copying it over to the other server. If you are, try the following. Make sure noone is in the db or connected to it though.

What i have put in for the logical and physical names are just best guess at what yours are called, so check them first on the original server and amend accordingly.

RESTORE DATABASE testdb
FROM DISK = 'C:\where you saved the backup file\testdb.BAK'
WITH RECOVERY, REPLACE, NOUNLOAD,
MOVE 'testdb_data' TO 'd:\physical data file location\testdb_data.mdf',
MOVE 'testdb_log' TO 'd:\physical data file location\testdb_log.ldf'

Mutley