Exporting MSSQL database

Hello all

Before I begin - I have very little experience with SQL and databases in general, so please if you are kind enough to offer your advice, please be kind to break it down, even if it seems obvious to you.

Here is the problem - I am hosting a DotNetNuke website with a certain company, whom I would like to leave. In order to migrate my site to another hosting provider I need a copy of my DB, in a .mdf file format (according to the new hosting provider), which I am unable to get from my current provider, why I do not know, and if truth be told dont' particularly care. Anyways. What can I do? Another problem is that I can only access my existing DB thru hosting provider's web portal, not MSSQL Server Management studio. I have an option to export into .CSV file or .XML file. But what do I do with them after that? Bring it into the studio, save it as .mdf? This all probably sounds really funny, and naive to someone who knows about these things, but I am really frustrated. Many thanks in advance.

Quote:

Originally Posted by yellofsh

Hello all

Before I begin - I have very little experience with SQL and databases in general, so please if you are kind enough to offer your advice, please be kind to break it down, even if it seems obvious to you.

Here is the problem - I am hosting a DotNetNuke website with a certain company, whom I would like to leave. In order to migrate my site to another hosting provider I need a copy of my DB, in a .mdf file format (according to the new hosting provider), which I am unable to get from my current provider, why I do not know, and if truth be told dont' particularly care. Anyways. What can I do? Another problem is that I can only access my existing DB thru hosting provider's web portal, not MSSQL Server Management studio. I have an option to export into .CSV file or .XML file. But what do I do with them after that? Bring it into the studio, save it as .mdf? This all probably sounds really funny, and naive to someone who knows about these things, but I am really frustrated. Many thanks in advance.

Well if you could get into the management studio it'd be easy you just create a backup of the database. Otherwise if you can only save the data in each table in your database to a .csv file then it'll be more tricky depending on how many tables are in the database.

First of all you'll need to brush up on your SQL (don't worry it's easy). Start by creating a new database (CREATE DATABASE myDB), then you'll have to create each table manually and define all the fields (unless the web portal will script the CREATE TABLE statements for you) . Then for each table you'll need to import the data from the .csv file into the table using a BULK INSERT statement.

Once all this is done you have your database and you'll find the .mdf file somewhere in the SQL Server directory.

This is only a rough guide but hope it point you in the right direction.

Exporting MS SQL Server or Oracle Databases to MySQL

I also posted similarly in mailing.database.mysql

I have created a rather large (60 table database) website dealio in PHP that
works with MS SQL Server, Oracle, MySQL and presumably any other data base I
add to
the wrapper functions that I created. All the development was done in
Oracle, but now its time to create that large datastructure again in
MySQL -- now I've built this all in notepad for Oracle (twice) and built it
in SQL Server using the SQL Server import tools (pretty nice) -- is there
anything that I can do that to get this datastructure and maybe even the
data into MySQL using Oracle/MS SQL Server tools ?

James"James Alexander Starritt"
<jamesstarrittRemovethefollowingtoemailme@.hotmail.c om> wrote in message
news:403ac257_1@.newspeer2.tds.net...
> I also posted similarly in mailing.database.mysql
> I have created a rather large (60 table database) website dealio in PHP
that
> works with MS SQL Server, Oracle, MySQL and presumably any other data base
I
> add to
> the wrapper functions that I created. All the development was done in
> Oracle, but now its time to create that large datastructure again in
> MySQL -- now I've built this all in notepad for Oracle (twice) and built
it
> in SQL Server using the SQL Server import tools (pretty nice) -- is there
> anything that I can do that to get this datastructure and maybe even the
> data into MySQL using Oracle/MS SQL Server tools ?

SQL Server will permit the creation of scripts
for the data tables that should be useful, and the
bcp function should handle the data.

Pete Brown
Falls Creek
NSW
Oz
|||"James Alexander Starritt" <jamesstarrittRemovethefollowingtoemailme@.hotmail.c om> wrote in message news:<403ac257_1@.newspeer2.tds.net>...
> I also posted similarly in mailing.database.mysql
> I have created a rather large (60 table database) website dealio in PHP that
> works with MS SQL Server, Oracle, MySQL and presumably any other data base I
> add to
> the wrapper functions that I created. All the development was done in
> Oracle, but now its time to create that large datastructure again in
> MySQL -- now I've built this all in notepad for Oracle (twice) and built it
> in SQL Server using the SQL Server import tools (pretty nice) -- is there
> anything that I can do that to get this datastructure and maybe even the
> data into MySQL using Oracle/MS SQL Server tools ?
> James

From the MSSQL side, you could start by scripting the objects from
Enterprise Manager, then reviewing the code and making any
modifications needed to handle different data types, syntax etc. I
know nothing about MySQL, so I have no idea how large or small a task
this would be, except to guess that procedural code (stored
procedures, triggers, user-defined functions) may be the hardest part.
The resulting file can then be run in MySQL.

To move the data over, there are several different options. You can
use BCP or DTS to export to flat files, then import them into MySQL
using whatever tools it provides for bulk import. Alternatively, if
there is an ODBC driver for MySQL, you could use DTS to move the data
directly from MSSQL and/or Oracle to MySQL. Finally, you may be able
to create a linked server on MSSQL, then simply do an INSERT ...
SELECT ... to move the data.

Simon

Exporting Database for Importing on Another Server

This may be a simple question with an obvious answer, but I'm fairly new to databases and I can't seem to find the right procedure.

I created a database on my machine. If I want to copy the information from this database (columns, tables, and entries specifically) from my machine to another SQL server on a different machine, what is the best way to do this without manually re-entering all of the information?

Is there a simple export/import of a database for transferring it from one server to another?

Thanks,

~Josh Graber

Using Backup and Restore is often considered the 'best' way to move a database from one server to another.

Check in Books Online about using Restore with the [with MOVE] option.

|||

there are many method which u can use for this purpose....

(a) Backup/Restore

(b) Detach /attach

(c) copy database wizard

u can read about these method in BOL ...

Madhu

Exporting data to access

I am looking for a way to export my information out of SQL 2000 to access.
I would like to have an exact replica of my SQL databases but in dbf format.
Is there a way to do a backup in SQL in DBF format? Is there a utility to
convert a SQL Backup to DBF.
Thank-you
If you want your database in access, why do you want it in
dbf format? Access uses mdb files.
You can't backup a SQL Server database to other database
platform formats. You can export the data to Access using
DTS. Another option is replication as Access can be a
subscriber to SQL Server publications. You can find more
info in books online under the topic Access Subscribers.
-Sue
On Tue, 24 Aug 2004 09:20:17 -0300, "kmd" <kmd@.hotmail.com>
wrote:

>I am looking for a way to export my information out of SQL 2000 to access.
>I would like to have an exact replica of my SQL databases but in dbf format.
>Is there a way to do a backup in SQL in DBF format? Is there a utility to
>convert a SQL Backup to DBF.
>Thank-you
>

Exporting data between databases

Just starting out with MSSQL 2000 and have a bit of a problem.
I have a remote database that is of the same design of a local database. What I want to do is update one particular field of each record in my local database with data from the remote one. Of course the primary key of each of the records from the remote dbase and the local dbase must match (as even though the remote database has the most up-to-date information, it has more records in it than my local one (but I do not require the additional records).

How on earth do I do this? I could use the DTS based on a query, but how can I ensure that the data goes in the correct records' field?

Any help would be appreciated!

Thanks.If you have ACCESS 2000 or 2002 you can create two linked tables.

One to each table and then use access to run your update, then you can run you insert of the additional fields at the same time.|||Originally posted by Odin_the_Celt
How on earth do I do this? I could use the DTS based on a query, but how can I ensure that the data goes in the correct records' field?


What about using the primary keys for unique identifing the records? And using Inner Join in the update statement.

Best regards!|||Originally posted by Odin_the_Celt
How on earth do I do this? I could use the DTS based on a query, but how can I ensure that the data goes in the correct records' field?


What about using the primary keys for unique identifing the records?

Best regards!

Export wizard exports views as tables

Hi

I have a problem using SQL 2005 Export wizard.

I have 2 databases : DB1 and DB2

DB1 has X tables and Y views
DB2 has W tables and Z views

I want to export only few selected views from DB1 to DB2.

In Import and Export wizard, I select these views and execute the export-package. Completed Successfully.

And finally the problem:

All views that i exported from DB1, are not placed in DB2 as views, but as TABLES!

Why?

Waiting for your quick answer,
Martin

This is by design.

It may happen that some of the tables on which the view depends are not imported at all, which can cause the view definition to fail.

Thanks,

Kuntal

Export wizard exports views as tables

Hi

I have a problem using SQL 2005 Export wizard.

I have 2 databases : DB1 and DB2

DB1 has X tables and Y views
DB2 has W tables and Z views

I want to export only few selected views from DB1 to DB2.

In Import and Export wizard, I select these views and execute the export-package. Completed Successfully.

And finally the problem:

All views that i exported from DB1, are not placed in DB2 as views, but as TABLES!

Why?

Waiting for your quick answer,
Martin

This is by design.

It may happen that some of the tables on which the view depends are not imported at all, which can cause the view definition to fail.

Thanks,

Kuntal

Export SQLServer Agent Job?

We have a new SQLServer 2000 installation and are moving over databases from
a SQLServer 7 installation. How do we bring over the SQLServer Agent jobs?
Thanks in advance,
MikeYou can script them... right click, All Tasks >, generate SQL script
"Mike Lopez" <mikelpez@.optonline.net> wrote in message
news:Ort9RfQYDHA.2284@.TK2MSFTNGP12.phx.gbl...
> We have a new SQLServer 2000 installation and are moving over databases
from
> a SQLServer 7 installation. How do we bring over the SQLServer Agent jobs?
> Thanks in advance,
> Mike
>|||> Once the script is generated what then? Copy it to the new SQL Server 2000
> server and execute it in Query Analyzer?
You might have to adjust server names etc., if they are included in the
script output. But generally, yes, that's the idea. I usually save such
scripts as <jobname>.job.sql files... this way they get the right icon in
Windows explorer, are recognized by Visual Studio / QA, but they also are
clearly marked as a JOB (whereas with the JOB extension, it gets a red x
like a windows scheduled task that doesn't work, at least in W2K3).
And as Chinna points out, you could also use the DTS task "transfer jobs"...

Export SQL Server Diagrams

Are there any tools available that can export SQL Server Diagrams. We have
around 50 complex databases whose structure might change periodically. I wan
t
to export those SQL server diagrams to pdfs or image files. If you have any
suggestions, let me know.
Thanks.We use Visio for this at the place I work at (reverse engineer DB)
and then we convert to PDF
"Pradip" <Pradip@.discussions.microsoft.com> wrote in message
news:2CB5193C-C212-442F-938F-E825E9A2C82A@.microsoft.com...
> Are there any tools available that can export SQL Server Diagrams. We have
> around 50 complex databases whose structure might change periodically. I
> want
> to export those SQL server diagrams to pdfs or image files. If you have
> any
> suggestions, let me know.
> Thanks.|||Unfortunately, I have never come across something that does this (and I
think it would be pretty valuble, too.)
What I've ended up doing is either printing out the diagram across multiple
8.5 x 11 pages and taping them together to get a bigger "poster" diagram...
or
in some cases I've been lucky enough to be able to print the diagram out on
a big plotter (usually used for proj. mgmt. graphics/charts) so I don't have
to tape pages together.
You should be able to make PDF's out of them if you have the Adobe PDFWriter
(a.k.a Distiller) printer driver ..it allows you to print to a PDF file.
-Jason
"Pradip" <Pradip@.discussions.microsoft.com> wrote in message
news:2CB5193C-C212-442F-938F-E825E9A2C82A@.microsoft.com...
> Are there any tools available that can export SQL Server Diagrams. We have
> around 50 complex databases whose structure might change periodically. I
want
> to export those SQL server diagrams to pdfs or image files. If you have
any
> suggestions, let me know.
> Thanks.|||You'll need to use ERD tools for that I think, unless you are satisfied with
the PrintScreen button. Database Diagrams do not support an export feature
that I am aware of.
You can use Microsoft's Visio, E/RWin (www.ca.com), Enterprise Architect
(http://www.sparxsystems.com/), probably a host of others I can't think of
off the top of my head.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Pradip" <Pradip@.discussions.microsoft.com> wrote in message
news:2CB5193C-C212-442F-938F-E825E9A2C82A@.microsoft.com...
> Are there any tools available that can export SQL Server Diagrams. We have
> around 50 complex databases whose structure might change periodically. I
want
> to export those SQL server diagrams to pdfs or image files. If you have
any
> suggestions, let me know.
> Thanks.|||If all you require is something that can be printed (say using MS Word) one
method I use is to connect to the database via an MS Access project (.adp
file instead of the normal .mdb). With the diagram open, you then have a
option to copy the diagram to the clipboard and paste it into word as an
image.
Regards
Dazza
"Pradip" <Pradip@.discussions.microsoft.com> wrote in message
news:2CB5193C-C212-442F-938F-E825E9A2C82A@.microsoft.com...
> Are there any tools available that can export SQL Server Diagrams. We have
> around 50 complex databases whose structure might change periodically. I
> want
> to export those SQL server diagrams to pdfs or image files. If you have
> any
> suggestions, let me know.
> Thanks.|||With the diagram open in the MS Access project, click on Edit -- Copy
Diagram to Clipboard.
Regards
Dazza
"Dazza" <Post2Group@.Only.com> wrote in message
news:ekeI3hKMFHA.3328@.TK2MSFTNGP14.phx.gbl...
> If all you require is something that can be printed (say using MS Word)
> one method I use is to connect to the database via an MS Access project
> (.adp file instead of the normal .mdb). With the diagram open, you then
> have a option to copy the diagram to the clipboard and paste it into word
> as an image.
> Regards
> Dazza
> "Pradip" <Pradip@.discussions.microsoft.com> wrote in message
> news:2CB5193C-C212-442F-938F-E825E9A2C82A@.microsoft.com...
>|||This is a great idea. This will work for me atleast to export the diagram to
bitmap. Is there a way to do this programatically. Can we develop a program
will automatically connect to all the SQL Server databases and import them t
o
Access and copy the Diagram to Clipboard and export them to JPEG everyday. I
f
this is possible then this would be a huge benefit for me.
"Dazza" wrote:

> With the diagram open in the MS Access project, click on Edit -- Copy
> Diagram to Clipboard.
> Regards
> Dazza
>
> "Dazza" <Post2Group@.Only.com> wrote in message
> news:ekeI3hKMFHA.3328@.TK2MSFTNGP14.phx.gbl...
>
>