Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 sql equivalent to exports

Author  Topic 

helpme
Posting Yak Master

141 Posts

Posted - 2005-01-08 : 11:19:33
In oracle, you can do an export of the objects belonging to a user. From this, you can drop and re-import an individual table, rather than having to recover the whole database (the import recreates the table and reloads it). Is there an equivalent to this type of export/import in sql server? (exporting all tables for a user, then import back an individual table with the import rebuilding and reloading the data?)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-01-08 : 11:24:32
You can use DTS or bcp to export and import data from SQL Server to another file format. SQL Server Books Online has more details on both.
Go to Top of Page

helpme
Posting Yak Master

141 Posts

Posted - 2005-01-08 : 11:53:57
Will exporting to this other file format save the "table create" information? Can you do a whole user schema, or do you have to do individual tables? Thanks for replying to this.
Go to Top of Page

jhermiz

3564 Posts

Posted - 2005-01-08 : 12:37:21
If you are wanting to script out your db yes it will output the create table information.
If you open up EM you should be able to right click on a db or table and there is an option to script the db objects, etc.




Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]


Go to Top of Page

helpme
Posting Yak Master

141 Posts

Posted - 2005-01-08 : 13:14:23
Right now we have exports that automatically run for oracle and if we want to restore a single table, we can drop the table and run the import utility against the export file and it will rebuild the table and reload the data, set privileges, etc (we can make changes to the table without having to modify the export procedure). Am I right in thinking with sql we would have to re-run the script of the table information and modify any dts packages any time a new column was added?
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-08 : 14:26:31
You can create a database then have a SP or dts package that copies the table to that database then backs it up then deletes the table.
You can create a similar task which restores the database and copies the table.


maybe a better option is to script the schema and bcp out all the table data. Restoring will be the reverse.
This will script all the tables - the second one does all (almost) objects.
http://www.mindsdoor.net/DMO/DMOScripting.html
http://www.mindsdoor.net/DMO/DMOScriptAllDatabases.html

This will bcp out (and in) all the data from all tables
http://www.mindsdoor.net/SQLTsql/BCPAllTables.html

They probably only work for dbo but it's not difficult to change them for other owners.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

helpme
Posting Yak Master

141 Posts

Posted - 2005-01-08 : 15:18:46
Thanks for the replies
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-01-09 : 03:42:42
SQL Server has no equivalent to the Oracle export.
Go to Top of Page

amachanic
SQL Server MVP

169 Posts

Posted - 2005-01-09 : 09:51:37
New versions of SQL LiteSpeed have object-level backup and restore capabilities (so they claim) -- I have not tried it yet, but their marketing is convincing :)

Looks like you can d/l a trial version here:

http://www.imceda.com/Backup_System.htm
Go to Top of Page
   

- Advertisement -