Sql-server – some tips on setting up sql server database server

sql serversql-server-2012ssms

I am currently setting up a new SQL Server database server with databases. It is supplied by a third party company.

I need to request access for them around ports etc.

I would like to be able connect to the SQL Server instance on the above database from SQL Server Management Studio on my PC. What do I need to get setup on the server so I can get such access?

The other question I have is that I have a number of databases on other servers, I will like to "copy" these databases to the new server. I need to copy both structure and data. What is the best way to do such?

Best Answer

The standard SQL port is 1433. Though, the provider could be using another port. So, you'll need to ask them what port they're exposing.

Once you know the port, you can connect to the database instance using the following connection string (substituting placeholders with appropriate values):

"Data Source=111.222.333.444,1433;
Network Library=DBMSSOCN;
Initial Catalog=[databasename];
User ID=[username];Password=[password];"

Substitute 111.222.333.444,1433 with the appropriate ip address and port.

The other question I have is that I have a number of databases on other servers, I will like to "copy" these databases to the new server. I need to copy both structure and data. What is the best way to do such?

You can either backup and restore the database or you can generate scripts to create the schema and insert data. To generate the schema and data scripts go to:

object explorer => [right-click] database => select tasks => "generate scripts"

Then, under the "advanced options" make sure schema and data is selected for "Types of data to script":

enter image description here

Related Question