Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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?

share|improve this question
This is pretty broad, and there's a lot of SQL Server info out there. Check out brentozar.com for many specifics on setting up new servers. As for copying databases, you'll just want to use BACKUP and RESTORE. – Matt Feb 27 at 1:21

migrated from stackoverflow.com Feb 27 at 9:50

2 Answers

up vote 2 down vote accepted

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 got to:

[navigate to] object explorer >
[right-click] database >
[click] tasks >
[click] generate scripts

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

enter image description here

share|improve this answer
up vote 1 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.