Moving SQL Server from local computer to server

Question

I kind of new to SQL Server, I always used access db for my sites.

I created a SQL Server on my local computer and now I want to take this db and transfer it to the server. In access all I had to do is, take the mdb file and put it on the server and change the connection string. How can I transfer the SQL Server db to the server?

Is there any file to put on the server ?

Also the connection string isn't a folder but a local computer like this:

Data Source=my-PC;Initial Catalog=storeSQL1;User ID='my-PC\com';Password='';Trusted_Connection=YES;

Who can provide me this connection string for the server (the hosting company) ?

You can generate scripts with Management studio and run that on server database to create your db. You can add user and create password and use connection string similar to what you mentioned above — rs.
– rs., Commented Dec 22, 2011 at 15:14

Eric Petroelje · Accepted Answer · 2011-12-22 15:13:28Z

1

The easiest way would probably be to create a backup of the database on your local machine, then restore that backup on the new server.

answered Dec 22, 2011 at 15:13

Eric Petroelje

60.7k9 gold badges133 silver badges178 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

TehBoyan Over a year ago

+1 - In many cases this is the simplest and least time consuming

marc_s · Accepted Answer · 2011-12-22 15:46:43Z

1

Roadmap is:

Do simple backup-restore to move user databases to target server.
Create script on source server, that can recover permissions and login-users pairing
Restore the CLR and TRUSTWORTHY security for databases, that using unsafe assemblies, simpliest way is (in proper DB):
```
exec sp_changedbowner 'sa' --sa just for example

ALTER DATABASE dbname SET TRUSTWORTHY ON
```
Enjoy

edited Dec 22, 2011 at 15:46

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Dec 22, 2011 at 15:34

Oleg Dok

21.9k4 gold badges48 silver badges55 bronze badges

Comments

Robert · Accepted Answer · 2011-12-22 15:13:43Z

0

Depending on your version of SQL Server here is a good article that outlines all the ways to move a SQL Server Database.

http://blogs.msdn.com/b/sreekarm/archive/2009/09/11/move-a-database-from-one-server-to-another-server-in-sql-server-2008.aspx

As for getting the connection string yes the hosting company would provide you with that. Where is the database hosted, you could check their knowledge base articles or if it's an in house data base I'm sure a dba could provide you with that information. It won't change much from what you have but it will change.

answered Dec 22, 2011 at 15:13

Robert

3,0843 gold badges25 silver badges32 bronze badges

Comments

Donavan White · Accepted Answer · 2011-12-22 15:23:14Z

0

I'm not sure what tools your using, but to start you need to do a dump or backup of your current database on your machine. After you do that then you can do and import which should create all the tables and import any data you have.

After the data exists on the server then as far as the connection string, you just need to say the Data Source is the server ip address or host name and change your User ID and Pass to match that server.

If you need more details on any part of this process, post what tools your using and what your environment looks like and I would be more than happy to assist you.

answered Dec 22, 2011 at 15:23

Donavan White

1,12610 silver badges23 bronze badges

Comments

Michal Barcik · Accepted Answer · 2011-12-22 15:33:42Z

0

In my opinion the best way to do that is to detach the db from one server(pc), copy the files to the second one and then attach them on the second server/pc.

To detach:

USE master;
GO
EXEC sp_detach_db @dbname = N'AdventureWorks2008R2';
GO

To attach:

USE master;
GO
CREATE DATABASE MyAdventureWorks 
    ON (FILENAME = 'C:\MySQLServer\AdventureWorks2008R2_Data.mdf'),
    (FILENAME = 'C:\MySQLServer\AdventureWorks2008R2_Log.ldf')
    FOR ATTACH;
GO

answered Dec 22, 2011 at 15:33

Michal Barcik

6624 silver badges6 bronze badges

Collectives™ on Stack Overflow

Moving SQL Server from local computer to server

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related