sql server迁移数据（文件组之间的互相迁移与文件组内文件的互相迁移）

2022-11-06 17:42:31

转自：https://www.cnblogs.com/lyhabc/p/3504380.html?utm_source=tuicool

SQLSERVER将数据移到另一个文件组之后清空文件组并删除文件组

总结：

（1）如果是一个文件组内只有一个文件

　　~~把所有在该文件组内的表删除聚集索引，然后新建聚集索引至新的文件组

 （2）如果是一个文件组内多个文件

　　【1】把某个文件清空转移到其他文件：使用DBCC SHRINKFILE(要移动数据的数据文件逻辑名称,EMPTYFILE)

　　【2】把该文件组内所有文件内数据都转移到另外一个文件组：

　　　　~~首先使用 DBCC SHRINKFILE(要移动数据的数据文件逻辑名称,EMPTYFILE)，把所有数据都转移到同一个文件上去

　　　　~~把所有在该文件组内的表删除聚集索引，然后新建聚集索引至新的文件组

--.收缩文件

 DBCC SHRINKFILE(test2,EMPTYFILE)

--.移除test2数据文件test2.ndf

 ALTER DATABASE TEST1

 REMOVE FILE test2

这里要根据是一对多还是一对一来选择移动数据的方法

如果是一对多：使用DBCC SHRINKFILE(要移动数据的数据文件逻辑名称,EMPTYFILE)

如果是一对一：删除原有聚集索引，创建新的聚集索引到迁移的文件组

可以使用sp_help 查看表所在文件组，

可以使用如下查看数据库文件与文件组情况。

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'

sql server迁移数据（文件组之间的互相迁移与文件组内文件的互相迁移）

之前写过一篇文章：SQLSERVER将一个文件组的数据移动到另一个文件组

每个物理文件（数据文件）对应一个文件组的情况（一对一）

如果我把数据移到另一个文件组了，不想要这个已经清空的文件组了，怎麽做？

脚本跟之前那篇文章差不多

 1 USE master

 2 GO

 3

 4

 5 IF EXISTS(SELECT * FROM sys.[databases] WHERE [database_id]=DB_ID('Test'))

 6 DROP DATABASE [Test]

 7

 8 --1.创建数据库

 9 CREATE DATABASE [Test]

10 GO

11

12 USE [Test]

13 GO

14

15

16 --2.创建文件组

17 ALTER DATABASE [Test]

18 ADD FILEGROUP [FG_Test_Id_01]

19

20 ALTER DATABASE [Test]

21 ADD FILEGROUP [FG_Test_Id_02]

22

23

24

25 --3.创建文件

26 ALTER DATABASE [Test]

27 ADD FILE

28 (NAME = N'FG_TestUnique_Id_01_data',FILENAME = N'E:\FG_TestUnique_Id_01_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB )

29 TO FILEGROUP [FG_Test_Id_01];

30

31 ALTER DATABASE [Test]

32 ADD FILE

33 (NAME = N'FG_TestUnique_Id_02_data',FILENAME = N'E:\FG_TestUnique_Id_02_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB )

34 TO FILEGROUP [FG_Test_Id_02];

35

36

37 --4.创建表，这个表的数据存放在[FG_Test_Id_01] 文件组上

38 CREATE TABLE aa(id INT ,cname NVARCHAR(4000)) ON [FG_Test_Id_01]

39 GO

40

41

42 --5.插入数据

43 INSERT INTO [dbo].[aa]

44 SELECT 1,REPLICATE('s',3000)

45 GO 500

46

47

48 --6.查询数据

49 SELECT * FROM [dbo].[aa]

50

51

52 --7.创建聚集索引在[FG_Test_Id_02]文件组上

53 CREATE CLUSTERED INDEX PK_ID ON [dbo].[aa]([id]) WITH(ONLINE=ON) ON [FG_Test_Id_02]

54 GO

55

56

57 --8.我们查看一下文件组的逻辑文件名

58 EXEC [sys].[sp_helpdb] @dbname = TEST -- sysname

59

65

66 --9.移除FG_Test_Id_01文件组

67 ALTER DATABASE TEST

68 REMOVE FILE FG_TestUnique_Id_01_data

当你移动数据到文件组[FG_Test_Id_02]上时，这时候文件组[FG_Test_Id_01]里面已经没有数据了
使用下面的脚本查看

 1 --数据库文件、大小和已经使用空间

 2 USE [Test]  --要查看的当前数据库的使用空间，自动增长大小，数据库文件位置

 3 GO

 4 set nocount on

 5 create table #Data(

 6       FileID int NOT NULL,

 7       [FileGroupId] int NOT NULL,

 8       TotalExtents int NOT NULL,

 9       UsedExtents int NOT NULL,

10       [FileName] sysname NOT NULL,

11       [FilePath] nvarchar(MAX) NOT NULL,

12       [FileGroup] varchar(MAX) NULL)

13

14 create table #Results(

15       db sysname NULL ,

16       FileType varchar(4) NOT NULL,

17       [FileGroup] sysname not null,

18       [FileName] sysname NOT NULL,

19       TotalMB numeric(18,2) NOT NULL,

20       UsedMB numeric(18,2) NOT NULL,

21       PctUsed numeric(18,2) NULL,

22       FilePath nvarchar(MAX) NULL,

23       FileID int null)

24

25 create table #Log(

26       db sysname NOT NULL,

27       LogSize numeric(18,5) NOT NULL,

28       LogUsed numeric(18,5) NOT NULL,

29       Status int NOT NULL,

30       [FilePath] nvarchar(MAX) NULL)

31

32 INSERT #Data (FileID, [FileGroupId], TotalExtents, UsedExtents, [FileName], [FilePath])

33 EXEC ('DBCC showfilestats WITH NO_INFOMSGS')

34

35 update #Data

36 set #Data.FileGroup = sysfilegroups.groupname

37 from #Data, sysfilegroups

38 where #Data.FileGroupId = sysfilegroups.groupid

39

40 INSERT INTO #Results (db, [FileGroup], FileType, [FileName], TotalMB, UsedMB, PctUsed, FilePath, FileID)

41 SELECT DB_NAME() db,

42             [FileGroup],

43             'Data' FileType,

44             [FileName],

45             TotalExtents * 64./1024. TotalMB,

46             UsedExtents *64./1024 UsedMB,

47             UsedExtents*100. /TotalExtents  UsedPct,

48             [FilePath],

49             FileID

50 FROM #Data

51 order BY --1,2

52 DB_NAME(), [FileGroup]

53

54 insert #Log (db,LogSize,LogUsed,Status)

55 exec('dbcc sqlperf(logspace) WITH NO_INFOMSGS ')

56

57 insert #Results(db, [FileGroup], FileType, [FileName],  TotalMB,UsedMB, PctUsed, FilePath, FileID)

58 select DB_NAME() db,

59             'Log' [FileGroup],

60             'Log' FileType,

61             s.[name] [FileName],

62             s.Size/128. as LogSize ,

63             FILEPROPERTY(s.name,'spaceused')/8.00 /16.00 As LogUsedSpace,

64             ((FILEPROPERTY(s.name,'spaceused')/8.00 /16.00)*100)/(s.Size/128.) UsedPct,

65             s.FileName FilePath,

66             s.FileID FileID

67       from #Log l , master.dbo.sysaltfiles f , dbo.sysfiles s

68       where f.dbid = DB_ID()

69       and (s.status & 0x40) <> 0

70       and s.FileID = f.FileID

71       and l.db = DB_NAME()

72

73 SELECT r.db AS "Database",

74 r.FileType AS "File type",

75 CASE

76      WHEN r.FileGroup = 'Log' Then 'N/A'

77      ELSE r.FileGroup

78 END "File group",

79 r.FileName AS "Logical file name",

80 r.TotalMB AS "Total size (MB)",

81 r.UsedMB AS "Used (MB)",

82 r.PctUsed AS "Used (%)",

83 r.FilePath AS "File name",

84 r.FileID AS "File ID",

85 CASE WHEN s.maxsize = -1 THEN null

86     ELSE CONVERT(decimal(18,2), s.maxsize /128.)

87 END "Max. size (MB)",

88 CONVERT(decimal(18,2), s.growth /128.) "Autogrowth increment (MB)"

89 FROM #Results r

90 INNER JOIN dbo.sysfiles s

91 ON r.FileID = s.FileID

92 ORDER BY 1,2,3,4,5

93

94 DROP TABLE #Data

95 DROP TABLE #Results

96 DROP TABLE #Log

使用下面的SQL语句移除文件组[FG_Test_Id_01]就可以了

5 --9.移除FG_Test_Id_01文件组

6 ALTER DATABASE TEST

7 REMOVE FILE FG_TestUnique_Id_01_data

此时就只剩下主文件组和[FG_Test_Id_02]文件组了

注意：如果不使用聚集索引来移动文件组[FG_Test_Id_01]上的数据到文件组[FG_Test_Id_02]

1 --4.创建表，这个表的数据存放在[FG_Test_Id_01] 文件组上

2 CREATE TABLE aa(id INT ,cname NVARCHAR(4000)) ON [FG_Test_Id_01]

3 GO

直接使用下面SQL语句来收缩文件会报错

1 -收缩一下FG_Test_Id_01文件组文件

2 DBCC SHRINKFILE(FG_TestUnique_Id_01_data,EMPTYFILE)

报错内容

1 DBCC SHRINKFILE: 无法移动堆页 3:515。

2 消息 2555，级别 16，状态 1，第 1 行

3 无法将文件 "FG_TestUnique_Id_01_data" 的所有内容移到其他位置，以完成清空文件操作。

4 语句已终止。

5 DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

6 消息 1105，级别 17，状态 2，第 1 行

7 无法为数据库 'Test' 中的对象 'dbo.aa' 分配空间，因为 'FG_Test_Id_01' 文件组已满。请删除不需要的文件、删除文件组中的对象、将其他文件添加到文件组或为文件组中的现有文件启用自动增长，以便增加可用磁盘空间。

因为文件组[FG_Test_Id_01]里还有数据，不能清空

两个物理文件（数据文件）对应一个文件组的情况（一对多）

上面的情况是每个物理文件（数据文件）对应一个文件组的情况

下面这种情况是两个物理文件（数据文件）对于一个文件组的情况

一对一的情况使用聚集索引里移动数据，而一对一的情况使用DBCC SHRINKFILE

创建数据库

test1和test2这两个数据文件归属于主文件组primary，而数据文件test1最大大小为6MB初始大小为5MB

test2数据文件最大大小没有限制

使用下面脚本添加数据到主文件组

 1 --1.创建表，这个表的数据存放在主文件组上

 2 CREATE TABLE aa(id INT ,cname NVARCHAR(4000))

 3 GO

 4

 5

 6 --2.插入数据

 7 INSERT INTO [dbo].[aa]

 8 SELECT 1,REPLICATE('s',3000)

 9 GO 600

10

11

12 --3.查询数据

13 SELECT * FROM [dbo].[aa]

14

15

16

17

18 --4.我们查看一下文件组的逻辑文件名

19 EXEC [sys].[sp_helpdb] @dbname = TEST1

20  -- sysname

21 SELECT  DB_NAME(database_id) AS DatabaseName ,

22         Name AS Logical_Name ,

23         Physical_Name ,

24         ( size * 8 ) / 1024 SizeMB

25 FROM    sys.master_files

26 WHERE   DB_NAME(database_id) = 'Test1'

因为第一个数据文件的最大大小限制，所以有一部分数据插入到了test2.ndf

现在修改test1数据文件的最大大小限制为20MB

相关SQL

1 ALTER DATABASE [Test1] MODIFY FILE(name='Test1',SIZE=5MB, filegrowth=1MB, MAXSIZE=20MB)

执行下面的SQL语句

1 --5.收缩文件

2 DBCC SHRINKFILE(test2,EMPTYFILE)

3

4

5 --6.移除test2数据文件test2.ndf

6 ALTER DATABASE TEST1

7 REMOVE FILE test2

在执行第五条语句的时候，执行下面脚本

 1 --数据库文件、大小和已经使用空间

 2 USE [Test1]  --要查看的当前数据库的使用空间，自动增长大小，数据库文件位置

 3 GO

 4 set nocount on

 5 create table #Data(

 6       FileID int NOT NULL,

 7       [FileGroupId] int NOT NULL,

 8       TotalExtents int NOT NULL,

 9       UsedExtents int NOT NULL,

10       [FileName] sysname NOT NULL,

11       [FilePath] nvarchar(MAX) NOT NULL,

12       [FileGroup] varchar(MAX) NULL)

13

14 create table #Results(

15       db sysname NULL ,

16       FileType varchar(4) NOT NULL,

17       [FileGroup] sysname not null,

18       [FileName] sysname NOT NULL,

19       TotalMB numeric(18,2) NOT NULL,

20       UsedMB numeric(18,2) NOT NULL,

21       PctUsed numeric(18,2) NULL,

22       FilePath nvarchar(MAX) NULL,

23       FileID int null)

24

25 create table #Log(

26       db sysname NOT NULL,

27       LogSize numeric(18,5) NOT NULL,

28       LogUsed numeric(18,5) NOT NULL,

29       Status int NOT NULL,

30       [FilePath] nvarchar(MAX) NULL)

31

32 INSERT #Data (FileID, [FileGroupId], TotalExtents, UsedExtents, [FileName], [FilePath])

33 EXEC ('DBCC showfilestats WITH NO_INFOMSGS')

34

35 update #Data

36 set #Data.FileGroup = sysfilegroups.groupname

37 from #Data, sysfilegroups

38 where #Data.FileGroupId = sysfilegroups.groupid

39

40 INSERT INTO #Results (db, [FileGroup], FileType, [FileName], TotalMB, UsedMB, PctUsed, FilePath, FileID)

41 SELECT DB_NAME() db,

42             [FileGroup],

43             'Data' FileType,

44             [FileName],

45             TotalExtents * 64./1024. TotalMB,

46             UsedExtents *64./1024 UsedMB,

47             UsedExtents*100. /TotalExtents  UsedPct,

48             [FilePath],

49             FileID

50 FROM #Data

51 order BY --1,2

52 DB_NAME(), [FileGroup]

53

54 insert #Log (db,LogSize,LogUsed,Status)

55 exec('dbcc sqlperf(logspace) WITH NO_INFOMSGS ')

56

57 insert #Results(db, [FileGroup], FileType, [FileName],  TotalMB,UsedMB, PctUsed, FilePath, FileID)

58 select DB_NAME() db,

59             'Log' [FileGroup],

60             'Log' FileType,

61             s.[name] [FileName],

62             s.Size/128. as LogSize ,

63             FILEPROPERTY(s.name,'spaceused')/8.00 /16.00 As LogUsedSpace,

64             ((FILEPROPERTY(s.name,'spaceused')/8.00 /16.00)*100)/(s.Size/128.) UsedPct,

65             s.FileName FilePath,

66             s.FileID FileID

67       from #Log l , master.dbo.sysaltfiles f , dbo.sysfiles s

68       where f.dbid = DB_ID()

69       and (s.status & 0x40) <> 0

70       and s.FileID = f.FileID

71       and l.db = DB_NAME()

72

73 SELECT r.db AS "Database",

74 r.FileType AS "File type",

75 CASE

76      WHEN r.FileGroup = 'Log' Then 'N/A'

77      ELSE r.FileGroup

78 END "File group",

79 r.FileName AS "Logical file name",

80 r.TotalMB AS "Total size (MB)",

81 r.UsedMB AS "Used (MB)",

82 r.PctUsed AS "Used (%)",

83 r.FilePath AS "File name",

84 r.FileID AS "File ID",

85 CASE WHEN s.maxsize = -1 THEN null

86     ELSE CONVERT(decimal(18,2), s.maxsize /128.)

87 END "Max. size (MB)",

88 CONVERT(decimal(18,2), s.growth /128.) "Autogrowth increment (MB)"

89 FROM #Results r

90 INNER JOIN dbo.sysfiles s

91 ON r.FileID = s.FileID

92 ORDER BY 1,2,3,4,5

93

94 DROP TABLE #Data

95 DROP TABLE #Results

96 DROP TABLE #Log

你会发现

数据都移动到了test1.mdf里去了

执行第六条SQL语句，删除test2.ndf文件

数据没有丢失

这里关键在于EMPTYFILE参数：DBCC SHRINKFILE(test2,EMPTYFILE)

总结

这里要根据是一对多还是一对一来选择移动数据的方法

如果是一对多：使用DBCC SHRINKFILE(要移动数据的数据文件,EMPTYFILE)

如果是一对一：创建聚集索引

参考文章： [SQL]透過 DBCC SHRINKFILE([要清空的File], 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补充：

这个实验的测试脚本和结果

 1 USE master

 2 GO

 3

 4 --DROP DATABASE [Test]

 5

 6

 7 IF EXISTS(SELECT * FROM sys.[databases] WHERE [database_id]=DB_ID('Test'))

 8 DROP DATABASE [Test]

 9

10 --1.创建数据库

11 CREATE DATABASE [Test]

12 GO

13

14 USE [Test]

15 GO

16

17

18 --2.创建文件组

19 ALTER DATABASE [Test]

20 ADD FILEGROUP [FG_Test_Id_01]

21

22

23

24

25

26 --3.创建文件

27 ALTER DATABASE [Test]

28 ADD FILE

29 (NAME = N'FG_TestUnique_Id_01_data',FILENAME = N'E:\FG_TestUnique_Id_01_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB )

30 TO FILEGROUP [FG_Test_Id_01];

31

32 ALTER DATABASE [Test]

33 ADD FILE

34 (NAME = N'FG_TestUnique_Id_02_data',FILENAME = N'E:\FG_TestUnique_Id_02_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB )

35 TO FILEGROUP [FG_Test_Id_01];

36

37

38

39

40

41 --4.创建表，这个表的数据存放在[FG_Test_Id_02] 文件组上

42 CREATE TABLE aa(id INT ,cname NVARCHAR(4000)) ON [FG_Test_Id_01]

43 GO

44

45

46 --5.插入数据

47 INSERT INTO [dbo].[aa]

48 SELECT 1,REPLICATE('s',3000)

49 GO 1000

50

51

52 --6.查询数据

53 SELECT * FROM [dbo].[aa]

54

55

56

57

58 --7.我们查看一下文件组的逻辑文件名

59 EXEC [sys].[sp_helpdb] @dbname = TEST -- sysname

60

61

62

63 --8.收缩文件

64 DBCC SHRINKFILE(FG_TestUnique_Id_02_data,EMPTYFILE)

65

66

67 --9.移除FG_TestUnique_Id_03_data数据文件FG_TestUnique_Id_03_data.ndf

68 ALTER DATABASE TEST

69 REMOVE FILE FG_TestUnique_Id_02_data

70

71

72

73 --10.查询数据

74 SELECT * FROM [dbo].[aa]

75 SELECT COUNT(*) FROM [dbo].[aa]

数据没有丢失

答案：

FG_TestUnique_Id_02_data.ndf里的数据会移动到FG_TestUnique_Id_01_data.ndf，不会移动到Test.mdf

因为DBCC SHRINKFILE只能在同一文件组内移动数据，而mdf只能属于主文件组primary

表分区

分区表没有数据然后文件组依赖的文件删除了居然没有报错
然后插入数据到分区表报错没有分配文件

码农公寓

转自：https://www.cnblogs.com/lyhabc/p/3504380.html?utm_source=tuicool

SQLSERVER将数据移到另一个文件组之后清空文件组并删除文件组

相关文章