---创建内存表文件组
ALTER DATABASE [test] ADD FILEGROUP [test_ag] CONTAINS MEMORY_OPTIMIZED_DATA   
GO 
----创建内存表数据库文件
ALTER DATABASE [test] 
ADD FILE  
(  
   NAME = ‘test_memory‘,  
   FILENAME =‘D:\database\memory‘  
)  
TO FILEGROUP [test_ag];  
GO  

USE [test]
GO
ALTER DATABASE [test]  REMOVE FILE [test_memory]
GO

USE test;  
GO  
-- Create a data file and assume it contains data.  
ALTER DATABASE test   
ADD FILE (  
    NAME = Test1data,  
    FILENAME = ‘D:\database\t1data.ndf‘,  
    SIZE = 5MB  
    );  
GO  
-- Empty the data file.  
DBCC SHRINKFILE (test_memory, EMPTYFILE);  
GO  

USE [test]
GO  
EXEC sp_filestream_force_garbage_collection @dbname = N‘test‘ @filename = N‘ test_memory ‘;  

USE [test]
GO
ALTER DATABASE [test]  REMOVE FILE [test_memory]
GO

ALTER LOGIN [test] DISABLE
GO

USE [master]
GO
ALTER DATABASE [test] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE;--将数据库设置成单用户并回滚当前连接

USE [test];---保持连接操作,防止其它用户此时进行连接
GO

CHECKPOINT

---返回当前buffer中每个数据库所占的buffer大小和buffer中脏页的大小
WITH    CTE1
          AS ( SELECT   COUNT(*) * 8 / 1024 AS dirty_cached_size_MB ,
                        COUNT(*) AS dirty_pages,
                        CASE database_id
                          WHEN 32767 THEN ‘ResourceDb‘
                          ELSE DB_NAME(database_id)
                        END AS database_name
               FROM     sys.dm_os_buffer_descriptors
               WHERE    is_modified = 1
               GROUP BY DB_NAME(database_id),database_id
             ),
        CET2
          AS ( SELECT   COUNT(*) * 8 / 1024 AS cached_size_MB ,
                        COUNT(*) AS pages,
                        CASE database_id
                          WHEN 32767 THEN ‘ResourceDb‘
                          ELSE DB_NAME(database_id)
                        END AS database_name
               FROM     sys.dm_os_buffer_descriptors
               GROUP BY DB_NAME(database_id),database_id
             )
    SELECT 
    CET2.database_name,
    CET2.cached_size_MB,
    --CET2.pages,
    CTE1.dirty_cached_size_MB
    --CTE1.dirty_pages 
    FROM CTE1 INNER JOIN CET2 ON CTE1.database_name = CET2.database_name

---将数据库选项改成多用户访问
ALTER DATABASE [test] 
SET MULTI_USER;

/*
1.断开数据库所有连接同时禁止新的连接进来
2.比如禁止登入用户、将实例设为单用户模式等。
*/
----1.设置数据库脱机
USE [master] 
ALTER DATABASE [test] SET  OFFLINE WITH ROLLBACK IMMEDIATE;

----2.手动修改数据库物理文件名，例如将test.mdf改成test_old.mdf

----3.语句修改
USE [master] 
ALTER DATABASE [test] 
MODIFY FILE (NAME = test, FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\test_old.mdf‘);
GO
ALTER DATABASE [test] 
MODIFY FILE (NAME = test_log, FILENAME = ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\test_old_log.ldf‘);
GO

---4.设置数据库在线
USE [master] 
ALTER DATABASE [test] SET  ONLINE


----5.修改数据库逻辑文件名

USE [test]
GO
ALTER DATABASE [test] MODIFY FILE (NAME=N‘test‘, NEWNAME=N‘test_old‘)
GO
USE [test]
GO
ALTER DATABASE [test] MODIFY FILE (NAME=N‘test_log‘, NEWNAME=N‘test_old_log‘)
GO

----6.重命名数据库
USE [master] 
EXEC sp_renamedb N‘test‘, N‘test_old‘;  

----7.查询
SELECT *
FROM sys.master_files
WHERE database_id = DB_ID(‘test_old‘);