CODEWITHSUNDEEP
sqlt-sqlsql-server-2008
Kate
Kate

Reputation: 775

Restore DB from one server to another by sql-script

Are there any way to restore 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

Upvotes: 1

Views: 3667

Answers (3)

Jason Clark
Jason Clark

Reputation: 1425

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'

Upvotes: 0

prav
prav

Reputation: 450

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

Upvotes: 2

Thomas
Thomas

Reputation: 64645

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.

Upvotes: 1

Related Questions