mysql database backup and recovery

How to Backup and Restore MSSQL Databases (Detailed Tutorial for Beginners)_MySQL

bitsCN.com

How to Backup and Restore MSSQL Databases (Detailed Tutorial for Beginners)

I. Backup Database 1, Enterprise Manager 2, SQL statement (full backup): backupdatabase database todisk=’c:/your backup file name’

Second, restore the database 1, Enterprise Manager 2, SQL statement: restoredatabase database fromdisk=’c:/your backup file name’

The following are the specific steps:

a. Backup database

1 SQL Enterprise Manager, open the SQL Enterprise Manager, in the console root directory in order to open MicrosoftSQLServer;

2, SQLServer group – & gt; double-click to open your server – & gt; double-click to open the database directory;

3, select the name of your database (such as Forum database Forum) – & gt; and then click on the menu above Tools – & gt; and then click on the menu above the database directory;

1, backup database

3, select your database name (such as Forum database Forum) – & gt; and then click on the menu above the database directory. Then point above the menu in the Tools –> select Backup Database;

4, backup options select Full Backup, the purpose of the backup to if the original path and name of the selected name point to delete, and then point to add, if there is no path and name of the original direct choice to add, and then specify the path and filename, specify the point to return to the backup window after the backup window, and then point to OK to backup.

Second, restore the database

1, open the SQL Enterprise Manager, in the console root directory, click MicrosoftSQLServer;

2, SQLServer group – & gt; double-click to open your server – & gt; point to the icon bar of the new database icon, the name of the new database to take their own;

3, click on the name of the newly created database (such as Forum database Forum) – & gt; and then point to the above menu in the Tools – & gt; choose to restore the database;

4, in the pop-up window of the restore option to choose from the device – & gt; point to select the device – & gt; point to add – & gt; and then choose your backup file name – ->Add and tap OK to return, then the device bar should appear you just selected the database backup file name, the backup number defaults to 1 (if you have done multiple backups of the same file, you can click on the backup number next to the view of the contents of the check box to select the latest backup point OK) -> and then click on the top of the General next to the Options button;

5. The window that appears, select the option to force a restore on an existing database, as well as the option to enable the database to continue to run but not restore other transaction logs in the recovery completion state. In the middle of the window will restore the database file here in accordance with your SQL installation settings (you can also specify their own directory), the logical file name does not need to change, move to the physical file name to be based on your recovery of the machine to make changes, such as your SQL database installed in D:/ProgramFiles/MicrosoftSQLServer/ MSSQL/Data, then the SQL database is installed in D:/ProgramFiles/MicrosoftSQLServer/ MSSQL/Data, then the database is installed in D:/ProgramFiles/MicrosoftSQLServer/ MSSQL/Data. MSSQL/Data, then in accordance with the directory of the machine you are recovering to make the relevant changes, and the last file name had better be changed to the name of your current database (such as the original is bbs_data.mdf, and now the database is forum, it will be changed to forum_data.mdf), the logs and data files have to be in accordance with this way to make the relevant changes (the log of the file name is *_log.ldf end), here you can freely set the recovery directory, provided that the directory must exist (such as you can specify d:/sqldata/bbs_data.mdf or d:/sqldata/bbs_log.ldf), or else the recovery will be reported as an error;

6, after the completion of the modification, click OK below recovery, then a progress bar will appear, prompting the progress of the recovery, recovery is complete, the system will automatically prompt success, such as the middle of the prompt error, please record the relevant error content and ask the more familiar with the SQL operation of the personnel, the general error is nothing more than a directory error or a duplicate filename or file name error or not enough space or the database is being used in the wrong database is in use! You can try to close all the windows about SQL and then reopen them to recover the operation, if you still suggest that the error is in use, you can stop the SQL service and then restart it to see, as for the other errors mentioned above, you can generally follow the content of the error to make the appropriate changes can be recovered.

Third, set up a daily automatic database backup

1, open the Enterprise Manager, in the console root directory, click MicrosoftSQLServer – & gt; SQLServer group – & gt; double-click to open your server;

2, and then click on the menu above the Tools – & gt; select Database Maintenance Planner;

3, the next step to select the data to be automatically backed up – & gt; next update data optimization information, here generally do not have to make a choice – & gt; next check the integrity of the data, but also generally do not choose;

4, the next step to specify the database maintenance plan, the default is 1 week backup, click to change the choice of daily backups and then click OK; < /p>

5, point to the above menu in the Tools – & gt; select the database maintenance plan, the default is 1 week backup, click to change the choice of daily backup after pointing to determine; < /p>

5, the next step specify the backup disk directory, select the specified directory, such as you can create a new directory in the D disk, such as: d:/databak, and then choose to use this directory here, if your database is more than one of the best choice to create a subdirectory for each database, and then choose to delete the backup earlier than how many days ago, generally set 4-7 days, this depends on your specific requirements of the backup. This depends on your specific backup requirements, the backup file extension is generally bak on the default;

6, the next step to specify the transaction log backup plan, depending on your needs to do the selection – & gt; the next step to generate the report, generally do not do the selection – & gt; the next step to maintain the history of the plan, it is best to use the default option – & gt; the next step to complete;

7, after the completion of the system may prompt the system to delete the backup file, generally set 4-7 days.

7, after the completion of the system is likely to prompt the SqlServerAgent service is not started, first click OK to complete the plan set, and then find the desktop rightmost status bar in the SQL green icon, double-click on the open, in the service of the choice of SqlServerAgent, and then click on the Run arrow, select the bottom of the when starting the OS automatically start the service;

8, this time the database plan has been successfully run. At this time, the database program has been successfully run, it will be in accordance with your above settings for automatic backup.

By “www.qq.com65127378ol…”

bitsCN.com

How to Implement MySQL Database Backup and Recovery

Backing up your database in the event of a lost or corrupted database table is important, and if a system crash occurs you definitely want to be able to restore your tables with the least amount of data loss as possible to the state they were in at the time of the crash. Sometimes it’s the MySQL administrators who are the ones causing the damage and the administrators who have been known to corrupt tables by trying to edit them directly with editors such as vi or Emacs. editors such as vi or Emacs to try to edit them directly, which is never a good thing for tables!　　　　The two main methods of backing up databases are with the mysqlmp program or by copying the database files directly (e.g. with cpcpio or tar, etc.) Each method has its advantages and disadvantages. mysqlmp operates in conjunction with the MySQL server, and the direct copy method takes place outside of the server, and you have to take steps to make sure that no clients are modifying the tables you’re going to copy if you want to back up the database with a file system backup. The same problem occurs if the database tables are modified during the file system backup process and you enter a state where the table files in the backup are inconsistent in terms of their subject matter, which makes it pointless to restore the tables at a later date. The difference between a file system backup and a direct copy of the files is that in the latter case, you are in complete control of the backup process, so that you can take steps to ensure that the server keeps the tables undisturbed. mysqlmp is slower than the direct copy method. mysqlmp generates text files that can be ported to other machines, even those with different hardware configurations. direct-copy files cannot be ported to other machines unless the table you are copying uses the MyISAM storage format ISAM tables can only be copied on machines with similar hardware configurations. the introduction of the MyISAM table storage format in MySQL solves this problem because the format is The MyISAM table storage format introduced in MySQL solves this problem because the format is machine-independent, so a direct copy of the files can be ported to a machine with a different hardware configuration as long as two conditions are met: the other machine must also be running MySQL or a later version, and the files must be in the MyISAM format rather than ISAM. Regardless of which backup method you use, if you need to restore a database, there are a few principles that should be followed to ensure the best possible results. Have the server perform an update log When you need to recover data after a crash, the update log will help you After you have restored the data to the state it was in when you backed it up using the backup file, you can reapply the changes made behind the backup by running a query in the update log This restores the database tables to the state they were in at the time of the crash In file-system backup terms, the database backup file represents a full dump (fullmp), whereas the update log represents a full dump of the backup file. In file system backup terms, the database backup file represents a fullmp, while the update log represents an incrementalmp Using a consistent and understandable naming scheme for backup files such as backupbuckup etc. does not make a lot of sense when performing your recovery You will waste time trying to figure out what is in the file You may find it useful to form the name of the backup file using the name of the database and the date e.g. % mysqlmpsamp_tab_database_file_name mysqlmpsamp_db>/usr/archives/mysql/samp_db %mysqlmpmenagerie>/usr/archives/mysql/menagerie You may want to compress your backups after they have been generatedBackups are generally very large! You also need to allow your backup files to expire so that they don’t fill up your disk, just as you allow your log files to expire. Back up your backup files with file system backups. Keep your backup files on a different filesystem than the one used for your database. This reduces the likelihood that the filesystem containing your data directory will fill up as a result of generating backups. The same techniques used to create backups are also useful for copying databases to another machine. Most commonly a database is transferred to a server running on a different host, but you can also transfer the data to a different server on the same host. Databases When you use the mysqlmo program to generate a database backup file, by default the contents of the file contain the CREATE statement that creates the table being dumped and the INSERT statement that contains the rows of data from the table. In other words, mysqlmp generates output that can later be used as input to mysql to rebuild the database. You can dump the entire database into a single text file as follows % mysqlmpmp_backup_tables_files_files_files_files_file mysqlmpsamp_db>/usr/archives/mysql/samp_db The beginning of the output file looks like this #MySQLDump # #Host:localhostDatabase:samp_db # #Serverversionalphalog # Tablestructurefortableabsence # CREATETABLEabsence( student_idint()unsignedDEFAULTNOTNULL datedDEFAULTNOTNULL PRIMARYKEY(student_iddate) ); # # # MySQLDump # #Host:hostDatabase:samp_db # #Serverversionalphalog # #Serverversionalphalog iddate) ); # #Dumpingdatafortableabsence # INSERTINTOabsenceVALUES(); INSERTINTOabsenceVALUES(); INSERTINTOabsenceVALUES(); INSERTINTOabsenceVALUES(); The rest of the file has more INSERT and CREATETABLE statements make up the rest of the file If you want to compress the backup use a command like the following %mysqlmpsamp_db|gzip>/usr/archives/mysql/samp_dbgz If you want a huge database the output file will also be very large and may be difficult to manage if you wish you can use it on the If you wish you can list individual table names after the database name on the mysqlmp command line to dump their contents This breaks the dump file into smaller more manageable files The following example shows how to dump some tables from the samp_db database into separate files %mysqlmpsamp_dbstudentscoreeventabsence> grapbooksql %mysqlmpsamp_dbstudentscoreeventabsence> /usr/archives/mysql/samp_dbgz grapbooksql %mysqlmpsamp_dbmemberpresident>histleaguesql If you are generating a backup file that is intended to be used to periodically refresh the contents of another database you may want to use the adddroptable option which tells the server to write the DROPTABLEIFEXISTS statement to the backup file. Write the backup file and then when you take out the backup file and load it into the second database you won’t get an error if the tables already exist If you dump a database so that you can move it to another server you don’t even have to create the backup file make sure that the database exists on the other host and then pipeline the dumped database so that mysql can read the output of mysqlmp directly for example If you want to copy the database samp_db from the host to a server you can easily do so %mysqladminhcreatesamp_db %mysqlmpsamp_db|mysqlhsamp_db Later on, if you want to refresh the database again skip the mysqladmin command but add the adddroproptable to mysqlmp to avoid the need to create a backup file and then pipe the database so that mysql can read the output of mysqlmp directly. addddroptable to mysqlmp to avoid getting the table already exists error %mysqlmpadddroptablesamp_db|mysqlhsamp_db Other useful options for mysqlmp include flushlogs and locktables The combination of flushlogs and locktables will be helpful for your database checkpoints Locktables locks all the tables that you are dumping while locktables locks all the tables that you are dumping while flushlogs locks the tables that you are dumping. Locktables locks all tables you are dumping while flushlogs closes and reopens the update log file The new update log will only include queries that modify the database from the point of the backup This will set your update log checkpoints to the point of the backup (however if you have clients that need to perform an update locking all tables is not a good thing for clients accessing them during the backup) If you are using flushlogs to set the checkpoints to the point of backup it is probably best to lock them to the point of the backup (if you have clients that need to perform an update locking all tables is not good for client access during the backup) If you use flushlogs to set checkpoints it’s probably best to dump the entire database if you’re dumping individual files it’s harder to synchronize the update log checkpoints with the backup files during recovery you’re usually extracting the contents of the update logs on a per-database basis there’s no option of extracting the updates on an individual table so you’ll have to extract them yourself by default mysqlmp reads the entire contents of a table into memory before writing it which is usually really unnecessary and is not a good idea. In fact if you have a large table that is almost failing you can use the quick option to tell mysqlmp to write each row as soon as it retrieves it to further optimize the dumping process use the opt instead of the quickopt option to turn on other options to speed up the dumping of the data and reading it back out Implementing backups with the opt option is probably the most common way to do this because of the speed benefits of backups however be warned that the opt option does come at a cost. However, be warned that the opt option does come at a cost. opt optimizes your backup process, not other clients’ access to the database. opt prevents anyone from updating any of the tables you’re dumping by locking all of them at once. you can easily see the effect of this on general database access when your database is generally used very frequently and just regulated backups are taken once a day. an option that has the opposite effect of opt is this option makes mysqlmp write INSERTDELAYED statements instead of INSERT statements if you’re loading data files into another database and you want to minimize the impact of this operation on queries that might appear in that database delayed is helpful press option is helpful when you’re copying a database to another machine because it reduces the number of bytes transferred over the network Because it reduces the number of bytes transferred over the network, in the following example, notice that press is given to programs communicating with the server on the remote host and not to programs connecting to the local host %mysqlmpoptsamp_db|mysqlpresshsamp_db mysqlmp has a number of options, as described in the MySQL Reference Manual Using Direct Copy. Another way to back up the database and tables that does not involve mysqlmp is to copy the database table files directly. Typically this is done with a utility such as cptar or cpio. The best way to ensure the integrity of your copy is to shut down the server to copy the files and then restart the server If you do not want to shut down the server, lock the server while you perform a table check The same constraints that apply to copying files apply if the server is running, and you should use the same locking protocols to keep the server quiet Assuming that the server is down or that you have locked the tables that you want to copy The following example shows how to back up the entire samp_db database to a backup directory. db database to a backup directory (DATADIR indicates the server’s data directory) %cdDATADIR %cprsamp_db/usr/archive/mysql Individual tables can be backed up as follows %cdDATADIR/samp_db %cpmember*/usr/archive/mysql/ samp_db %cpscore/cprsamp_db/usr/archive/mysql/ samp_db %cpscore*/usr/archive/mysql/samp_db When you have finished backing up you can lixin/Article/program/MySQL/201311/29384