some tips on setting up sql server database server

Posted on

Question :

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?

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

Use BACKUP / RESTORE to copy schema and data. Some folks might suggest other things like DETACH / ATTACH or set offline & copy. File-based copies with mdf/ldf files are dangerous because, if something goes wrong with the files while the database is detached/offline, now you have zero copies of your database. If you’re going to protect yourself properly (by taking a backup first), you may as well just use that backup instead of bothering to take the database offline in any way.

Leave a Reply

Your email address will not be published. Required fields are marked *