Trying to copy a database

Question

Trying to copy a database

Ray K

SSC-Dedicated

Points: 31163
More actions
January 24, 2011 at 8:33 am

#388700
Hey guys . . . I'm trying to copy databases from our host onto our local server(s).
Here's the scenario:
- The database in question is hosted by a provider on a SQL 2005 server.
- I'm trying to copy it locally to my own machine, where I'm running SQL 2008.
- The source database is running production data, so I can't afford to bring it offline.
- Because the source server isn't ours, I don't have full access to it. I do, however, have SA access to our data. Also, if this helps any, I am able to build tables and stored procedures on the source server. I'm not entirely sure what other permissions I have on the source server.
- For the same reason, I'm very leery about doing this via backup/restore.
- I tried using the Copy Database wizard, and got the following error: "An exception occurred while executing a Transact-SQL statement or batch: xp_regread() returned error 5, 'Access is denied.' (Microsoft SQL Server, Error: 22001)" I'm guessing that this is because of my limited access to the source server.
- If it's at all possible, I'd like to avoid doing this by recreating all the tables locally and copying the data over. (I can do that, but am hoping to save myself a great deal of effort.)
So, what would be the easiest way to go about this? Anyone have any suggestions?
Thanks in advance!
+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

I'd try doing a copy-only backup first. If that fails, then it'll probably be a matter of table-by-table data copying. SSMS for SQL 2008 has features for scripting both structure and data for tables, if the database is small enough for that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Ray K SSC-Dedicated Points: 31163 More actions · Answer 2

Thanks for the assist as always, Gus. I'll try the copy-only option and see what happens.

If that fails, then it'll probably be a matter of table-by-table data copying.

I was kinda afraid you'd say that! Fortunately, the database isn't huge, so this is an option, but like I said, I'm hoping to save myself some effort!

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 3

Do you have Visual Studio?

My edition allows me to do a project which scripts out all the objects.

VSTS Database Edition.

Or maybe a trial of a similar tool can work. ;>)

Greg E

Ray K SSC-Dedicated Points: 31163 More actions · Answer 4

Greg Edwards-268690 (1/24/2011)
Do you have Visual Studio?
My edition allows me to do a project which scripts out all the objects.
VSTS Database Edition.
Or maybe a trial of a similar tool can work. ;>)
Greg E

Hmmm, that's true. I didn't even think about that!

In any case, I've already started scripting through SSMS! So far, it seems to be going okay.

Oh well!

+--------------------------------------------------------------------------------------+
Check out my blog at https://pianorayk.wordpress.com/

Greg Edwards-268690 SSC-Insane Points: 20680 More actions · Answer 5

VSTS just automates it, and gets all the objects.

Maybe a bit more important with hundreds of tables.

You didn't say why you were doing this, but the tool can also help manange change from dev to prod for example.

Greg E