Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Simplest way to export data from a database in one environment and then import it into another database in another domain on a different SQl Svr?

Posted on 2006-04-18
7
Medium Priority
?
284 Views
Last Modified: 2011-10-03
MS SQL 2k in both environments.  I am a beginner at SQL but don't wish to delve too far into it as it is not really my focus.  We backed up a database and tried to restore it onto a new SQL Svr but it can't find the originating domain accounts so it no likee.  

I just want to focus on the data itself now and export it to a file and then import it into the new database so we can move on with our task.  What is a "flat file" and is that the method we should use?  I need a quick step by step and know it can't be that hard to do.  Please help.  Thanks experts!
0
Comment
Question by:Sp0cky
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 27

Accepted Solution

by:
ptjcb earned 1600 total points
ID: 16482174
The simplest method - use the IMPORT/EXPORT wizard in DTS. In Enterprise Manager, find the server you want, right-click, All Tasks - choose either import data or export data. A wizard form will appear and you walk through each screen answering the questions.
0
 
LVL 9

Expert Comment

by:raopsn
ID: 16482185
If you can connect from one server to the other .. you can use DTS directly to transfer the data
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 16482324
One other possibility:

Restore the db back to its *original* server.  Remove all domain-based users from the db.  Back that modified db up and restore it to the new server.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 400 total points
ID: 16482345
A "flat file" is a standard sequential file, like a .txt file.

Flat files are an option also.  You could use bcp to export every table to a separate file, copy those files to the new server, then use the DTS wizard to load each of the flat files (the BULK INSERT command is faster, but requires you to use line commands in Query Analyzer).
0
 

Author Comment

by:Sp0cky
ID: 16482552
Thanks everyone.  If I export the data, can I use excel 2k3 even though I am using sql2k?  Reason I am asking is because I read an informational article with some "how to" but it said export to excel 2k file and it will organize it automatically.  Then import that file into the other SQL Server.
0
 
LVL 27

Expert Comment

by:ptjcb
ID: 16482922
DTS will allow you to export it out to a file. On the Export form select Excel instead of another SQL Server database. It will then ask you for the location of the Excel file.

Not sure what you mean by Excel organize the file. It won't matter to SQL Server because the tables will be organized based on your clustered indexes. It will ignore any other sort order.

0
 

Author Comment

by:Sp0cky
ID: 16483142
Thanks guys!!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

581 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