CODEWITHSUNDEEP
sql-serverdatabase
magol
magol

Reputation: 6273

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

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.

Upvotes: 0

Views: 6569

Answers (4)

magol
magol

Reputation: 6273

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

Upvotes: 0

Cade Roux
Cade Roux

Reputation: 89711

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.

Upvotes: -1

p.campbell
p.campbell

Reputation: 100607

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.

Upvotes: 2

Kendrick
Kendrick

Reputation: 3787

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.

Upvotes: 2

Related Questions