Solved

EXPORT & IMPORT OF DATABASE

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

691 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