| Subject | Re: [firebird-support] How to recreate database? |
|---|---|
| Author | Helen Borrie |
| Post date | 2013-10-18T01:35:20Z |
At 01:23 p.m. 18/10/2013, Caroline Beltran wrote:
The following statement (POSIX format: if you're on Windows, fix the paths) extracts the SQL schema from the database employee.fdb to a schema script file called employee.sql:
isql -extract -a -output /data/scripts/employee.sql /data/employee.fdb
If you omit the -a switch, you won't get script text for:
System tables and views, system triggers (you don't want it)
External function and BLOB filter declarations
Object ownership attributes (you will need this if the DB has objects that were created by different users)
You'll have to edit the output script to remove (or change) the CREATE DATABASE statement DEFAULT CHARACTER SET parameter and other cruft. Also, AFAIR, the output script is in ASCII so you'll need an editor that can convert the script to UTF-8 encoding *without* the BOM characters (such as the free Notepad++). It's a good idea to break it up, too, especially if you have a large body of metadata. And don't forget to have the client character set setting as UTF-8 when running the isql script[s] (use SET NAMES UTF8).
Actually, there are quite a few export/import (data-pumping) tools around for Firebird databases. If you are already using a proprietary tool, you might find it has one built in.
Helen Borrie, Support Consultant, IBPhoenix (Pacific)
Author of "The Firebird Book" and "The Firebird Book Second Edition"
http://www.firebird-books.net
__________________________________________________________________
>I created a database with no character set and would like to convert it to the UTF-8 character set.isql with the -[e]x[tract] switch will extract the entire metadata of the database, by default to the screen but you can use an additional switch to output it to a text file.
>
>Is it possible to extract the DDL and create an empty database (UTF-8)?
>
>I don't want to have to recreate the tables, domains, generators, triggers and indexes using my firebird database utility.
The following statement (POSIX format: if you're on Windows, fix the paths) extracts the SQL schema from the database employee.fdb to a schema script file called employee.sql:
isql -extract -a -output /data/scripts/employee.sql /data/employee.fdb
If you omit the -a switch, you won't get script text for:
System tables and views, system triggers (you don't want it)
External function and BLOB filter declarations
Object ownership attributes (you will need this if the DB has objects that were created by different users)
You'll have to edit the output script to remove (or change) the CREATE DATABASE statement DEFAULT CHARACTER SET parameter and other cruft. Also, AFAIR, the output script is in ASCII so you'll need an editor that can convert the script to UTF-8 encoding *without* the BOM characters (such as the free Notepad++). It's a good idea to break it up, too, especially if you have a large body of metadata. And don't forget to have the client character set setting as UTF-8 when running the isql script[s] (use SET NAMES UTF8).
>I can then write a script that copies all records from the old database to the new database.Can you? Wow!
Actually, there are quite a few export/import (data-pumping) tools around for Firebird databases. If you are already using a proprietary tool, you might find it has one built in.
>P.S. Is there a newsgroup server that we can connect to with a news reader or do we have to browse the messages here on Yahoos web interface?This is an **email list** so you should be getting all the list traffic in your inbox. The Yahoo interface is "it" for browsing. Some of our Yahoo lists get collected by gmane, too. Possibly this one does. I think it might have a newsreader-friendly interface...
Helen Borrie, Support Consultant, IBPhoenix (Pacific)
Author of "The Firebird Book" and "The Firebird Book Second Edition"
http://www.firebird-books.net
__________________________________________________________________