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?
al4629740Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
chapmandewConnect With a Mentor Commented:
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
 
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
 
al4629740Author Commented:
(BACKUP DATABASE statement)

what statement do I put in for example?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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.

All Courses

From novice to tech pro — start learning today.