Solved

EXPORT & IMPORT OF DATABASE

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

828 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