Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2007
    Location
    Ireland
    Posts
    2

    Database backup formats

    Hi,

    I would like to find out about database backups. For example in db2, oracle and MS sql backups is the data saved as text in a zip file? Where is the best place to find documentation on database backups and the format in which the data is stored. I'm trying to find out if it is possible to restore a database backup from vendor A which has orginated from vendor B

    regards

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    They are stored in propietry formats. These are not specific to the RDBMS but the version too (e.g. you cannot* restore a SQL Server 2005 backup to a 2000 instance).

    Why would you be bothered about restoring Oracle to MySQL?

    *I think this is the case - there might be some hoops you can jump through.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2007
    Posts
    9
    For example 'mysqldump' command has option --compatible=

    See at man mysqldump:
    o --compatible=name

    Produce output that is more compatible with other database systems
    or with older MySQL servers. The value of name can be ansi,
    mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb,
    no_key_options, no_table_options, or no_field_options. To use
    several values, separate them by commas. These values have the same
    meaning as the corresponding options for setting the server SQL
    mode. See the section called "SQL MODES".

    Other database also must have same tool.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by ISPserver
    Other database also must have same tool.
    Not MSSS. Can't speak for Oracle.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jun 2007
    Location
    Ireland
    Posts
    2
    thanks for the information, I'll investigate the solutions offered. The reason I asked the question is because I sometimes get past sample data in the format of database backups. I was wondering if there was a quick way to restore these rather than request all the information again and specify steps to export the data.

    regards

  6. #6
    Join Date
    Aug 2007
    Posts
    3

    Oracle Recovery Experts

    Quote Originally Posted by mccarv
    Hi,

    I would like to find out about database backups. For example in db2, oracle and MS sql backups is the data saved as text in a zip file? Where is the best place to find documentation on database backups and the format in which the data is stored. I'm trying to find out if it is possible to restore a database backup from vendor A which has orginated from vendor B

    regards


    hi there

    i really appreciate your questions regarding database backup bcuz many people don't even have a clue about it but it is something very important when one is dealing with databases... maintaining backups & recovering successfully both are equally crucial....

    i've been looking for answers regarding backup & recovery querries... and got most of my querries replied from www.oracleplace.com

    giv it a shot & i hope it'll work for you

    Good Luck

    Bubye

  7. #7
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    I believe oracle allows you to dump tables to a text format.

    PostgreSQL backups are either compressed, or in plain text, and you have the option of backing up the table data, the table structure (DDL) or both. (plus, you have the option of storing the table data as a series of insert statements, or as a block of csv data, for a bulk copy back into the restored table)

    Here's an example of a pg backup file of a test table.
    Code:
    --
    -- PostgreSQL database dump
    --
    
    -- Started on 2007-08-08 11:31:29 US Mountain Standard Time
    
    SET client_encoding = 'UTF8';
    SET check_function_bodies = false;
    SET client_min_messages = warning;
    
    SET search_path = tds_schema, pg_catalog;
    
    SET default_tablespace = tds_tablespace;
    
    SET default_with_oids = false;
    
    --
    -- TOC entry 1359 (class 1259 OID 19190)
    -- Dependencies: 1696 1697 8
    -- Name: test_table; Type: TABLE; Schema: tds_schema; Owner: postgres; Tablespace: tds_tablespace
    --
    
    CREATE TABLE test_table (
        pk_id bigint NOT NULL,
        tstamp timestamp without time zone DEFAULT now(),
        user_name character varying(30) DEFAULT "current_user"(),
        comments character varying(2000)
    );
    
    
    ALTER TABLE tds_schema.test_table OWNER TO postgres;
    
    --
    -- TOC entry 1702 (class 0 OID 0)
    -- Dependencies: 1359
    -- Name: TABLE test_table; Type: COMMENT; Schema: tds_schema; Owner: postgres
    --
    
    COMMENT ON TABLE test_table IS 'test table';
    
    
    --
    -- TOC entry 1695 (class 2604 OID 19192)
    -- Dependencies: 1359 1358 1359
    -- Name: pk_id; Type: DEFAULT; Schema: tds_schema; Owner: postgres
    --
    
    ALTER TABLE test_table ALTER COLUMN pk_id SET DEFAULT nextval('test_table_pk_id_seq'::regclass);
    
    
    --
    -- TOC entry 1700 (class 0 OID 19190)
    -- Dependencies: 1359
    -- Data for Name: test_table; Type: TABLE DATA; Schema: tds_schema; Owner: postgres
    --
    
    INSERT INTO test_table (pk_id, tstamp, user_name, comments) VALUES (1, '2006-12-07 09:04:54.944', 'postgres', 'This is an test insertion');
    INSERT INTO test_table (pk_id, tstamp, user_name, comments) VALUES (2, '2006-12-07 09:05:05.899', 'postgres', 'This is an test insertion');
    INSERT INTO test_table (pk_id, tstamp, user_name, comments) VALUES (3, '2006-12-07 09:08:08.57', 'postgres', 'This is an test insertion, too!');
    INSERT INTO test_table (pk_id, tstamp, user_name, comments) VALUES (4, '2006-12-07 09:10:09.333', 'postgres', 'This is an test insertion, too!');
    INSERT INTO test_table (pk_id, tstamp, user_name, comments) VALUES (5, '2006-12-07 09:10:30.914', 'postgres', 'This is an test insertion, too!');
    INSERT INTO test_table (pk_id, tstamp, user_name, comments) VALUES (6, '2006-12-07 09:10:31.204', 'postgres', 'This is an test insertion, too!');
    INSERT INTO test_table (pk_id, tstamp, user_name, comments) VALUES (7, '2006-12-07 09:10:31.444', 'postgres', 'This is an test insertion, too!');
    INSERT INTO test_table (pk_id, tstamp, user_name, comments) VALUES (8, '2006-12-07 09:10:31.665', 'postgres', 'This is an test insertion, too!');
    INSERT INTO test_table (pk_id, tstamp, user_name, comments) VALUES (9, '2006-12-07 09:10:31.905', 'postgres', 'This is an test insertion, too!');
    INSERT INTO test_table (pk_id, tstamp, user_name, comments) VALUES (10, '2006-12-07 09:10:34.489', 'postgres', 'This is an test insertion, too!');
    INSERT INTO test_table (pk_id, tstamp, user_name, comments) VALUES (11, '2006-12-07 09:10:34.659', 'postgres', 'This is an test insertion, too!');
    INSERT INTO test_table (pk_id, tstamp, user_name, comments) VALUES (12, '2006-12-07 09:10:34.829', 'postgres', 'This is an test insertion, too!');
    INSERT INTO test_table (pk_id, tstamp, user_name, comments) VALUES (13, '2006-12-07 09:10:34.979', 'postgres', 'This is an test insertion, too!');
    INSERT INTO test_table (pk_id, tstamp, user_name, comments) VALUES (14, '2006-12-07 09:10:35.15', 'postgres', 'This is an test insertion, too!');
    INSERT INTO test_table (pk_id, tstamp, user_name, comments) VALUES (15, '2006-12-07 09:10:35.32', 'postgres', 'This is an test insertion, too!');
    INSERT INTO test_table (pk_id, tstamp, user_name, comments) VALUES (16, '2006-12-07 09:10:35.51', 'postgres', 'This is an test insertion, too!');
    INSERT INTO test_table (pk_id, tstamp, user_name, comments) VALUES (17, '2006-12-07 09:10:35.68', 'postgres', 'This is an test insertion, too!');
    INSERT INTO test_table (pk_id, tstamp, user_name, comments) VALUES (18, '2006-12-07 09:10:35.851', 'postgres', 'This is an test insertion, too!');
    INSERT INTO test_table (pk_id, tstamp, user_name, comments) VALUES (19, '2006-12-07 09:10:36.021', 'postgres', 'This is an test insertion, too!');
    INSERT INTO test_table (pk_id, tstamp, user_name, comments) VALUES (20, '2006-12-07 09:10:36.191', 'postgres', 'This is an test insertion, too!');
    INSERT INTO test_table (pk_id, tstamp, user_name, comments) VALUES (21, '2006-12-07 09:10:36.361', 'postgres', 'This is an test insertion, too!');
    INSERT INTO test_table (pk_id, tstamp, user_name, comments) VALUES (22, '2006-12-07 09:10:36.502', 'postgres', 'This is an test insertion, too!');
    INSERT INTO test_table (pk_id, tstamp, user_name, comments) VALUES (23, '2006-12-07 09:10:36.682', 'postgres', 'This is an test insertion, too!');
    INSERT INTO test_table (pk_id, tstamp, user_name, comments) VALUES (24, '2006-12-07 09:10:36.852', 'postgres', 'This is an test insertion, too!');
    INSERT INTO test_table (pk_id, tstamp, user_name, comments) VALUES (25, '2006-12-07 09:10:37.022', 'postgres', 'This is an test insertion, too!');
    INSERT INTO test_table (pk_id, tstamp, user_name, comments) VALUES (26, '2006-12-07 09:12:34.16', 'postgres', 'This is an test insertion, too!');
    INSERT INTO test_table (pk_id, tstamp, user_name, comments) VALUES (27, '2006-12-07 09:13:04.613', 'postgres', 'This is an test insertion, too!');
    INSERT INTO test_table (pk_id, tstamp, user_name, comments) VALUES (28, '2006-12-07 09:13:48.576', 'postgres', 'This is an test insertion, too!');
    INSERT INTO test_table (pk_id, tstamp, user_name, comments) VALUES (29, '2006-12-07 09:14:34.431', 'postgres', 'This is an test insertion, using default values for ALL fields except comments...');
    
    
    --
    -- TOC entry 1699 (class 2606 OID 19197)
    -- Dependencies: 1359 1359
    -- Name: test_table_pk; Type: CONSTRAINT; Schema: tds_schema; Owner: postgres; Tablespace: tds_tablespace
    --
    
    ALTER TABLE ONLY test_table
        ADD CONSTRAINT test_table_pk PRIMARY KEY (pk_id);
    
    
    -- Completed on 2007-08-08 11:31:29 US Mountain Standard Time
    
    --
    -- PostgreSQL database dump complete
    --
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •