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
0
Add a comment
|
3 Answers
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
Comments
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.
4 Comments
Kate
How do I restore backup on other server by script or wizard? I don't want to copy backup file from one server to another manualy because of its size.
Thomas
@Kate - You would need to write a script to do it. If you do not want to copy the backup file, you would need to put it in a mutually accessible folder. The source server would drop the backup file into that folder and the destination server would restore from that folder.
Kate
Thomas: " The source server would drop the backup file into that folder and the destination server would restore from that folder" - And how do I get access to that folder?
Thomas
@Kate - It has to be a folder that is created that both servers can access. The source server must have write access to drop the backup file there and the destination server must have read access to read the backup from there to do the restore.
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'
