May 4, 2005 at 9:58 am
Hello All:
I'm still fairly new to SQL server. I'm using SQL Server 2000 with an Access .adp as a front end. Soon I will be moving my database from a test server to an active server. I noticed that when I copy the tables of a database and import them into a new database using DTS, I lose the primary keys on the tables. I was wondering why this happens and what I can do to stop it. Also, in this process, I've noticed that sometimes the data in the table is duplicated. Again, I'm not sure how or when this occurs?
Is there a correct way to move/copy a database from one server to another? Or even copy the db on one server?
One more... Although I'm currently using Access for my front end, I've been thinking about using something different. Any favorites or recommendations.
Any help is greatly appreciated.
Thanks,
Cleech
May 4, 2005 at 1:13 pm
I prefer to script out database objects when promoting them to a new server. This way you are sure what is being moved and if an error arises you see it plain and simple in query analyzer. The easiest way to do this is by Right clicking on the database and selecting all tasks.....generate sql scripts. Click the show all button and then you can select the objects you want to move. After you have all the objects you want selected make sure you click on the options tab and check off the Primary Keys, Foreign Keys, Check constraints etc. Once you are done you can save the script to a file and keep it for later deployment.
With data you can right click your new database and select all tasks.....import data. One thing to remember though is you may need to run this multiple times to import the data into the tables in order of Referential integrity, unless of course you don't execute the add keys script until after the data is imported.
May 5, 2005 at 6:54 am
Instead of using the 'copy tables...' from the source database, use 'copy objects and data...' from the source database. There is an options button that allows you to include primary keys, triggers, indexes, etc. I rarely use the 'copy tables...' option, only to save a copy of pure data as a quick backup.
The preferred way I move/copy a database is to detach it with sp_detach_db, copy it to the new destination, and attach it with sp_attach_db.
July 7, 2006 at 2:58 pm
Well you can copy the database also as Journeyman said but the Best way to Move Database from one server to another is Backup the Database on a central location and Restore on a new server from central Location
Bhavesh gandhi
Systems Engineer
IGT SYSTEMS
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply