Solved

EXPORT & IMPORT OF DATABASE

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

746 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

13 Experts available now in Live!

Get 1:1 Help Now