• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 218
  • Last Modified:

backup one sql server db to another sql server

I have two SQL servers at two remote locations.  Two different IP address locations to be exact.  How can I back up on db to the other either through SQL or through using VB6?
0
al4629740
Asked:
al4629740
  • 5
  • 4
1 Solution
 
elimesikaCommented:
HI

lets mark DBs ar dbsource and dbtarget

Follow :

1)  from MSMS on source machine detach dbsource
2) from MSMS on target machine detach dbtarget

3) copy db file + log from source machine to target machine using ftp (backup original files)

4) attach dbtarget

5) attach  dbsource
0
 
chapmandewCommented:
Actually, in this case you do not want to detach and attach, since you're not moving them...and you likely want to keep your source server online.  Do this instead:

1.  backup the database that you want to copy to a file.  (BACKUP DATABASE statement)
2.  Copy that backup file (.bak) to the server you want to move it to
3.  Restore the database that you just copied to that server (RESTORE DATABASE statement)
0
 
al4629740Author Commented:
(BACKUP DATABASE statement)

what statement do I put in for example?
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
chapmandewCommented:
backup database yourdatabasename
to disk = 'c:\pathtowhereyouwanttoputbackupfile\backup.bak'
0
 
al4629740Author Commented:
If I was going to restore it over the internet to another location (sql server) how would I then restore it? example?
0
 
chapmandewCommented:
backup database yourdatabasename
to disk = \\servername\sharedpath\backup.bak'
0
 
al4629740Author Commented:
My target server is behind a router, how does this happen?  I access it via a connection string through port 1433.

x.x.x.x:1433
0
 
al4629740Author Commented:
I get this error
Msg 3201, Level 16, State 1, Line 1
Cannot open backup device 'C:\BackupIllinoisDB\backup.bak'. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
0
 
chapmandewCommented:
Did you copy your backup to the server so it is actually on the C: drive of the server that you want to restore it to?
0
 
al4629740Author Commented:
it was backed up to a backup folder, the same C drive as the source.  For testing right now.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now