（5.3.5）数据库迁移——sqlserver数据库与⽂件组的位置迁移（同⼀实例）【1】数据库物理⽂件迁移
四种均需要离线
【1.1】脱机迁移
SQL Server创建新库时，默认会把数据存放在C盘中，⼀旦中的存储数据多了以后，C盘的空间就会所剩⽆⼏。解决⽅案是将存放数据的物理⽂件迁移到其他盘。具体流程为：（1）将现有的数据库脱机
ALTER DATABASE DB1 SET OFFLINE WITH ROLLBACK IMMEDIATE;
（2）将数据库⽂件移到新的位置
⽂件复制完成以后需要：右键-属性-安全-在组或⽤户名处添加Authenticated Users-更改该组权限为完全权限，否则接下来的操作会报
中间可能存在的问题：
消息5120，级别16，状态101，第17⾏
⽆法打开物理⽂件“D:\MSSQL\DATA\testdb.mdf”。操作系统错误5:“5(拒绝访问。)”。
消息5120，级别16，状态101，第17⾏
⽆法打开物理⽂件“D:\MSSQL\DATA\testdb _log.ldf”。操作系统错误5:“5(拒绝访问。)”。
消息5181，级别16，状态5，第17⾏
⽆法重新启动数据库“ctrip”。将恢复到以前的状态。
消息5069，级别16，状态1，第17⾏
ALTER DATABASE 语句失败。
（3）修改数据库关联⽂件的指向
ALTER DATABASE DB1 MODIFY FILE(NAME = DB1, FILENAME = X:\SQLServer\DB1.mdf);
ALTER DATABASE DB1 MODIFY FILE(NAME = DB1_Log, FILENAME = X:\SQLServer\DB1_Log.ldf);
（4）将数据库进⾏联机
ALTER DATABASE DB1 SET ONLINE;
【1.2】在线修改⽂件位置
在线修改⽂件位置，但也需要服务重启才能⽣效，或者offline => online
MS SQL 数据库迁移⽂件，这⾥说的不是将数据库迁移到另外⼀台服务器，只是在服务器不同磁盘⽬录内做迁移。移动数据库⽂件的情况⼤致有下⾯⼀些：
1: 事先没有规划好，数据库⽂件或⽇志⽂件增长过快，导致某个盘或整个磁盘空间不⾜，需要移动数据⽂件或⽇志⽂件
2: 纯粹由于业务需求，数据增长过快。
3: 为了更好的IO的性能，需要将数据⽂件、⽇志⽂件分布到不同磁盘，减轻IO压⼒，提供IO性能。
4：故障恢复。例如，数据库处于可疑模式或因硬件故障⽽关闭。
案例：现在我在数据库实例中有数据库MyAssistant,(假设)由于事先没有规划好，导致数据⽂件位于E:\DataBase⽬录下, 我们需要将数据⽂件移动到D:\DataBase_Data⽬录下，
将⽇志⽂件移动到F:\DataBase_Log⽬录下。
步骤1：对数据库中每个要移动的⽂件（数据⽂件/⽇志⽂件），通过下⾯命令指定到新的⽬录
--查看逻辑名
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');
--迁移位置
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'D:\tempdb\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'D:\tempdb\templog.ldf');
GO
--停⽌服务，复制⽂件到指定位置
--开启服务
如果有多个数据库的数据⽂件/⽇志⽂件需要移动，可以通过⼀系列上述命令执⾏
ALTER DATABASE DATABASE_ID1
MODIFY FILE(NAME='DATABASE_NAME', FILENAME='....mdf');
ALTER DATABASE DATABASE_ID2
MODIFY FILE(NAME='DATABASE_NAME', FILENAME=.....mdf');
.......
步骤2：
将转移数据库脱机然后将数据库复制到，设定好的盘符下，如果⽂件名也改了，那也要改。再联机
【1.3】分离附加
（1）分离
EXEC sp_detach_db 'test'
（2）复制⽂件到⾃⼰想要的位置
（3）附加
EXEC sp_attach_db @dbname = test', @filename1 =@data_file, @filename2 = @log_file
【1.4】备份还原
restore move，with move恢复数据库
USE [master]
RESTORE DATABASE [test]
FROM DISK = N'D:\DBBackup\testfull.bak' WITH FILE = 1,
MOVE N'test' TO N'D:\MSSQL\test.mdf',
MOVE N'test_log' TO N'D:\MSSQL\test_log.ldf',
NOUNLOAD,NORECOVERY , STATS = 5
【2】⽂件组与⽂件迁移
【2.1】迁移策略
（1）如果是⼀个⽂件组内只有⼀个⽂件
~~把所有在该⽂件组内的表删除聚集索引，然后新建聚集索引⾄新的⽂件组
（2）如果是⼀个⽂件组内多个⽂件
（2.1）把某个⽂件清空转移到其他⽂件：使⽤DBCC SHRINKFILE(要移动数据的数据⽂件逻辑名称,EMPTYFILE)
（2.2）把该⽂件组内所有⽂件内数据都转移到另外⼀个⽂件组：
~~⾸先使⽤DBCC SHRINKFILE(要移动数据的数据⽂件逻辑名称,EMPTYFILE)，把所有数据都转移到同⼀个⽂件上去
~~把所有在该⽂件组内的表删除聚集索引，然后新建聚集索引⾄新的⽂件组
这⾥要根据是⼀对多（⼀个⽂件组中有多个⽂件）还是⼀对⼀（⼀个⽂件组中只有⼀个⽂件）来选择移动数据的⽅法
如果是⼀对多：使⽤DBCC SHRINKFILE(要移动数据的数据⽂件逻辑名称,EMPTYFILE) 把表数据集中到⼀个⽂件⾥去，然后再使⽤⼀对⼀的⽅式如果是⼀对⼀：删除原有聚集索引，创建新的聚集索引到迁移的⽂件组
可以使⽤sp_help 查看表所在⽂件组，
可以使⽤如下查看数据库⽂件与⽂件组情况。
--1.查看数据库⽂件与⽂件组情况
EXEC[sys].[sp_helpdb]@dbname= TEST1
-- sysname
SELECT DB_NAME(database_id) AS DatabaseName ,
Name AS Logical_Name ,
Physical_Name ,
( size *8 ) /1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) ='Test1'
--2.收缩⽂件，转移到⽂件组其他⽂件去
DBCC SHRINKFILE(test2,EMPTYFILE)
--3.移除数据库test1中的数据⽂件test2.ndf
ALTER DATABASE TEST1
REMOVE FILE test2
--4.创建表，这个表的数据存放在[FG_Test_Id_01] ⽂件组上
CREATE TABLE aa(id INT ,cname NVARCHAR(4000)) ON[FG_Test_Id_01]
--5.创建聚集索引在[FG_Test_Id_02]⽂件组上
CREATE CLUSTERED INDEX PK_ID ON[dbo].[aa]([id]) WITH(ONLINE=ON) ON[FG_Test_Id_02]
正⽂：
sql server迁移数据（⽂件组之间的互相迁移与⽂件组内⽂件的互相迁移）
之前写过⼀篇⽂章：
【2.2】⼀对⼀（⼀个⽂件组⼀个数据⽂件）
每个物理⽂件（数据⽂件）对应⼀个⽂件组的情况（⼀对⼀）
如果我把数据移到另⼀个⽂件组了，不想要这个已经清空的⽂件组了，怎麽做？
删除原有聚集索引，创建新的聚集索引到迁移的⽂件组
USE master
GO
IF EXISTS(SELECT*FROM sys.[databases]WHERE[database_id]=DB_ID('Test'))
DROP DATABASE[Test]
--1.创建数据库
CREATE DATABASE[Test]
GO
USE[Test]
GO
--2.创建⽂件组
ALTER DATABASE[Test]
ADD FILEGROUP [FG_Test_Id_01]
ALTER DATABASE[Test]
ADD FILEGROUP [FG_Test_Id_02]
--3.创建⽂件
ALTER DATABASE[Test]
TO FILEGROUP [FG_Test_Id_01];
ALTER DATABASE[Test]
ADD FILE
(NAME = N'FG_TestUnique_Id_02_data',FILENAME = N'E:\FG_TestUnique_Id_02_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB )
TO FILEGROUP [FG_Test_Id_02];
--4.创建表，这个表的数据存放在[FG_Test_Id_01] ⽂件组上
CREATE TABLE aa(id INT ,cname NVARCHAR(4000)) ON[FG_Test_Id_01]
GO
--5.插⼊数据
INSERT INTO[dbo].[aa]
SELECT1,REPLICATE('s',3000)
GO500
--6.查询数据
SELECT*FROM[dbo].[aa]
--7.创建聚集索引在[FG_Test_Id_02]⽂件组上
CREATE CLUSTERED INDEX PK_ID ON[dbo].[aa]([id]) WITH(ONLINE=ON) ON[FG_Test_Id_02]
GO
--8.我们查看⼀下⽂件组的逻辑⽂件名
EXEC[sys].[sp_helpdb]@dbname= TEST -- sysname
--9.移除FG_Test_Id_01⽂件组
ALTER DATABASE TEST
REMOVE FILE FG_TestUnique_Id_01_data
当你移动数据到⽂件组[FG_Test_Id_02]上时，这时候⽂件组[FG_Test_Id_01]⾥⾯已经没有数据了
使⽤下⾯的脚本查看
使⽤下⾯的SQL语句移除⽂件组[FG_Test_Id_01]就可以了
--9.移除FG_Test_Id_01⽂件组
ALTER DATABASE TEST
REMOVE FILE FG_TestUnique_Id_01_data
此时就只剩下主⽂件组和[FG_Test_Id_02]⽂件组了
注意：如果不使⽤聚集索引来移动⽂件组[FG_Test_Id_01]上的数据到⽂件组[FG_Test_Id_02]
--4.创建表，这个表的数据存放在[FG_Test_Id_01] ⽂件组上
CREATE TABLE aa(id INT ,cname NVARCHAR(4000)) ON[FG_Test_Id_01]
GO
直接使⽤下⾯SQL语句来收缩⽂件会报错
--收缩⼀下FG_Test_Id_01⽂件组⽂件
DBCC SHRINKFILE(FG_TestUnique_Id_01_data,EMPTYFILE)
sqlserver备份表语句报错内容
DBCC SHRINKFILE: ⽆法移动堆页3:515。
消息2555，级别16，状态1，第1⾏
⽆法将⽂件 "FG_TestUnique_Id_01_data" 的所有内容移到其他位置，以完成清空⽂件操作。
语句已终⽌。
DBCC执⾏完毕。如果DBCC输出了错误信息，请与系统管理员联系。
消息1105，级别17，状态2，第1⾏
⽆法为数据库'Test'中的对象'dbo.aa'分配空间，因为'FG_Test_Id_01'⽂件组已满。请删除不需要的⽂件、删除⽂件组中的对象、将其他⽂件添加到⽂件组或为⽂件组中的现有⽂件启⽤⾃动增长，以便增加可⽤磁盘空间。因为⽂件组[FG_Test_Id_01]⾥还有数据，不能清空
【2.3】⼀对多（⼀个⽂件组有多个数据⽂件）
两个物理⽂件（数据⽂件）对应⼀个⽂件组的情况（⼀对多）
上⾯的情况是每个物理⽂件（数据⽂件）对应⼀个⽂件组的情况
下⾯这种情况是两个物理⽂件（数据⽂件）对于⼀个⽂件组的情况
⼀对⼀的情况使⽤聚集索引⾥移动数据，⽽⼀对多的情况使⽤DBCC SHRINKFILE
创建数据库
test1和test2这两个数据⽂件归属于主⽂件组primary，⽽数据⽂件test1最⼤⼤⼩为6MB初始⼤⼩为5MB
test2数据⽂件最⼤⼤⼩没有限制
使⽤下⾯脚本添加数据到主⽂件组
--1.创建表，这个表的数据存放在主⽂件组上
CREATE TABLE aa(id INT ,cname NVARCHAR(4000))
INSERT INTO[dbo].[aa]
SELECT1,REPLICATE('s',3000)
GO600
--3.查询数据
SELECT*FROM[dbo].[aa]
--4.我们查看⼀下⽂件组的逻辑⽂件名
EXEC[sys].[sp_helpdb]@dbname= TEST1
-- sysname
SELECT DB_NAME(database_id) AS DatabaseName ,
Name AS Logical_Name ,
Physical_Name ,
( size *8 ) /1024 SizeMB
FROM sys.master_files
WHERE DB_NAME(database_id) ='Test1'
因为第⼀个数据⽂件的最⼤⼤⼩限制，所以有⼀部分数据插⼊到了test2.ndf
现在修改test1数据⽂件的最⼤⼤⼩限制为20MB
相关SQL
执⾏下⾯的SQL语句
--5.收缩⽂件
DBCC SHRINKFILE(test2,EMPTYFILE)
--6.移除test2数据⽂件test2.ndf
ALTER DATABASE TEST1
REMOVE FILE test2
在执⾏第五条语句的时候，执⾏下⾯脚本
你会发现
数据都移动到了test1.mdf⾥去了
执⾏第六条SQL语句，删除test2.ndf⽂件
数据没有丢失
这⾥关键在于EMPTYFILE参数：DBCC SHRINKFILE(test2,EMPTYFILE)
总结
这⾥要根据是⼀对多还是⼀对⼀来选择移动数据的⽅法
如果是⼀对多：使⽤DBCC SHRINKFILE(要移动数据的数据⽂件,EMPTYFILE)
如果是⼀对⼀：创建聚集索引
参考⽂章：
⼤家可以做⼀下实验
对于同⼀个⽂件组⾥的多个数据⽂件（不⼀定是主⽂件组），
⽐如有有个⽂件组叫[FG_Test_01]，⾥⾯有两个数据⽂件test3.ndf和test4.ndf
test3.ndf和test4.ndf都有数据
如果我运⾏DBCC SHRINKFILE(test4,EMPTYFILE)，test4.ndf⾥的数据是否会移动到test3.ndf还是会移动到test1.mdf 这个实验留给⼤家o(∩_∩)o
2014-1-14补充：
这个实验的测试脚本和结果
答案：
FG_TestUnique_Id_02_data.ndf⾥的数据会移动到FG_TestUnique_Id_01_data.ndf，不会移动到Test.mdf
因为DBCC SHRINKFILE只能在同⼀⽂件组内移动数据，⽽mdf只能属于主⽂件组primary
表分区
分区表没有数据然后⽂件组依赖的⽂件删除了居然没有报错
然后插⼊数据到分区表报错没有分配⽂件
【3】⽇志⽂件迁移
【3.1】离线迁移
直接使⽤【1】中的迁移办法即可；
【3.2】在线新增⽂件
如果我们的ldf在⼀个不太好的磁盘⾥，⽽业务⼜正在允许不停机，那么⽤这个办法还是不错的。
（1）新增⼀个⽇志⽂件到其他的盘符
--（1.1）创建⽇志⽂件
ALTER DATABASE[test]
ADD LOG FILE
(
NAME ='test_log1',
FILENAME='D:\program files\sqlserver2017\sqlserver workspace\E_Market_log1.ldf',
SIZE=5MB,
FILEGROWTH=0
)
--（1.2）把原来的⽇志⽂件停⽤，通过禁⽌⾃动增长的⽅式，代码如下，也可以⽤SSMS操作
USE[test]
GO
DBCC SHRINKFILE (N'test_Log' , 64) --这⾥的值是其当前ldf⼤⼩，也可以不⽤收缩
GO
USE[master]
GO
ALTER DATABASE[Db_Logs] MODIFY FILE ( NAME = N'Db_Logs_Log', MAXSIZE = UNLIMITED, FILEGROWTH =0) --这才是核⼼GO
--注意，只能把原⽇志停⽤，但不能删除！做完这个之后也不能随意收缩了
参考代码：在建库的时候创建多个数据与⽇志⽂件
GO
CREATE DATABASE E_Market
ON PRIMARY--主⽂件组
(
NAME ='E_Market_data',
FILENAME='D:\program files\sqlserver2017\sqlserver workspace\E_Market_data.mdf',
SIZE=10MB,
MAXSIZE=500MB,
FILEGROWTH=10%
),--第⼀个⽂件组结束
FILEGROUP FG--第⼆个⽂件组
(
NAME ='FG_E_Market_data',
FILENAME='D:\program files\sqlserver2017\sqlserver workspace\FG_E_Market_data.ndf',
SIZE=10MB,
FILEGROWTH=0
)
--⽇志⽂件不属于任何⽂件组
LOG ON
(
NAME ='E_Market_log',
FILENAME='D:\program files\sqlserver2017\sqlserver workspace\E_Market_log.ldf',
SIZE=5MB,
FILEGROWTH=0
),
--⽇志2的具体描述
(
NAME ='E_Market_log1',
FILENAME='D:\program files\sqlserver2017\sqlserver workspace\E_Market_log1.ldf',
SIZE=5MB,
FILEGROWTH=0
)
GO
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论