SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Evangelist
    Join Date
    May 2007
    Location
    Kent in Uk
    Posts
    538
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Backing up database

    I am very new to mysql. I have a database on my hosts remote server which I wish to back up. I have already been told to back it up I need sql yog. Which I downloaded. When I went in the backup option was "backup to a sql dump" Is this what I shoud be doing in order to back up to my external hard drive?

    cass27

  2. #2
    SitePoint Enthusiast webburu's Avatar
    Join Date
    Mar 2008
    Posts
    57
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes, but check it again that you have both database structure and data in your sql dump file.

  3. #3
    SitePoint Evangelist
    Join Date
    May 2007
    Location
    Kent in Uk
    Posts
    538
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    After saving using (structure and data) I save to an externel file. However what is been saved is: database name and then under that it says: Microsoft sql server query file. Does this seem right?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cass27 View Post
    Does this seem right?
    no
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist
    Join Date
    May 2007
    Location
    Kent in Uk
    Posts
    538
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    any ideas what may be wrong?

    I really just want to do an ordinary back up which I thought was quite straight forward but is proving more complex than i thought.
    The other thing I suppose is I use IIs on my local computer??
    I dont have apche locally.
    Can someone please tell me how i back up a remote mysql databse on windoows platform onto my onto my external hard drive? I have sql yog but its a bit of a mystery.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    if you produced a backup file with sqlyog and it says "microsoft sql server" in it, could you please paste the first few lines of it here?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Evangelist
    Join Date
    May 2007
    Location
    Kent in Uk
    Posts
    538
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The title is microsoft sql server Query file. When i click it it opens in microsoft sql express. I have this programme on my computer as I tried originally to learn asp.net but feel now I would rather try php.

    The first lines of the actual file are:

    /*
    SQLyog Enterprise Trial - MySQL GUI v7.02
    MySQL - 5.0.45-community-nt : Database - cass27
    *********************************************************************
    */


    /*!40101 SET NAMES utf8 */;

    /*!40101 SET SQL_MODE=''*/;

    /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
    /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

    CREATE DATABASE /*!32312 IF NOT EXISTS*/`cass27` /*!40100 DEFAULT CHARACTER SET latin1 */;

    USE `cass27`;

    /*Table structure for table `phpbb_auth_access` */

    DROP TABLE IF EXISTS `phpbb_auth_access`;

    CREATE TABLE `phpbb_auth_access` (
    `group_id` mediumint(8) NOT NULL default '0',
    `forum_id` smallint(5) unsigned NOT NULL default '0',
    `auth_view` tinyint(1) NOT NULL default '0',
    `auth_read` tinyint(1) NOT NULL default '0',
    `auth_post` tinyint(1) NOT NULL default '0',
    `auth_reply` tinyint(1) NOT NULL default '0',
    `auth_edit` tinyint(1) NOT NULL default '0',
    `auth_delete` tinyint(1) NOT NULL default '0',
    `auth_sticky` tinyint(1) NOT NULL default '0',
    `auth_announce` tinyint(1) NOT NULL default '0',
    `auth_vote` tinyint(1) NOT NULL default '0',
    `auth_pollcreate` tinyint(1) NOT NULL default '0',
    `auth_attachments` tinyint(1) NOT NULL default '0',
    `auth_mod` tinyint(1) NOT NULL default '0',
    KEY `group_id` (`group_id`),
    KEY `forum_id` (`forum_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

    It carries on going throught the different tables to create. It has also saved the data by the looks of it.
    Am I short of a programme to view the file in ?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cass27 View Post
    The title is microsoft sql server Query file. When i click it it opens in microsoft sql express.
    ah, i think i see the problem

    you have a windows computer where the ".sql" file extension has been associated with MS SQL Express

    you can change this association in Control Panel > Folder Options > File Types

    change it to notepad or your favourite text editor or even to sqlyog

    your file is actually the correct file for mysql
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Evangelist
    Join Date
    May 2007
    Location
    Kent in Uk
    Posts
    538
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks

    It now opens as a notepad file. though strangely it is still titled Microsoft sql server query file.

    Anyway if its the right file i guess it should restore ok?

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cass27 View Post
    Anyway if its the right file i guess it should restore ok?
    if there are no errors, yes

    you can keep this file as your backup, taking new downloads on a regular basis

    but you should at least try it out, i.e. run it back into sqlyog to create/restore it on your own hard drive
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Evangelist
    Join Date
    May 2007
    Location
    Kent in Uk
    Posts
    538
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks for sticking with this r937 this has helped a lot


Bookmarks

Posting Permissions

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