0

I have a situation in which I have taken a backup(sql scripyts containing the insert queries) of a table in one of the environment, and I want that backup to be restored in a different schema in in MYSQLClient Version 5.1.11. The problem is that the file is too big(200MB).

The backup file contains the "use schema_name" query, so if i try to restore that, It will be restored in the schema_name mentioned in that script.

Neither I can edit that file, since it is too big, that after edit it is not saved.

Note: the schema above means the DATABASE.

Please suggest me an approach.

Thanks.

Sashi Kant
  • 13,277
  • 9
  • 44
  • 71
  • What are you using as an editor? Most decent text editors can edit 200MB files without much trouble (Notepad++, Scite, Editplus) – Eric Petroelje Nov 03 '11 at 12:11
  • I have used both EditPlus as well as Note pad, the gets opened, but after edit,when I try to save, the application gets hang – Sashi Kant Nov 03 '11 at 12:16

1 Answers1

1

If you really can't edit the dump file (don't know what OS you're using but if it is Linux then you could try using something like sed to replace the schema name or a Windows equivalent) then you could try:

  1. Create the original schema in your new database
  2. Run the dump file into the original schema in your new database
  3. Create your new schema in your new database
  4. Rename each table from your original schema to your new schema.
  5. Drop the original schema

You can generate a script to move all tables in one schema to another schema using the following script:

To create a script to move all tables from 'FromSchema' to 'ToSchema':

select concat('RENAME TABLE ',table_schema,'.',table_name,' TO ','ToSchema','.',table_name,';')
from information_schema.tables t
where t.table_schema = 'FromSchema';

Good luck!

Community
  • 1
  • 1
Tom Mac
  • 9,693
  • 3
  • 25
  • 35