April 2, 2003 at 1:33 pm
Does anyone know of a way to copy all of the tables from a database to another database on the same server?
April 2, 2003 at 1:35 pm
hmmm DTS - one way
John Zacharkan
John Zacharkan
April 2, 2003 at 1:39 pm
Backup and restore. change db name in restore and select from device. change physical file name/location in options tab.
April 2, 2003 at 4:42 pm
quote:
hmmm DTS - one wayJohn Zacharkan
April 2, 2003 at 4:44 pm
I don't think DTS will let me copy to the same server, unless I am missing something.
April 2, 2003 at 5:10 pm
I'll send you an example in the morning.
quote:
I don't think DTS will let me copy to the same server, unless I am missing something.
John Zacharkan
John Zacharkan
April 3, 2003 at 7:06 am
DTS would surely let you copy the database.
You CANNOT use "Transfer Database Task".
But you can use "Copy SQL Server Objects Task". You can either append the data or Replace the data using this.
John,
If there is some oher way, please let us know.
Regards,
Murali Damera.
.
April 3, 2003 at 7:07 am
If you use DBArtisan, You can do this very well with Migrate option.
.
April 8, 2003 at 8:19 am
DTS is your best and most simple option
April 9, 2003 at 10:58 pm
Won't DTS only create the tables and populate them? What about indexes and keys constraints and trigger? (unless you script that first) Guess I'd have to know what "copy all tables" meant, and if the tables need to go to an existing Database before I'd know the right answer
Edited by - jefffoyal on 04/09/2003 10:59:46 PM
April 10, 2003 at 4:41 am
Actually I prefer the method of backup and restore to new database. This guarantees exact copy of the database, objects, permissions, the works just like the copy database objects in DTS, but the scripting that occurrs can be a little slower than a restore. Once done however, if you make no structure changes I would use DT to repop the data.
April 10, 2003 at 6:50 am
Use the IMPORT/Export featrue in SQL Server 2000.
1) First the "to" database needs to exist.
2) From "all tasks" select export
3) Choose your source/destincation
4) Copy objects and data between SQL Server database
5) From there, you select the objects and options you need to make a duplicate copy.
I would not recommend this for a database greater than 200-300meg. It can take quite a while.
I personally feel that the backup (to device) and restore is a simpler and quicker method for the size of databases we have.
Joseph
April 10, 2003 at 7:38 am
I agree with antares. The backup/restore method has become our standard way of copying a database at my company. Not only is it easy, but it seems to run much faster than DTS.
April 10, 2003 at 7:43 am
Thanks to all for the help. I will try both approaches to see which will satisfy my requirements best.
April 10, 2003 at 8:04 am
The database backup method works great, gets everything and is fairly simple.
Although there is a couple of things you must consider.
First using the database backups will also copy database users. If logins associated with the users are not defined to the target server then you will end up with orphan users. Even if they are defined, and the database user name is diffenent then than the login name then they still will end up as orphan database users.
Secondly you need to consider the column and database collation settings. If your source database and your target server/databases have different collations settings then you may run into issues. I've found trying to use TEMPDB that has one collation, with a users databases using a different collation occassionally cause problems. I make sure all server databases have the same collation to avoid collation errors.
Good luck and copy away.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply