Sql 简明教程

SQL Backup Database

在此 SQL 备份数据库教程中，我们将解释如何在 MySQL 和 MS SQL Server 中备份数据库。在原始数据库因电涌或磁盘崩溃等原因损坏或丢失时，拥有数据库备份是非常重要且基本的开发实践。通过练习此操作，可以在数据库发生故障之前对其进行恢复。

In this SQL Backup Database tutorial, we will explain how we can take a backup of a database in MySQL and MS SQL Server. It is very important and basic development practice to have a backup of the database in case the original is corrupted or lost due to power surges or disk crashes etc. By practicing this, the database can be recovered as it was before the failure.

SQL Backup Database Statement

在 SQL 中，BACKUP DATABASE 语句用于创建数据库备份。

In SQL, the BACKUP DATABASE statement is used to create database backups.

Syntax

以下是 SQL 备份数据库语句的语法：

Following is the syntax of SQL Backup Database Statement −

BACKUP DATABASE database_name
TO DISK = 'filepath'
GO

此处，SQL 命令创建数据库名称数据库的备份文件。

Here, the SQL command creates a backup file of the database_name database.

Types of Backups in SQL

在 SQL 中，有三种类型的数据库备份可用。它们如下所示：

In SQL, there are three types of database backups available. These are:

  1. Full Backup

  2. Differential Backup

  3. Transaction Log (T-log) backup

SQL Full Backup

完整备份是 SQL 服务器数据库的完整备份。

A full backup is a complete backup of an SQL server database.

以下是 SQL 完整备份的语法：

Following is the syntax of SQL Full Backup −

BACKUP DATABASE database_name
TO medium = 'filepath'
GO

此处，database_name 是数据库的名称，medium 指存储介质，例如磁盘、磁带或 url。

Here, database_name is the name of the database, medium refers to the storage medium such as disk, tape or url.

SQL Differential Backup

在 Sql 中，您还可以使用 WITH DIFFERENTIAL 命令仅备份新的更改。

In Sql, you can also backup only the new changes by using the WITH DIFFERENTIAL command.

以下是 Sql 差分备份的语法：

Following is the syntax of Sql Differential Backup −

BACKUP DATABASE my_db
TO medium = 'filepath'
WITH DIFFERENTIAL;
GO

此处，database_name 是数据库的名称，medium 指存储设备，例如磁盘、磁带或 url。

Here, database_name is the name of the database, medium refers to storage device such as disk, tape or url.

Transaction Log (T-log) backup

事务日志备份包括自上一次事务日志备份以来所有事务。BACKUP LOG comnmand 用于执行事务日志备份。

A transaction log backup includes all the transactions since the last transaction log backup. BACKUP LOG comnmand is used to perfom the Transaction Log backup.

以下是 Sql 事务日志备份的语法：

Following is the syntax of Sql transaction log backup −

BACKUP LOG database_name
TO medium = 'filepath';
GO

此处，database_name 是数据库的名称，medium 指存储设备，例如磁盘

Here, database_name is the name of the database, medium refers to storage device such as disk

Restore Database From Backup

要还原数据库中的备份文件，我们可以使用 RESTORE DATABASE 命令。

To restore a backup file in Database, we can use the RESTORE DATABASE command.

以下是“从备份恢复数据库”的语法：

Following is the syntax of Restore Database From Backup −

RESTORE DATABASE database_name
FROM DISK = 'filepath';
GO

这里，database_name 是数据库的名称，medium 指磁盘、磁带或 url。

Here, database_name is the name of the database, medium refers to disk, tape or url.

MySQL and MS SQL Database Backup and Restore

以下是 MySQL 和 MS SQL 数据库中创建备份的过程。

Here is the process to create backup in MySQL and MS Sql databases.

Backup MySQL Database

MySQL 命令可用于获取给定数据库的完整备份。此操作将通过命令行执行，且需要数据库用户名和密码，最好是有管理权限。

MySQL mysqldump command can be used to take complete backup of a given database. This operation will be performed from command line and will require database user name and password, preferably admin privilege.

$ mysqldump -u username -p"password" -R testDB > testDB.sql

我们紧跟在我们的密码后面使用 -p 标志，以连接到数据库，其间没有空格。需要 -R 通知 mysqldump 复制存储过程和函数以及数据库中的正常数据。

We are using the -p flag immediately followed by our password to connect to the database with no space between. The -R is required to tell mysqldump to copy stored procedures and functions along with the normal data from the database.

根据数据库大小，以上命令可能需要一段时间来创建最终输出文件 testDB.sql。一旦该命令完成，你将获得 testDB.sql 文件中的一个完整数据库转储，你可以将其安全地保存在任何地方。之后，此文件可以用作还原数据库。

Depending on the database size, above command may take sometime to create a final output file testDB.sql. Once command is completed, you will have a complete database dump in testDB.sql file which you can keep safe anywhere you like. Later this file can be used to restore the database.

Restore MySQL Database

如果我们有数据库转储，那么我们可以使用以下两步过程来还原我们的数据库。第一步是使用提示符命令创建我们的新数据库，如下所示：

If we have a database dump then we can use the following two step process to restore our database. First step is to create our new database using mysqladmin prompt command as follows:

$ mysqladmin -u username -p"password" create tutorialsDB;

下一步是将旧数据库导入到新数据库中，如下所示：

The next step is to import old database into new database shown below :

$ mysql -u username -p"password" tutorialsDB < testDB.sql;

Backup MS SQL Database

如果你使用的是 MS SQL 服务器，那么 SQL 会提供一个简单的 SQL 命令，用于为现有数据库创建备份。

If you are working with MS SQL Server then to create a backup for an existing database, SQL provides us with a simple SQL BACKUP DATABASE command.

以下是 SQL 中 BACKUP DATABASE 命令的语法：

Following is the syntax of the BACKUP DATABASE command in SQL −

BACKUP DATABASE database_name
TO DISK = 'filepath'
GO

以下是为数据库创建备份文件的示例，在驱动器上。

Following is an example to create a backup file for the database testDB on D drive.

SQL> BACKUP DATABASE testDB
TO DISK = 'D:\testDB.bak'
GO

当我们执行上述查询时，输出将获得如下：

When we execute the above query, the output is obtained as follows −

Processed 344 pages for database 'testDB', file 'testDB' on file 1.
Processed 2 pages for database 'testDB', file 'testDB_log' on file 1.
BACKUP DATABASE successfully processed 346 pages in 0.011 seconds (245.383 MB/sec).

Restore MS SQL Database

如果你有一个 MS SQL 数据库的正确备份，那么可以在需要时轻松还原。

If you have a proper backup of an MS SQL database then youc an easily restore it when needed.

以下是 SQL 中 RESTORE DATABASE 命令的语法：

Following is the syntax of the RESTORE DATABASE command in SQL −

RESTORE DATABASE database_name
FROM DISK = 'filepath'
[WITH REPLACE]
GO

如果你希望覆盖现有数据库，这里可以给出选项。

Here WITH REPLACE option can be given if you want to overwrite the existing database.

以下是从驱动器上的备份文件恢复数据库的示例。

Following is an example to restore a database from a backup file testDB.bak available on D drive.

SQL> RESTORE DATABASE testDB
FROM DISK = 'D:\testDB.bak'
WITH REPLACE
GO