How do I "Install" a SQL Server Database to a Remote Server Without Physical Access?

Question

I have a brand new database on an ISP which I intend to use to build an ASP.NET data-driven website. However, I would like to build the whole thing locally and transplant the database to the ISP.

I have TCP/IP access (i.e. I can connect to the remote database directly thru SQL Server Management Studio Express), but I do not have Terminal Services access, as this is a database shared with other users of the ISP.

Is there a simple way to "replicate" my local development database to the remote server?

Answer 1

One way is to use something like the Database Publishing Wizard to create an SQL script, and then run that script in SQL Server Management studio in the remote database.

Apparently this is integrated in Visual Studio 2008 (didn't know that before).

Answer 2

I just had a similar problem. I downloaded and used the SQL Server Pulishing Wizard. Can be found here. You end up with a single script file that you apply to your database and it creates everything including the data.

Answer 3

You could just backup the database on your local server and restore it onto the target server. This'd be easier if the collation of the two servers were the same, but even that's not a show-stopper. Scripting the database is fine while it's empty, but it gets trickier if you want to move it back and forth once it has data in it.

Answer 4

How about putting your database in the App_Data folder? You can create it straight from Visual Studio. It should then auto-mount. Then you should be able to just copy it over with the rest of the files.

Answer 5

Assuming you have FTP access, upload a "COPY ONLY" backup of the database to the server and then use Management Studio express to restore the backup and browse to the file and restore it on the server. Should work ok if the SQL version is compatable.