| Author |
Topic |
|
kwilder
Starting Member
5 Posts |
 Posted - 2010-01-02 : 16:58:11
|
What is the best way to export data from one SQL Server 2008 to another SQL Server 2008? Keep in mind when you answer that I need the following to happen? - create tables in new database
- set primary key and auto increment settings on new table creation
- apply table relationships
- insert existing primary keys values with data import
I'm a small web host and I need to migrate about 15 SQL Server databases and I need the absolute easiest and most accurate way of doing this.I've found the Export data doesn't set the primary keys on the new tables or maintain table relationships.I using Export data to insert data can run out of memory on some tables.I spent almost an hour trying to move a single database with 10 tables while trying to maintain existing data relationships with primary keys, etc. There has to be a better way.So what is the best way to do this and handle those tables with a lot of data?I appreciate your suggestions.Thanks,KWilder |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
kwilder
Starting Member
5 Posts |
Posted - 2010-01-02 : 19:58:51
|
I assume it retains all the current database settings, etc?So can I also assume that this would be the general scenario: - Back up current database on old server
- FTP it over to new server
- Drop it in SQL Data directory
- Restore it to new server
- and all is right with the world?
Does this sound correct?Thanks. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
kwilder
Starting Member
5 Posts |
Posted - 2010-01-03 : 20:25:51
|
| It worked! Thanks. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
kwilder
Starting Member
5 Posts |
Posted - 2010-01-06 : 18:03:25
|
| Tara,This is kind of a follow up of this thread, I was able to move the databases successfully and confirmed that all the primary keys are set and all the auto increment settings, etc are ok. So all that looks ok.But, I just tried accessing the database on the new server and I get the error:Cannot open database...Login failed for user ...I created this database and user via the Helm 4 control panel. This always works for newly created databases. I've never migrated a database to a new server before.I've used the exact same credentials, I've updated the connection string to point to the new server.Do you know why this would happen and how I can fix it?Thanks,KWilder |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
kwilder
Starting Member
5 Posts |
Posted - 2010-01-07 : 11:17:49
|
| Yes. I created the database and the user through the Helm 4 control panel.Any other ideas?Thanks,KWilder |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Jinasoso
Starting Member
5 Posts |
Posted - 2011-03-24 : 01:31:15
|
| http://www.daniweb.com/software-development/csharp/threads/355427/1513117#post1513117This tread may be helpful for you! |
|
|
|
|
How to Export Data?