Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

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.

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