Copy DB tables and stored procedures to a new DB with different name

Hello all!

What I need to do is take a site that I have and make 3 copies of it so I will have 4 separate sites with 4 separate DB's but running on the same server. One of the sites is complete but what I need to know is how do I make a complete copy of the DB including all stored procedures and populate a blank DB that has a different name with contents from the master DB?

So if DB1 is complete and I want to now populate DB2, DB3 and DB4 with everything from DB1 (tables, stored procedures, data etc.) what would be the best way to do this?

The new sites are already setup in IIS and I have already transferred the files to each sites root, so all that is left is to setup the new DB's. They are all running on the same server… I think that is about everything someone would need to know to help me!

Any help would be greatly appreciated!!!

Hello,

1) Create empty dbs

db2,3,4

2) Right click on db1 and select Generate SQL script

3) Generate the script for tables,sp,views....

4) Open your query analyzer

Now, you have to do the following for each db

Use db2
-paste the sql script you did generate in step 3

Run it

Repeat step 4 for all your db.

Hope that helps
Regards

|||

That is easy use the Backup and Restore wizard use the restore from device option it will ask you for a new name just add the name and you will be done in a few minutes. Known issues orphaned permissions just delete the restore and create a new one. If you have more questions post again. Hope this helps.

|||

Hello

Is there another way so I can copy tables and procedures to a new DB , without any manual thing "by coding??"

Thank you

sqlsql

Copy Access Table to SQL

Exporting an Access table and saving it as a DTS package copies the table but
loses it's column sorting. In the Access table, the "Main Number" is sorted
as ascending, but dropping and copying the table via DTS distorts the sort
and does not contain a sort sequence, even by ID. What causes this and how
can I correct this?
Thanks, PIn SQL Server you don't have to worry about how the data is stored. All you
have to do is to, specify an ORDER BY clause in your queries, to get the
data out in your desired sort order.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Petra" <Petra@.discussions.microsoft.com> wrote in message
news:09506D78-2CFC-4482-AD02-2000131510A2@.microsoft.com...
Exporting an Access table and saving it as a DTS package copies the table
but
loses it's column sorting. In the Access table, the "Main Number" is sorted
as ascending, but dropping and copying the table via DTS distorts the sort
and does not contain a sort sequence, even by ID. What causes this and how
can I correct this?
Thanks, P

Copy Access Table to SQL

Exporting an Access table and saving it as a DTS package copies the table but
loses it's column sorting. In the Access table, the "Main Number" is sorted
as ascending, but dropping and copying the table via DTS distorts the sort
and does not contain a sort sequence, even by ID. What causes this and how
can I correct this?
Thanks, P
In SQL Server you don't have to worry about how the data is stored. All you
have to do is to, specify an ORDER BY clause in your queries, to get the
data out in your desired sort order.
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Petra" <Petra@.discussions.microsoft.com> wrote in message
news:09506D78-2CFC-4482-AD02-2000131510A2@.microsoft.com...
Exporting an Access table and saving it as a DTS package copies the table
but
loses it's column sorting. In the Access table, the "Main Number" is sorted
as ascending, but dropping and copying the table via DTS distorts the sort
and does not contain a sort sequence, even by ID. What causes this and how
can I correct this?
Thanks, P

Copies over links

Hi if I have a backup file in a remote 'US' location of
3gb and I use getright or wget to copy this to a location
in london I want to now add another location Ireland to
copy this file to, will this extend the time of the copy
to london if I want to copy this data to both locations at
the same time. Both locations have separate links to the US
Thanks for any advice
Deanny
Deanny,
as you have separate links, there isn't bandwidth contention. However, there
will be disk useage contention unless you're writing to separate disks which
have separate disk controllers.
HTH,
Paul Ibison

Copied Package ConnectionManager has photographic memory?

I originally made 1 package, got it the way I wanted with configurations,etc, and then made 30 copies of it, renaming, etc.

The original package (and the copies) has 2 connections. Connection A is set at runtime by a configuration from an XML file. Connection B is set from a SQL Server lookup from Connection A.

I'm now trying to run one of those copied packages. As I mentioned, in my package I have 2 connections. I changed one of my connections, save the package, go back and look at the connection, and the initial catalog has changed. Life is good, right? Wrong. I close the package, go back in, and the initial catalog is back to the original. If I look at the package's XML in notepad, I see the correct new connection manager. So where is it picking up this info from?

Interestingly, if I delete the connection, add a new connection, rename it to the original connection I had (so my configuration will still be good), save it, close it, go back in, it still reverts back to that original connection.

Whats the deal? Its almost like I have to name my connections differently across packages?Check if it is configured in the configuration settings
(in this case it is loaded from config provider) or
maybe in the property expressions (in which case it
is calculated).|||wow. I must be burnt out. I see the problem. User error. thanks