Using 16.0.0.2798

This is more of a warning to others rather than a question, but please feel free to comment.

It seems as if the dbunload utility when executed with the -e option to exclude a table, doesn't produce a complete rebuild script skipping users, functions etc. I don't understand this behavior, the help says

Excludes the specified tables from the reload.sql file. Table names are always case insensitive, even in case sensitive databases. A reload.sql file created with the -e option should not be used to rebuild a database because the file will not include all the database tables. If a table has foreign keys referring to it, the database cannot be rebuilt without the contents of the table. It is recommended that you only use the -e option with the -d option to unload data for all tables except those identified by -e. <<

In my case the -d option was not included so I expected a complete reload script to be generated. I guess the only alternative is to delete the table prior to unload.

asked 01 Oct, 05:45

JJ%20Diaz's gravatar image

JJ Diaz
101228
accept rate: 0%

I unloaded again without data and used this unload script to fix the first one generated to save the time of unloading all data again (this is a 1 TB replicating database). Verified both the call to SYS.sa_setsubscription and SYS.sa_setremoteuser.

(01 Oct, 09:35) JJ Diaz

Thanks for posting this warning.

(01 Oct, 09:53) Breck Carter

If memory serves, all my attempts to use dbunload -e for any purpose whatsoever have failed.

A simple comparison of V16 demo database dbunload commands with and without -e GROUPO.MarketingInformation reveals that a lot of stuff is included, but even more is excluded.

In particular, note that it's not just users and functions that are missing, but all the views and procedures:

"%SQLANY16%\bin64\dbunload.exe"^
  -c "ENG=demo;DBN=demo;UID=dba;PWD=sql;"^
  -o dbunload_16_log_demo.txt^
  -r reload_16.sql^
  -v^
  reload_16

"%SQLANY16%\bin64\dbunload.exe"^
  -c "ENG=demo;DBN=demo;UID=dba;PWD=sql;"^
  -e GROUPO.MarketingInformation^
  -o dbunload_16_log_demo_e.txt^
  -r reload_16_e.sql^
  -v^
  reload_16_e

dbunload -e output omits these sections...

--   Create dbspaces
--   Create ldap servers
--   Create login policies
--   Create users
--   Create role definitions
--   Create user types
--   Create spatial units of measure
--   Create spatial reference systems
--   Create remote servers
--   Create dbspace permissions
--   Create external environments
--   Create external environment objects
--   Create certificates
--   Create text configurations
--   Create materialized views
--   Create immediate materialized views
--   Create functions
--   Create views
--   Create user messages
--   Create procedures
--   Create SQL Remote definitions
--   Create MobiLink definitions
--   Create Synchronization profiles
--   Create logins
--   Create events
--   Create services
--   Create mirror options and servers
--   Set DBA password
--   Create options

Other sections (like ALTER TABLE) appear in different orders so it's difficult to determine if they are the same.

It is difficult to imagine how the output from dbunload -e could be useful for ANY PURPOSE without editing.

permanent link

answered 01 Oct, 09:48

Breck%20Carter's gravatar image

Breck Carter
30.9k498682993
accept rate: 20%

edited 01 Oct, 09:49

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×46

question asked: 01 Oct, 05:45

question was seen: 72 times

last updated: 01 Oct, 09:53