Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Andyg
Starting Member
3 Posts |
 Posted - 2003-05-28 : 11:07:31
|
| Here is the issue...Normally if I'm required to do an import or export I have the process create the database from scratch. Because they create an empty database for me. The constraints such as the primary or foreign keys or default values don't get transferred for some reason. What I did was after the import I went tot the original databases to generate the SQL on those constraints and then ran them from query analyzer on the new databases on the new server. This appeared to have worked although I'm- not user if this is the recommended practice...(please let me know....Now the issue I'm having is the (up numbers) or refereed to in SQL server as Identity columns....where the numbers get automatically implemented whenever a new record is created. These columns are not set in the new tables. I talked to the DBA from our new service provider (who wasn't very helpful)... explained to me that this was actually normal, and that I'll have to implement the Identity columns manually ....I find that hard to believe , if I have 50k tables I have to go through to manually reset the identity columns so that the value will append by one each time seems very high maintenance...he mentioned that I probably can run a script to do it.Can you advise on how I can approach this? The new Provider were a week late in getting this server ready for us and I know have until today to get everything up and running.Thanks for any help you can provide...P.S I also tried doing a backup..ftping that backup and then restoring the files and still had to run the scripts for the constraints...still don't see a way to implement the identity columns. |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2003-05-28 : 11:43:49
|
| You should probably either script out your tables, or restore a backup. Otherwise, you have to alter each table to add the identity property onto the column. If you simply restore a backup, your database should be an exact replica, you shouldn't need to add any constraints, keys or identities (As long as the source had these things when the backup was made).-Chadhttp://www.clrsoft.comSoftware built for the Common Language Runtime. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-28 : 13:45:56
|
| You do not want to script out the identity column thing since there are 50k tables. You need to check the source database to see if the identity column has been implemented there and also if the constraints have been created there. If they have, then a backup and restore will fix your problem. If they aren't, then still do the backup and restore and then apply the constraints. The identity column thing is going to give you lots of headaches. To add an identity column, you need to first create a temporary table that has the same structure as the other table but with the identity column added, then you'll need to transfer your data from the other table into the temporary table. Then rename the temporary table to the other table. Then copy over all of your constraints. To write a script to do this is going to be very hard. You need to get some support from your service provider on this.Tara |
|
|
|
Andyg
Starting Member
3 Posts |
Posted - 2003-05-28 : 14:46:36
|
| Thanks for the advice guys...everything worked out good |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-05-28 : 20:23:09
|
| What did you end up doing to fix this?Tara |
|
|
|
Andyg
Starting Member
3 Posts |
Posted - 2003-05-29 : 13:04:19
|
| Backed up the databases again from scratch and did a restore on the new server repalcing all objects in the old database. |
|
|
|
|
|
|
|
|
Importing databases (need help)