（5.3.5）数据库迁移——sql server数据库与文件组的位置迁移（同一实例）

（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;

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

（1）如果是一个文件组内只有一个文件
　　~~把所有在该文件组内的表删除聚集索引，然后新建聚集索引至新的文件组
（2）如果是一个文件组内多个文件

　　（2.1）把某个文件清空转移到其他文件：使用DBCC SHRINKFILE(要移动数据的数据文件逻辑名称,EMPTYFILE)
　　（2.2）把该文件组内所有文件内数据都转移到另外一个文件组：

　　　　~~首先使用 DBCC SHRINKFILE(要移动数据的数据文件逻辑名称,EMPTYFILE)，把所有数据都转移到同一个文件上去
　　　　~~把所有在该文件组内的表删除聚集索引，然后新建聚集索引至新的文件组

这里要根据是一对多（一个文件组中有多个文件）还是一对一（一个文件组中只有一个文件）来选择移动数据的方法
如果是一对多：使用DBCC SHRINKFILE(要移动数据的数据文件逻辑名称,EMPTYFILE) 把表数据集中到一个文件里去，然后再使用一对一的方式
如果是一对一：删除原有聚集索引，创建新的聚集索引到迁移的文件组

--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]

 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]
 ADD FILE
 (NAME = N'FG_TestUnique_Id_01_data',FILENAME = N'E:\FG_TestUnique_Id_01_data.ndf',SIZE = 1MB, FILEGROWTH = 1MB )
 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]
 SELECT 1,REPLICATE('s',3000)
 GO 500
 
 --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

--9.移除FG_Test_Id_01文件组
ALTER DATABASE TEST
REMOVE FILE FG_TestUnique_Id_01_data

--4.创建表，这个表的数据存放在[FG_Test_Id_01] 文件组上
CREATE TABLE aa(id INT ,cname NVARCHAR(4000)) ON [FG_Test_Id_01] 
GO

--收缩一下FG_Test_Id_01文件组文件
DBCC SHRINKFILE(FG_TestUnique_Id_01_data,EMPTYFILE)

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' 文件组已满。请删除不需要的文件、删除文件组中的对象、将其他文件添加到文件组或为文件组中的现有文件启用自动增长，以便增加可用磁盘空间。

--1.创建表，这个表的数据存放在主文件组上
CREATE TABLE aa(id INT ,cname NVARCHAR(4000)) 
GO

--2.插入数据
INSERT INTO [dbo].[aa]
SELECT 1,REPLICATE('s',3000)
GO 600

--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'

--5.收缩文件
DBCC SHRINKFILE(test2,EMPTYFILE)
 
--6.移除test2数据文件test2.ndf
ALTER DATABASE TEST1
REMOVE FILE test2

--（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