2023年7月30日发(作者：)

从命令⾏备份和还原SQLServer数据库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, youneed to create a true backup file through SQL Server维护计划中最重要的部分是定期备份数据库。 要备份数据库，您不能简单地复制数据库的相应MDF和LDF⽂件，因为SQLServer对其具有锁定。 相反，您需要通过SQL Server创建⼀个真实的备份⽂件。While this can be done by developing a Maintenance Plan inside of SQL Management Studio, the free Express editions ofSQL Server do not offer this interface. To work around this, you can easily backup your databases by running the commandbelow 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] TODISK=’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:'”SqlCmd -E -S MyServer –Q“备份数据库[MyDB] TO DISK ='D：'”Named SQL Server instance:命名SQL Server实例：SqlCmd -E -S MyServerMyInstance –Q “BACKUP DATABASE [MyDB] TO DISK=’D:'”SqlCmd -E -S MyServerMyInstance –Q“备份数据库[MyDB]到磁盘='D：'”The above create a fully restorable backup copy of “MyDB” to the file “D:” which can be used fordisaster recovery. Of course, you can change the backup location and file to whatever you need, but make sure you specifya folder location which exists on the local machine. This backup file can then be copied to a tape drive or another externalbackup location.上⾯的代码将“ MyDB”的完全可恢复的备份副本创建到⽂件“ D：”，该副本可⽤于灾难恢复。 当然，您可以将备份位置和⽂件更改为所需的任何位置，但是请确保指定了本地计算机上存在的⽂件夹位置。 然后可以将此备份⽂件复制到磁带机或其他外部备份位置。A common question is “Can a backup file be created to a mapped drive or UNC location?” and the quick answer is no. Thereason is because the SQL Server Windows Service runs as a user account which only has access to the local machine. Youcould 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] FROMDISK=’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:'”SqlCmd -E -S MyServer –Q“从DISK =“ D：”还原数据库[MyDB]”The above command will restore a backup of “MyDB” from the data stored in the backup file “D:”.Any changes made to MyDB since the backup file was created will be lost.上⾯的命令将从存储在备份⽂件“ D：”中的数据中恢复“ MyDB”的备份。 ⾃创建备份⽂件以来，对MyDB所做的任何更改都将丢失。An important thing to remember when using the above command is that it is intended to be used on the same SQL Serverthat the respective backup file was created on. SQL backup files store ‘behind the scenes’ information that control whereand how the data files in the backup file are copied. If you are restoring a backup from a different SQL Server, the pathlocations in the backup file may not match the server you are restoring to and an error will result. While this can be workedaround, 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'。