Solved

EXPORT & IMPORT OF DATABASE

Posted on 1998-07-29
5
291 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
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 50 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

896 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now