Copy Microsoft SQL Server 2008 DB using connectionString values

With a connection string you can access the DATA in that SQL Server Database. In order for you to copy the entire database you need to either back it up and restore, or detach it and attach it in your local server.

What tool should I use?
on my local PC I have SQL Server Enterprise Manager.
I do not have a remote desktop access to the remote computer
but again only db_url, Initial Catalog, User ID and Password information.

If you can access the data, you can do this (although it's not a copy of the entire database, only tables and data):

- Create a database with the same name
- Create a linked server to the remote server, from your local server
- Use SELECT INTO statements to create tables with the data on the remote server

This is only an option, and probably not the best one. But you will at least have all the data with the same table names (although I'm not sure about datatypes for each field).

mwvisa1, how do I run the Copy Database Wizard?

Caveat: it requires SQL Agent, so if you are running Express edition locally, this will not work.

mwvisa1, this operation does NOT copy the Stored Procedures.. does it?
if so, how can it be done? (using the SQL ConnectionString values..)
Thanks. Aryeh.

tuchfeld, as stated in the documentation, copying of user-defined procedures is part of the functionality. In fact I just copied another database just now and carried over procedures successfully. If the procedures you are missing are shared ones in master database, then take a look at this Article inspired by your question:

https://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/A_7251-Database-Copying-Wizardry.html

Section 6, Select Additional Objects, shows where you can choose to bring over additional objects.

Aryeh,

If anything and the database copies, leaving only the stored procedures behind, you can click on procedures and select Script As and via connection to both databases in SSMS, you can quickly take scripts from one side and run to create procedures in the other.

Regards,
Kevin

Please see attached image.
I snapshot the important steps in the wizard.
What is wrong?
Thanks, Aryeh.

It might be SQL Server verssion 2005 (local), 2008 (remote) problem.. I need to check.

That is the DTS Import/Export Wizard and not Copy Database Wizard. Please see my Article for how to launch CDW. If you already have the destination database and are copying objects using DTS, then that is a different story.

Oh, ok, as you pointed in your article http://msdn.microsoft.com/en-us/library/ms188664.aspx
To open CDW: In SQL Server Management Studio, in Object Explorer, expand Databases, right-click a database, point to Tasks, and then click Copy Database.

But, as seen in the attached image
I get different window and not as yours CDW-03a-OfflineMethod.png
why is that?

As you can see a top the dialog, you are using the SQL Server Import/Export Wizard instead. Please read my Article again and do exactly as instructed there. I just tried this on SQL Server 2005 to double check I didn't miss a version difference and the screens appear in the same exact order; therefore, I just suspect you are clicking Import versus Copy Database...

I mean Figure 3a in your article.

Doesn't matter. Look at your pictures here. They are for Import Wizard, not Copy Database Wizard as shown in my illustrations. They will NOT look the same because they are different tools.

Ok, I'm folowing you now running the Copy Database (and not Import Data).
I get your figure 2b
(I do not have Express)
I press Yes to continue
and get error as can be seen in the attached image.

First, the prompt on you clicked "Yes" on will come back to be an error. Take note from article. SQL Server Agent needs to be running on the destination server.

As for the error, it appears to be regarding your credentials. Does your account have permissions?

You have been more that Excellent ! Thanks for now. I'll need to check on these on the remote server..

Good luck!
Regards, Kevin