Restore DB from one server to another by sql-script

Are there any way to resto开发者_StackOverflow社区re db from one server to another, if I know other server IP address? Can I do it by sql-script or some wizard? I use MS Sql Server Managment Studio 2008

TSQL script as

USE DATABASE -- TO TAKE BACKUP

GO

BACKUP DATABASE XXX -- XXX is database backup name

TO DISK = '\\\YYYY\XXX.BAK' -- YYYY is the shared folder to your backup and restore. Servers need access permissions on the folder as shared to available for both servers.

GO

USE MASTER

RESTORE DATABASE XXX

FROM DISK = '\\\YYYY\XXX.BAK'

GO

thanks prav

As far as I know, you must do this in a two step process: create a backup file from the source database server, use the backup file to restore onto the target server. You can script the backup and restore presuming that one server can talk to the other, the destination server could (assuming the appropriate permissions), fire off a backup to an accessible location and then restore from that file.

you can restore your database from one server to another server by executing below script

RESTORE DATABASE mydb
FROM DISK='d:\mydb.bak'
WITH
MOVE 'mydb' TO 'D:\TSQL\mydb.mdf',
MOVE 'mydb_log' TO 'D:\TSQL\mydb_log.ldf'