日期:2014-05-18 浏览次数:20571 次
-- 建测试数据库 create database ilovemk -- 查询文件状态,为online select 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 ONLINE ilovemk 2 ilovemk_log E:\SQLDATA\ilovemk_log.LDF ONLINE -- 数据库设为 offline alter database ilovemk set offline -- 再查询文件状态,为online select 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 ONLINE ilovemk 2 ilovemk_log E:\SQLDATA\ilovemk_log.LDF ONLINE
------解决方案--------------------
-- 建测试数据库 create database ilovemk -- 建测试表 use ilovemk create table tab1(id int,de varchar(10)) insert into tab1 select 1,'a' union all select 2,'b' union all select 3,'c' select * from tab1 id de ----------- ---------- 1 a 2 b 3 c -- 查询文件状态,为online use master select 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 ONLINE ilovemk 2 ilovemk_log E:\SQLDATA\ilovemk_log.LDF ONLINE -- 数据库设为 offline alter database ilovemk set offline -- 查询数据库状态,为 offline select name,state_desc from sys.databases where database_id=db_id('ilovemk') name state_desc ------------------- ilovemk OFFLINE -- 再查询文件状态,依然为online select 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 ONLINE ilovemk 2 ilovemk_log E:\SQLDATA\ilovemk_log.LDF ONLINE -- 附加为新数据库ilovemk2 exec sp_attach_db 'ilovemk2','E:\SQLDATA\ilovemk.mdf','E:\SQLDATA\ilovemk_log.LDF' -- 查询测试表 select * from ilovemk2.dbo.tab1 id de ----------- ---------- 1 a 2 b 3 c
------解决方案--------------------