如何将 SQL Server 2008 R2 数据库从一台机器复制到另一台机器 [英] How to copy SQL Server 2008 R2 database from one machine to another

问题描述

我在 SQL Server 2008 R2 中有一个数据库，我想将此数据库复制到另一台机器上.

I have a database in SQL Server 2008 R2, and I want to copy this database onto another machine.

如何制作副本?

我该如何恢复它?

谢谢

推荐答案

可能有更多方法可以做到这一点，但我通常右键单击数据库并选择任务 → 备份..."，备份类型为完整".之后，您可以将创建的文件复制到目标计算机，在 SQL Server Management Studio 中连接到其 SQL Server，右键单击Databases"文件夹并选择Restore Database".选择设备"并选择文件，然后恢复.
另一种方法是在 SQL Server Management Studio 中编写数据库脚本(右键单击数据库，然后任务 → 生成脚本...").在此过程中，将有一个称为设置脚本选项"的步骤，您必须在其中单击高级"按钮并仔细浏览选项.您肯定要为选项要编写脚本的数据类型"选择数据和架构".如果我真的只想传输数据结构和数据本身，我有时更喜欢这种方法.

There are probably more ways to do this but I usually right-click the database and choose "Tasks → Back up..." with Backup type "Full". After that you can copy the created file to your target machine, connect to its SQL Server in SQL Server Management Studio, right-click the "Databases" folder and choose "Restore Database". Select "Device" and choose the file, then restore.
Another approach would be to script the database in SQL Server Management Studio (right-click the database, then "Tasks → Generate scripts..."). During this process there'll be a step called "Set Scripting Options", where you'll have to click the "Advanced" button and carefully go through the options. You'll definitely want to choose "Data and schema" for the option "Types of data to script". I sometimes prefer this method if I really just want the data structures and the data itself to be transferred.

更新:抱歉，我忘了提及如何为脚本选项恢复数据库.我总是通过在设置脚本选项"步骤中选择保存到新查询窗口"来生成脚本.脚本生成后，只需将其留在那里片刻.
在目标服务器上创建一个与您为其生成脚本的名称相同的新数据库.或者，您也可以在源服务器上为此创建一个脚本(右键单击数据库，选择脚本数据库为→ CREATE TO ... → 剪贴板")并通过右键单击 SSMS 中的服务器节点来执行此脚本对象资源管理器，选择新建查询"，将脚本粘贴到查询窗口并执行.如果您确实需要数据库的完整副本而不仅仅是数据本身，则第二个选项是更好的选择.
一旦您沿着这两条道路中的一条走下去，您应该已经创建了一个新数据库.在对象资源管理器中右键单击该数据库并选择新建查询"，然后将包含数据库结构和数据的脚本复制并粘贴到新查询窗口中并执行查询.这应该可以解决问题.

Update: Sorry, I forgot to mention how to restore the database for the scripting option. I always generate the scripts by selecting "Save to new query window" during the "Set Scripting Options" step. After the script is generated, just leave it there for a moment.
On the target server create a new database with the same name as the one you generated the scripts for. Or you can create a script for that on the source server, too (right-click the database, choose "Script Database as → CREATE TO... → Clipboard") and execute this script by right-clicking the server node in the SSMS Object Explorer, selecting "New query", pasting the script into the query window and executing it. This second option is the better choice if you really need a complete copy of the database and not just the data itself.
Once you've gone down one of these two roads you should have created a new database. Right-click this database in Object Explorer and select "New Query", then copy and paste the script containing the database structure and data into the new query window and execute the query. This should do the trick.

这篇关于如何将 SQL Server 2008 R2 数据库从一台机器复制到另一台机器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！