| Author |
Topic |
|
ujjaval
Posting Yak Master
108 Posts |
 Posted - 2006-09-14 : 03:52:45
|
| Hi,How can I create exact copy of database as a new another database on the same server in Sql Server 7.0? Is there any command for that?I tried in Enterprise Manager using Export Data, but gave me error with invalid columns numbers or something and I don't understand why?Thanks,Ujjaval |
|
|
ujjaval
Posting Yak Master
108 Posts |
Posted - 2006-09-14 : 04:14:56
|
| Just noting down the exact error that I'm getting.It is:"[Microsoft][ODBC SQL Server Driver][SQL Server]Insert Error: Column name or number of supplied values does not match table definition."Thanks,Ujjaval |
 |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-09-14 : 04:42:02
|
Can't you use backup & restore ?
KH |
|
|
|
ujjaval
Posting Yak Master
108 Posts |
Posted - 2006-09-14 : 04:45:40
|
| no actually I can't. I have a database called 'testv1_0'. I took its backup on 'testv1_0backup.bak' file.then using Restore Database option, I mentioned restor database as 'testWH' and selected option to overwrite any existing files and gave that back up file created above. I get error message that .MDF file is not valid because it takes by default that file for testv1_0 database. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-14 : 07:22:29
|
| You can restore it to a differently-named database, and "MOVE" the physical file to a different location/name [compared to the original] - and whilst you are at it it would be prudent to change the logical names of the files - so that they are different to the original!All this is, IMHO, extremely difficult to fathom using Enterprise Manager's Restore screen. For syntax of doing this in Query Anaylser seehttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=RESTORE%20syntax%20/%20example,RestoreKristen |
|
|
|
anilkdanta
Starting Member
25 Posts |
Posted - 2006-09-21 : 07:35:56
|
| Take backup of XYZ database and restore it on different database on the same Server is not possible. Because the backup file internally saves the XYZ name and expects the same name while restoring.Generate SQL script for all the objects - Tables, SPs, Views, UDFs along with primary key, foriegn key, index definitions.1) Create a Empty database with a name ABC2) Run the Create Table scripts3) Create Index statements4) Using a DTS package export all data to tables. Or use Export wizard.5) Run primar key, foreign key scripts.6) Run SPs, Views, UDFs script.Now a new database same as XYZ is ready with ABC name. |
|
|
|
DMcCallie
Yak Posting Veteran
62 Posts |
Posted - 2006-09-21 : 10:36:32
|
| Detach the DB, copy the .MDF and the .LDF to another directory. Rename the copied containers and then move them back to the Data directory. Attach the original DB. Attach the renamed containers and specify a new name. (hint: sp_attach_db is your friend)EXEC sp_attach_db @dbname = N'pubs', @filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf', @filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf' |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-09-21 : 11:22:03
|
| "Take backup of XYZ database and restore it on different database on the same Server is not possible"This is NOT true. You just need to use the MOVE option of RESTORE to change the target location during the restore."Detach the DB, copy the .MDF and the .LDF to ..."Won't this leave the internal/logical names the same?, which is probably not a good idea for two databases on the same server! Note also that 1) you are taking the database offline for this process which is a bad idea for a production database! and 2) you should have a backup before you use DETACH in case the database fails to re-attach for any reason .... in which case I reckon you might as well restore rather than detach, copy and reattach two databases!Kristen |
|
|
|
DMcCallie
Yak Posting Veteran
62 Posts |
Posted - 2006-09-21 : 12:55:31
|
| Yes I have restored databases with a new name quite a few times on the same server in SQL7. It will work. I just pointed out another way to skin this cat...DeWayne |
|
|
|
|
copy database on same server