?
Solved

EXPORT & IMPORT OF DATABASE

Posted on 1998-07-29
5
Medium Priority
?
300 Views
Last Modified: 2010-03-19
Can u kindly list down the steps for exporting a database and also import the export to another database in MS SQL Server
0
Comment
Question by:vram
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 2

Expert Comment

by:jbiswas
ID: 1089293
Do you want the details on how to bcp out/in data, or just how to load a database backup? Maybe you're referring to the DTS export and import!! Please clarify
0
 
LVL 9

Accepted Solution

by:
cymbolic earned 100 total points
ID: 1089294
One of the easiest methods if transportig between two SQL Server databases is to use RAS support and dial in to your remote server, then use the tools/Database Object/Transfer screen to transfer your database and its contents between servers.

Alternatively, you can have one SQL Server create a backup of the database, then copy and send the backup device to another server.  On the target server, copy in the backup file, then create a backup device of the same name on the target server, make a database, then restore to the newly made database.

Still another method is to use MS Access as a go between.  You can link it to one SQL server, import all tables and contents to the .mdb, copy the .mdb and take it over to another server domain, link to a SQL Server on the target and export or copy using the Access front end interface.

SQL Server also supports a native format so that you can use BCP to export then import back in.  If you are not on a machine with SQL Client support installed, get to one, and use the SQL Books OnLine to get your answers.  It's an excellent help file formatted resource that you can install when you install your SQL Client software.

I've also written an ODBC SQL migration program in VB that uses SQL scripts with mapping constructs to migrate and convert data from one ODBC source to another (Access to SQL Server for instance).  ALthough this is general purpose, you can write one for a specific purpose to do a conversion/transfer as well.
So, what's your pleasure?
0
 

Author Comment

by:vram
ID: 1089295
Hi,
  I used the Dump & Load commands to do export and import. But when i login using the new user for the new database and sp_help on some table, it says table not found.
The following are the steps i followed
1.Logged in as sa, executed sp_addumpdevice 'disk', 'disk_dev', 'c:\disk1.dmp', 2
2. logged in as the user for the db which needs to be dumped and execute
dump database (db name i want to dump) to disk 'disk_dev'
3. Created a new database 'X' of the same data and device size.
4. create new user , default user for database 'X'
5. log in as sa, execute Load database ''X" from disk = 'c:\disk1.dmp'  
All the operations done on the same machine
Can u suggest me whethere the above method is correct or if any mistakes do point out.
Thanx in advance


0
 
LVL 1

Expert Comment

by:baryonic
ID: 1089296
Sounds like a problem with the dbo on the 2 databases. Is it the same on them both, or is it different? It could be that you can't see the tables as you need to specify owner.table.
0
 
LVL 1

Expert Comment

by:mativare
ID: 1089297
In MSSQL 7.0 beta you can use data dransformation services
to export / import all tables it is windows based and very handy and easy and fast, but if you import into new table you loose all indexing and relatioships, therefore create tables first
NB of course you loose all SPs too
0

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question