|
Восстановление БД с потерянной файловой группой
#39606080
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
lex452,
EMERGENCY не нужен
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
set nocount on;
use [master]
go
if exists (select 1 from sys.databases where name = 'test')
DROP DATABASE [test];
GO
exec xp_cmdshell 'del "C:\TEMP\test_pri.mdf"', no_output
exec xp_cmdshell 'del "C:\TEMP\test_pri.mdf.safe"', no_output
exec xp_cmdshell 'del "C:\TEMP\test_sec.ndf"', no_output
exec xp_cmdshell 'del "C:\TEMP\test_log.ldf"', no_output
exec xp_cmdshell 'del "C:\TEMP\test_log.ldf.safe"', no_output
GO
CREATE DATABASE [test]
ON PRIMARY
( NAME = N'test_pri' , FILENAME = N'C:\TEMP\test_pri.mdf' , SIZE = 64MB , MAXSIZE = UNLIMITED, FILEGROWTH = 5MB ),
FILEGROUP [SECONDARY] DEFAULT
( NAME = N'test_sec' , FILENAME = N'C:\TEMP\test_sec.ndf' , SIZE = 64MB , MAXSIZE = UNLIMITED, FILEGROWTH = 5MB )
LOG ON
( NAME = N'test_log' , FILENAME = N'C:\TEMP\test_log.ldf' , SIZE = 64MB , MAXSIZE = UNLIMITED, FILEGROWTH = 5MB )
GO
use [test]
GO
CREATE PARTITION FUNCTION [PF](int) AS RANGE RIGHT FOR VALUES ( 100 )
GO
CREATE PARTITION SCHEME [PS] AS PARTITION [PF] TO ([PRIMARY], [SECONDARY])
GO
CREATE TABLE [dbo].[PT]( [ID] [INT] NOT NULL ) ON [PS]([ID])
GO
CREATE UNIQUE CLUSTERED INDEX [IDC_PT] ON [dbo].[PT] ( [ID] ) ON [PS]([ID])
GO
INSERT INTO [dbo].[PT] ( [ID] ) VALUES (1);
INSERT INTO [dbo].[PT] ( [ID] ) VALUES (2);
INSERT INTO [dbo].[PT] ( [ID] ) VALUES (3);
INSERT INTO [dbo].[PT] ( [ID] ) VALUES (99);
INSERT INTO [dbo].[PT] ( [ID] ) VALUES (100);
INSERT INTO [dbo].[PT] ( [ID] ) VALUES (101);
INSERT INTO [dbo].[PT] ( [ID] ) VALUES (102);
GO
SELECT * FROM [test].[dbo].[PT]
GO
use [master];
GO
ALTER DATABASE [test] SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO
exec xp_cmdshell 'del "C:\TEMP\test_sec.ndf"', no_output
GO
DBCC CheckPrimaryFile ( N'C:\TEMP\test_pri.mdf' , 3 )
GO
-- Это будет база с потерянным NDF
exec xp_cmdshell 'rename "C:\TEMP\test_pri.mdf" "test_pri.mdf.safe"', no_output
exec xp_cmdshell 'rename "C:\TEMP\test_log.ldf" "test_log.ldf.safe"', no_output
GO
DROP DATABASE [test];
GO
CREATE DATABASE [test]
ON PRIMARY
( NAME = N'test_pri' , FILENAME = N'C:\TEMP\test_pri.mdf' , SIZE = 64MB , MAXSIZE = UNLIMITED, FILEGROWTH = 5MB ),
FILEGROUP [SECONDARY] DEFAULT
( NAME = N'test_sec' , FILENAME = N'C:\TEMP\test_sec.ndf' , SIZE = 64MB , MAXSIZE = UNLIMITED, FILEGROWTH = 5MB )
LOG ON
( NAME = N'test_log' , FILENAME = N'C:\TEMP\test_log.ldf' , SIZE = 64MB , MAXSIZE = UNLIMITED, FILEGROWTH = 5MB )
GO
-- Это потерянный файл NDF
ALTER DATABASE [test] MODIFY FILE ( NAME = 'test_sec' , OFFLINE );
GO
ALTER DATABASE [test] SET OFFLINE WITH ROLLBACK IMMEDIATE;
GO
exec xp_cmdshell 'del "C:\TEMP\test_pri.mdf"', no_output
exec xp_cmdshell 'del "C:\TEMP\test_log.ldf"', no_output
exec xp_cmdshell 'rename "C:\TEMP\test_pri.mdf.safe" test_pri.mdf', no_output
exec xp_cmdshell 'rename "C:\TEMP\test_log.ldf.safe" test_log.ldf', no_output
GO
ALTER DATABASE [test] SET ONLINE;
GO
--ALTER DATABASE [test] SET NEW_BROKER;
GO
select name ,user_access_desc ,state_desc ,service_broker_guid ,is_broker_enabled
from sys.databases where name = N'test'
begin try
SELECT * FROM [test].[dbo].[PT];
end try
begin catch
select 'This may limit the query result.'
end catch
GO
|
|
|
