Wednesday, July 1, 2009

Backup your MySQL Database

Ok, so you have a MySQL installation, and you want to backup your precious data, but you don't know how.

My Solution has some dependancies.

a.
Install your SQL application as a Service, so that you can start and stop it on scheduled command. You will need to do your own research to learn how to do this.

b. Create a folder on your hard disk C:\Backup

c. know what the Service Name of your SQL Application is

d. Know the path to your SQL Database files ie. C:\program files\SQL APP\database

Ok lets do this:

1.
Create a batch file in notepad START >> RUN >> NOTEPAD (ok)

2. Copy this script and paste it into notepad, and then modify it so that it represents your ServiceName, and database location.

START COPY HERE >>

net stop SQL_SERVICENAME_HERE
c:
cd\
cd progra~1\SQL_APPLICATION_HERE\database
copy *.db c:\backup
copy *.log c:\backup
net start SQL_SERVICENAME_HERE

<< END COPY HERE

3. Save the script to your hard disk as C:\sqlbckp.bat

4. Check in Windows Explorer that the script file has saved with the right name and suffix. you may need to rename it, if you didn't turn off the .TXT part.

START >> RUN >> C:\ (ok)

5.
Open up your Scheduled Tasks and create a new task. The program will be the batch file you just created C:\sqlbckp.bat and you can set the schedule at a regular time interval. Make sure to set it for a time, when no one else will need to access the SQL Database. The reason is that, this script will shutdown the SQL Server in order to back itself up. It will restart the service after backup, but during the backup no one will have access.

NB. Also make sure that you use a login with administrative privaledges to set this scheduled task, as anything lower will likely fail when run.

6. Once you have saved your task, run it, and make sure that it copied the files for you. Also check that you can login to SQL after it is done.

Well done! :)

If you have problems, call me, or even better email me, and I will do my best to help you.

Cheers, josh


START >> PROGRAM FILES >> ACCESSORIES >> SYSTEM TOOLS >> SCHEDULED TASKS

No comments: