sql数据库分离附加_使用分离和附加方法移动SQL数据库

sql数据库分离附加

This article explores the process of moving a SQL database using the detach and attach method.

本文探讨了使用detach and Attach方法移动SQL数据库的过程。

介绍 (Introduction)

Sometimes we require moving data files (primary, secondary) and log files to different drives and servers. Some of the cases where you might require moving database files are as follows:

有时我们需要将数据文件（主要，辅助）和日志文件移动到不同的驱动器和服务器。 在某些情况下，您可能需要移动数据库文件，如下所示：

• Lack of free space: Suppose you have a disk having less free space. You might move a few files from one drive to another so that you can resolve space issues and database files can grow 可用空间不足 ：假设您的磁盘上的可用空间较少。 您可以将一些文件从一个驱动器移动到另一个驱动器，以便解决空间问题，并且数据库文件可以增长
• Database Management: Sometimes, we require segregating data files and log files into separate drives 数据库管理：有时，我们需要将数据文件和日志文件分离到单独的驱动器中
• Database movement: We might require moving a database to separate server, and in this case, you do not require a database on the source instance 数据库移动：我们可能需要将数据库移动到单独的服务器，在这种情况下，您不需要源实例上的数据库
• Database upgrade: We can also upgrade a database to a higher version by moving the data and log files to a higher version instance
• 数据库升级：我们还可以通过将数据和日志文件移动到更高版本的实例来将数据库升级到更高的版本

There are multiple ways to move a database such as backup & restore, take the database offline. In this article, we will discuss the detach and attach method to move a SQL database.

有多种移动数据库的方法，例如备份和还原，使数据库脱机。 在本文中，我们将讨论移动SQL数据库的分离和附加方法。

使用分离和附加方法移动数据库 (Move a database using detach and attach method)

We can use the following steps for moving a database using detach ad attach method.

我们可以按照以下步骤使用分离广告附加方法移动数据库。

收集SQL数据库信息 (Collecting SQL Database information)

Planning is necessary before we move a database. As you know, a SQL database might contain multiple data files(one primary and multiple secondary) and transaction log files. We should collect the information about existing files, their locations before start moving a database.

在移动数据库之前，必须进行计划。 如您所知，SQL数据库可能包含多个数据文件（一个主文件和多个辅助文件）和事务日志文件。 在开始移动数据库之前，我们应该收集有关现有文件及其位置的信息。

• Run the sp_helpfile command in current database security context and save information separately:

  在当前数据库安全上下文中运行sp_helpfile命令，并分别保存信息：

   

   

  1. Use SQLShack

  2. go

  3. Exec sp_helpfile

   

   

   

  

   

   

• We can also use sys.database_files catalog view and fetch the required information:

  我们还可以使用sys.database_files目录视图并获取所需的信息：

   

   

  1. SELECT file_id,

  2.        type_desc,

  3.        name,

  4.        physical_name,

  5.        state_desc

  6. FROM sys.database_files;

   

   

   

  

   

  In the above screenshots, we see that our database has single data and log file.

  在上面的屏幕截图中，我们看到我们的数据库具有单个数据和日志文件。

• Run the sp_helpdb command with a specific database, also returns the information:

  对特定的数据库运行sp_helpdb命令，还返回以下信息：

   

   

  sp_helpdb 'sqlshack'
  

   

   

   

  

   

   

• Using the GUI method, right-click on the database and properties. In the files section, you can also view files information:

  使用GUI方法，右键单击数据库和属性。 在文件部分，您还可以查看文件信息：

   

  

   

   

In the above screenshots, note that database files are in the default locations. Suppose we want to move these files to ‘C:\sqlshack\Demo’ folder.

在上面的屏幕截图中，请注意数据库文件位于默认位置。 假设我们要将这些文件移动到“ C：\ sqlshack \ Demo”文件夹中。

分离SQL数据库 (Detach a SQL database)

In this method, we first detach the database from the source instance. Right-click on the desired database in SSMS and click on Tasks->Detach:

在这种方法中，我们首先将数据库与源实例分离。 右键单击SSMS中的所需数据库，然后单击“任务”->“分离”：

 

It opens the detach database window, as shown below:

它将打开分离数据库窗口，如下所示：

 

Here, we get the following information’s:

在这里，我们得到以下信息：

• Database name

   

  数据库名称
• Drop connections: We should have any existing connections to the database else SQL Server cannot detach it. Also should look at the message column in the image below, a database has 6 active connections

   

  删除连接：我们应该与数据库建立任何现有连接，否则SQL Server无法分离它。 还应该查看下图中的消息列，一个数据库有6个活动连接

If you click on the active connections message, it gives prompt that you should close the existing connections:

如果单击活动连接消息，则会提示您应关闭现有连接：

 

You can review and close the connections manually. We also have a checkbox drop connections to drop all connections for you before detaching the SQL database.

您可以手动查看并关闭连接。 我们还有一个复选框删除连接，可在分离SQL数据库之前为您删除所有连接。

• Update Statistics: We can use this option for refreshing statistics before detaching the database. Ideally, you should update statistics, especially if you are moving the database to a different SQL instance 更新统计信息：在分离数据库之前，我们可以使用此选项刷新统计信息。 理想情况下，应该更新统计信息，尤其是在将数据库移至其他SQL实例时
• Status: Currently, it shows status not ready due to active connections. 状态 ：目前，由于连接处于活动状态，它显示的状态尚未就绪。

Let’s put a tick on both Drop Connections and Update Statistics before detaching this database as shown below:

如下图所示，在分离此数据库之前，让我们在“ 丢弃连接”和“ 更新统计信息”上打勾：

 

Instead of detaching the database, it is better to generate a script using this wizard and execute the script. This way, you can also learn the equivalent script as well. Under the Script drop-down box, choose the Script Action to New Query Window command:

与其分离数据库，不如使用此向导生成脚本并执行脚本。 这样，您还可以学习等效的脚本。 在“脚本”下拉框中，选择“对新查询窗口执行脚本操作”命令：

 

The similar script as it is shown below will be generated:

将生成如下所示的类似脚本：

1. USE [master]

2. GO

3. ALTER DATABASE [SQLShack] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

4. GO

5. USE [master]

6. GO

7. EXEC master.dbo.sp_detach_db @dbname = N'SQLShack', @skipchecks = 'false'

8. GO

The first script puts the database into single-user mode by rollback existing running queries:

第一个脚本通过回滚现有的正在运行的查询将数据库置于单用户模式：

1. USE [master]

2. GO

3. ALTER DATABASE [SQLShack] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

4. GO

Note: You should verify the AUTO_UPDATE_STATISTICS_ASYNC setting as well. If it is enabled, you cannot access the database into single-user mode.

注意：您还应该验证AUTO_UPDATE_STATISTICS_ASYNC设置。 如果启用，则不能以单用户模式访问数据库。

The second script uses the sp_detach_db stored procedure in the master database. You can see the parameter @skipchecks = false in this query. In the detach database window, we put a check on Update Statistics. This option specifies whether SQL Server to update statistics or not before detaching the database.

第二个脚本使用master数据库中的sp_detach_db存储过程。 您可以在此查询中看到参数@skipchecks = false。 在分离数据库窗口中，我们选中“更新统计信息”。 此选项指定SQL Server在分离数据库之前是否更新统计信息。

• @Skipchecks false: Update statistics true

   

  @Skipchecks false：更新统计信息为true
• @Skipchecks true: Update statistics false

   

  @Skipchecks true：更新统计信息为false

Execute the script, and in the output, it puts the database into single-user mode, performs update statistics and detaches the database:

执行脚本，然后在输出中将数据库置于单用户模式，执行更新统计信息并分离数据库：

 

将SQL数据库文件移动到所需位置 (Move SQL Database files into the desired location)

Now, move the database files from the old location to the new location. It is recommended to use copy-paste instead of cut paste. Copy-paste gives you the flexibility to quickly rollback in case of any unforeseen circumstances.

现在，将数据库文件从旧位置移动到新位置。 建议使用复制粘贴而不是剪切粘贴。 复制粘贴可让您灵活地在任何不可预见的情况下快速回滚。

从新位置附加SQL数据库文件 (Attach SQL Database files from the new location)

Once we have copied the database files into new locations on the desired SQL instance, connect to new instance, right-click on the Databases folder and from the context menu, choose the Attach command:

将数据库文件复制到所需SQL实例上的新位置后，连接到新实例，右键单击Databases文件夹，然后从上下文菜单中选择Attach命令：

 

It opens the Attach Databases window, as shown below:

它将打开“附加数据库”窗口，如下所示：

 

Click the Add button and navigate to the location of the database files, select the MDF file, as shown below:

单击添加按钮，然后导航到数据库文件的位置，选择MDF文件，如下所示：

 

Click OK. SSMS automatically fills the associated files and their location if it is in the same directory. You should validate the path of all database files and modify if required:

单击确定。 如果SSMS位于同一目录中，它将自动填充关联的文件及其位置。 您应该验证所有数据库文件的路径，并根据需要进行修改：

 

By default, it attaches the database with the original database name. We can modify the value for column attach as in the attached database window.

默认情况下，它将使用原始数据库名称附加数据库。 我们可以像在附加的数据库窗口中那样修改列附加的值。

As I always recommend, you should generate a script so that you can learn the equivalent script as well. It generates the following script.

正如我一直建议的那样，您应该生成一个脚本，以便您也可以学习等效的脚本。 它生成以下脚本。

1. USE [master]

2. GO

3. CREATE DATABASE [SQLShack] ON

4. ( FILENAME = N'C:\sqlshack\Demo\SQLShack.mdf' ),

5. ( FILENAME = N'C:\sqlshack\Demo\SQLShack_log.ldf' )

6. FOR ATTACH

7. GO

It uses a create database script with the FOR ATTACH option to attach a database from existing data and log files.

它使用带有FOR ATTACH选项的create database脚本从现有数据和日志文件附加数据库。

Execute this script, and you can see the database in Object Explorer. You can verify the database new location:

执行此脚本，您可以在对象资源管理器中看到该数据库。 您可以验证数据库的新位置：

 

附加和分离方法的局限性 (Limitation of Attach and detach method )

• We cannot detach system databases: master, model, msdb and tempdb

   

  我们无法分离系统数据库：master，model，msdb和tempdb
• We cannot use it for a replicated, mirrored or a database with the snapshot

   

  我们不能将其用于具有快照的复制，镜像或数据库
• A suspect database cannot be moved

   

  可疑数据库无法移动
• You cannot attach a database to a lower version of SQL Server. For example, we cannot attach a SQL 2016 database in SQL 2014 instance

   

  您不能将数据库附加到较低版本SQL Server。 例如，我们无法在SQL 2014实例中附加SQL 2016数据库
• If we attach a database having a higher version, SQL Server maintains the database compatibility. We can change the compatibility level once the database is online

   

  如果我们附加具有更高版本的数据库，则SQL Server会保持数据库兼容性。 数据库联机后，我们可以更改兼容性级别

结论 (Conclusion)

In this article, we explored moving a SQL database using Detach and Attach method. You should review all available options to move a database and choose the appropriate option that suits your requirement.

在本文中，我们探讨了使用Detach and Attach方法移动SQL数据库的方法。 您应该查看所有可用选项以移动数据库，然后选择适合您要求的适当选项。