I'm trying to copy a database from my laptop to my web server over the Internet using a VPN.
After solving all the common well documented problems with getting this to work, it now fails on the last step, where its supposed to be executing the job. The event log on the server says it failed to connect to my laptop. ("Failed to connect to server xxx") The event log on the laptop says that the server failed to log on. If I use the sa account it says the login failed for user 'sa', and if I use Windows Authentication it says SSPI handshake failed with error code 0x8009030c ... connection has been closed.
Of course this makes no sense as the first 2 steps of the wizard test that the login can be performed, and the SQL Management studio on the server can connect to my laptop fine either way.
I just spent several hours getting the encrypted connection to work between my laptop and the server. The server is in texas, i'm in BC. I had to set up a VPN, edit the windows hosts file to map the computer names to the VPN ip's, export a certificate from the server to my laptop, and change the sql server to run as 'local service' instead of 'network service' just to get this far.
Isn't there an easier way to do this? Why can't I just create a database with the same name on the server and overwrite the database files with ones from my laptop. It would take about 30 seconds instead of 30 hours...
Could any of the other features of sql server help me with this? replication? subscriptions? linked servers? data export wizards?