Copy a database from SQL Server on a webserver to local SQL Server Express instance

Question

I have taken over the administration of a website, but I have trouble getting the database to my test computer. I can access the database (MS SQL) via myLittleAdmin. On my test computer, I have MS SQL Express, and I wonder how I should do to copy the database.

Edit

The web hotel say explicitly that it can not replicate the database through SQL Server Management Studio. I have not find any way to connect to the database external.

The only way I have come to is to take a backup of the database in myLittleAdmin, but how then to use it I do not know.

If you can make a backup of it from the tool, I assume you can then download the backup? If so, you should be able to attach that backup to your local SQL Server instance... — Kendrick
– Kendrick, Commented Apr 14, 2011 at 15:25

Kendrick · Accepted Answer · 2011-04-12 20:53:56Z

2

If

it's not too large
you're dealing with firewall issues or other corporate nonsense
you only need to do it once, or very occasionally

Then copy and paste the data in to MS SQL Express using MS SQl Management Studio

It's crude and extremely slow, but it works. You can use MS Excel as an intermediary as well if you need to clean up any data...

Now I need to go shower.

answered Apr 12, 2011 at 20:53

Kendrick

3,7871 gold badge25 silver badges43 bronze badges

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

1 Comment

Kendrick Over a year ago

If anyone else wants to downvote this, at least man up and say why you're doing it. I think I made it clear that this is not best option, but in cases where there are no other options, this may be a valid solution. Why downvote an actual answer and pass over what should have been a comment and an answer that, while the "right way to do it" ignores the implied limitations of the issue?

p.campbell · Accepted Answer · 2011-04-12 21:29:12Z

2

Use SQL Server Management Studio (Express) to connect to the remote database. You can then copy the database.

right click the source database. Tasks-> Copy Database
choose your method of copy - SQL MO or detach (whichever appropriate for you, given the 'detach' will leave the DB unavailable for a short period of time)

Alternatively, you could script the CREATE of all the tables, views, stored procs, and data within the tables.

right click the source database. Tasks -> Generate Scripts
choose which objects to script.
carefully choose the options (users, logins, indexes, FK, and Scripts Only and/or Data)

If you absolutely cannot use/install SSMS on your SQL Server Express destination machine, consider spinning up a new development VM (VirtualBox, VMWare, etc), and install SSMS.

edited Apr 12, 2011 at 21:29

answered Apr 12, 2011 at 20:55

p.campbell

101k70 gold badges264 silver badges326 bronze badges

5 Comments

Joel Coehoorn Over a year ago

Note that the "detatch" method requires downtime, but it's also a lot faster and more reliable.

Kendrick Over a year ago

I made the assumption that he can't connect to the database because he implies he can only connect to the database via myLittleAdmin. If that's not the case, then this method is decidedly less awful.

magol Over a year ago

Unfortunately I can not use SQL Server Management Studio

p.campbell Over a year ago

@magol: why not use SSMS? Your destination machine is running SQL Server, so it will run SSMS. If not that, how about a new VM where you can load the tool? My Option 2 above will work and doesn't require 'replication', which is an entirely different approach. Replication != SSMS scripting.

magol Over a year ago

I dont now whay I dont can use SSMS, but the support for the web hotel say that I cant. How can a Vm solve the problem? I dont find any Generate Scripts in myLittleAdmin

magol · Accepted Answer · 2011-04-16 17:02:16Z

0

I talked to support and what I had to do was to set up an SSH tunnel. Then I could use the SQL Server Management Studio as usual

answered Apr 16, 2011 at 17:02

magol

6,28318 gold badges70 silver badges123 bronze badges

Comments

Cade Roux · Accepted Answer · 2011-04-12 21:32:03Z

-1

Where does MySQL come in? Normally, I would say backup and restore, but if you only have myLittleAdmin access, I'm not sure you can get that file out.

Might be easier to simply script all the schema creation and data inserts, too.

Despite the downvote, let me add that I have actually recently done this for a database of a prototype system I consulted to take to completion - the SQL Server was hosted and we even had SSMS access. But we could not back up and restore because we could not add a backup device to a machine outside of the hosting service or backup to a file we could get to on the hosting service.

edited Apr 12, 2011 at 21:32

answered Apr 12, 2011 at 20:53

Cade Roux

90.1k42 gold badges186 silver badges268 bronze badges

Collectives™ on Stack Overflow

Copy a database from SQL Server on a webserver to local SQL Server Express instance

4 Answers 4

1 Comment

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related