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. |
|
|
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. |
|
|
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] |
|
|
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? |
|
|
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.htmlhttp://www.mindsdoor.net/DMO/DMOScriptAllDatabases.htmlThis will bcp out (and in) all the data from all tableshttp://www.mindsdoor.net/SQLTsql/BCPAllTables.htmlThey 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. |
|
|
helpme
Posting Yak Master
141 Posts |
Posted - 2005-01-08 : 15:18:46
|
Thanks for the replies |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-01-09 : 03:42:42
|
SQL Server has no equivalent to the Oracle export. |
|
|
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 |
|
|
|