从命令行备份和还原SQL Server数据库

The most important part of a SQL Server maintenance plan is backing up your databases regularly. To backup a database, you cannot simply copy the respective MDF and LDF files of the database as SQL Server has a lock on these. Instead, you need to create a true backup file through SQL Server.

SQL Server维护计划中最重要的部分是定期备份数据库。 要备份数据库，您不能简单地复制数据库的相应MDF和LDF文件，因为SQL Server对其具有锁定。 相反，您需要通过SQL Server创建一个真实的备份文件。

While this can be done by developing a Maintenance Plan inside of SQL Management Studio, the free Express editions of SQL Server do not offer this interface. To work around this, you can easily backup your databases by running the command below while logged in as a Windows Administrator:

尽管可以通过在SQL Management Studio内部制定维护计划来完成此操作，但是SQL Server的免费Express版本不提供此接口。 要解决此问题，您可以在以Windows管理员身份登录时通过运行以下命令来轻松备份数据库：

SqlCmd -E -S Server_Name –Q “BACKUP DATABASE [Name_of_Database] TO DISK=’X:PathToBackupLocation[Name_of_Database].bak'”

SqlCmd -E -S Server_Name –Q“备份数据库[名称数据库] TO DISK ='X：PathToBackupLocation [名称数据库] .bak'”

The examples below will help.

以下示例将有所帮助。

Default SQL Server instance:

默认SQL Server实例：

SqlCmd -E -S MyServer –Q “BACKUP DATABASE [MyDB] TO DISK=’D:BackupsMyDB.bak'”

SqlCmd -E -S MyServer –Q“备份数据库[MyDB] TO DISK ='D：BackupsMyDB.bak'”

Named SQL Server instance:

命名SQL Server实例：

SqlCmd -E -S MyServerMyInstance –Q “BACKUP DATABASE [MyDB] TO DISK=’D:BackupsMyDB.bak'”

SqlCmd -E -S MyServerMyInstance –Q“备份数据库[MyDB]到磁盘='D：BackupsMyDB.bak'”

The above create a fully restorable backup copy of “MyDB” to the file “D:BackupsMyDB.bak” which can be used for disaster recovery. Of course, you can change the backup location and file to whatever you need, but make sure you specify a folder location which exists on the local machine. This backup file can then be copied to a tape drive or another external backup location.

上面的代码将“ MyDB”的完全可恢复的备份副本创建到文件“ D：BackupsMyDB.bak”，该副本可用于灾难恢复。 当然，您可以将备份位置和文件更改为所需的任何位置，但是请确保指定了本地计算机上存在的文件夹位置。 然后可以将此备份文件复制到磁带机或其他外部备份位置。

A common question is “Can a backup file be created to a mapped drive or UNC location?” and the quick answer is no. The reason is because the SQL Server Windows Service runs as a user account which only has access to the local machine. You could change the account the service runs as, but this is highly discouraged for security reasons.

一个常见的问题是“可以将备份文件创建到映射的驱动器或UNC位置吗？” 快速答案是不。 原因是因为SQL Server Windows服务以只能访问本地计算机的用户帐户身份运行。 您可以更改服务运行时使用的帐户，但是出于安全原因，强烈建议不要这样做。

从命令行还原数据库备份 (Restoring a Database Backup from the Command Line)

To restore a database from a backup file, simply use the command:

要从备份文件还原数据库，只需使用以下命令：

SqlCmd -E -S Server_Name –Q “RESTORE DATABASE [Name_of_Database] FROM DISK=’X:PathToBackupFile[File_Name].bak'”

SqlCmd -E -S Server_Name –Q“从DISK =“ X：PathToBackupFile [File_Name] .bak”还原数据库[Name_of_Database]

For example:

例如：

SqlCmd -E -S MyServer –Q “RESTORE DATABASE [MyDB] FROM DISK=’D:BackupsMyDB.bak'”

SqlCmd -E -S MyServer –Q“从DISK =“ D：BackupsMyDB.bak”还原数据库[MyDB]”

The above command will restore a backup of “MyDB” from the data stored in the backup file “D:BackupsMyDB.bak”. Any changes made to MyDB since the backup file was created will be lost.

上面的命令将从存储在备份文件“ D：BackupsMyDB.bak”中的数据中恢复“ MyDB”的备份。 自创建备份文件以来，对MyDB所做的任何更改都将丢失。

An important thing to remember when using the above command is that it is intended to be used on the same SQL Server that the respective backup file was created on. SQL backup files store ‘behind the scenes’ information that control where and how the data files in the backup file are copied. If you are restoring a backup from a different SQL Server, the path locations in the backup file may not match the server you are restoring to and an error will result. While this can be worked around, it is much easier to restore backups created on another SQL Server using the SQL Management Studio tool.

使用上述命令时要记住的重要一点是，它应与创建相应备份文件SQL Server一起使用。 SQL备份文件存储“幕后”信息，这些信息控制在何处以及如何复制备份文件中的数据文件。 如果要从其他SQL Server恢复备份，则备份文件中的路径位置可能与您要恢复到的服务器不匹配，并且会导致错误。 尽管可以解决此问题，但是使用SQL Management Studio工具还原在另一个SQL Server上创建的备份要容易得多。

Note: the commands above will work on SQL 2005 and higher (any edition). For SQL 2000 and earlier, replace ‘SqlCmd’ with ‘oSql’.

注意：上面的命令将在SQL 2005和更高版本(任何版本)上运行。 对于SQL 2000和更早版本，将'SqlCmd'替换为'oSql'。

翻译自: https://www.howtogeek.com/50295/backup-your-sql-server-database-from-the-command-line/