SQL Server Management Studio – How to Quickly Clone a Database

Opening the SQL server to direct outside connections is an idea to avoid if you can - you are significantly increasing your visible surface area for attack and any communication between you and the server (aside from login credentials in the authentication phase, perhaps) will not be encrypted. If you must open the port, make sure it is open only to your fixed IP address(es) (assuming you have fixed addresses that are not shared by anyone else, i.e. you are not using an ISP that has you behind a NAT arrangement like most mobile internet providers).

A much better option would be to install a VPN setup (such as OpenVPN for instance) or a SSH service (Cygwin includes a port of the common standard OpenSSH which I fund currently runs well as a service under Windows 2003 and 2008) that you can tunnel a connection through. This way SSH or the VPN handle both authentication and encryption, and adding (assuming you have secure passwords/keys) a significant layer of protection that a directly open port would not have. The compression support they offer will help response times for any queries that return more than a small amount of data too. A SSHd based arrangement may be easier to setup, especially if you are familiar with SSH already of course, though most true VPNs (like OpenVPN) have the advantage that communication over them is more likely to survive short communication blackouts (like an ADSL router losing sync and reconnecting). The VPN option is also less hassle if you have multiple instances of SQL server running on the target machine or at the target site. I've used both methods successfully for a number of things, including talking to SQL Server using local versions of the standard tools and found them to work well (though my OpenVPN servers are all on Linux, but I'm told it works well as server on Windows too).

Connecting to the remote machine (in)directly like this can be very convenient, but be aware that even with a careful VPN setup you are increasing the amount of machines your SQL Server is exposed to so, especially if it is a production system with your user's real data in, make sure you take the time to ensure that the machines that can see the server over the VPN/tunnel are secure and uninfected themselves (you probably do this already, of course, but it is always worth restating!).

First, you need CREATE DATABASE permissions to create a database. The db_creator role may be best for you in this case. Second, you need to script the restore.

For example:

RESTORE DATABASE someDatabase
FROM DISK = N'D:\sql\backups\myDatabase.bak'
WITH STATS = 10 ; 
GO