关于文件状态的问题
文件状态和数据库状态的关系
还是引用官方文档的一句话:
“在 SQL Server 中,数据库文件的状态独立于数据库的状态。文件始终处于一个特定状态,例如 ONLINE 或 OFFLINE。若要查看文件的当前状态,请使用 sys.master_files 或 sys.database_files 目录视图。如果数据库处于离线状态,则可以从 sys.master_files 目录视图中查看文件的状态。”
这我就有点疑惑了,难道数据库离线了文件还能在线使用?
[解决办法]
have a test,
-- 建测试数据库create database ilovemk-- 查询文件状态,为onlineselect db_name(database_id) database_name,file_id,name,physical_name,state_desc from sys.master_files where database_id=db_id('ilovemk')database_name file_id name physical_name state_desc-----------------------------------------ilovemk 1 ilovemk E:\SQLDATA\ilovemk.mdf ONLINEilovemk 2 ilovemk_log E:\SQLDATA\ilovemk_log.LDF ONLINE-- 数据库设为 offlinealter database ilovemk set offline-- 再查询文件状态,为onlineselect db_name(database_id) database_name,file_id,name,physical_name,state_desc from sys.master_files where database_id=db_id('ilovemk')database_name file_id name physical_name state_desc-----------------------------------------ilovemk 1 ilovemk E:\SQLDATA\ilovemk.mdf ONLINEilovemk 2 ilovemk_log E:\SQLDATA\ilovemk_log.LDF ONLINE
[解决办法]
-- 建测试数据库create database ilovemk-- 建测试表use ilovemkcreate table tab1(id int,de varchar(10))insert into tab1 select 1,'a' union allselect 2,'b' union allselect 3,'c'select * from tab1id de----------- ----------1 a2 b3 c-- 查询文件状态,为onlineuse masterselect db_name(database_id) database_name,file_id,name,physical_name,state_desc from sys.master_files where database_id=db_id('ilovemk')database_name file_id name physical_name state_desc-----------------------------------------ilovemk 1 ilovemk E:\SQLDATA\ilovemk.mdf ONLINEilovemk 2 ilovemk_log E:\SQLDATA\ilovemk_log.LDF ONLINE-- 数据库设为 offlinealter database ilovemk set offline-- 查询数据库状态,为 offlineselect name,state_descfrom sys.databases where database_id=db_id('ilovemk')name state_desc-------------------ilovemk OFFLINE-- 再查询文件状态,依然为onlineselect db_name(database_id) database_name,file_id,name,physical_name,state_desc from sys.master_files where database_id=db_id('ilovemk')database_name file_id name physical_name state_desc-----------------------------------------ilovemk 1 ilovemk E:\SQLDATA\ilovemk.mdf ONLINEilovemk 2 ilovemk_log E:\SQLDATA\ilovemk_log.LDF ONLINE-- 附加为新数据库ilovemk2exec sp_attach_db 'ilovemk2','E:\SQLDATA\ilovemk.mdf','E:\SQLDATA\ilovemk_log.LDF'-- 查询测试表select * from ilovemk2.dbo.tab1id de----------- ----------1 a2 b3 c
[解决办法]
-- 附加为新数据库ilovemk2exec sp_attach_db 'ilovemk2','E:\SQLDATA\ilovemk.mdf','E:\SQLDATA\ilovemk_log.LDF'