---創(chuàng)建內存表文件組
ALTER DATABASE [test] ADD FILEGROUP [test_ag] CONTAINS MEMORY_OPTIMIZED_DATA 
GO 
----創(chuàng)建內存表數據庫文件
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');