如何使用 DBCC WritePage 才不会破坏你的数据
如何使用 DBCC WritePage 才不会破坏你的数据
http://www.sqlnotes.info/2013/05/14/protect-yourself-from-using-dbcc-writepage/
I blogged DBCC WritePage a year ago here http://www.sqlnotes.info/2011/11/23/dbcc-writepage/. It’s an extremely dangerous command especially with the last parameter “directORbufferpool” of this command turned on. I also showed you how to fix the page checksum here http://www.sqlnotes.info/2013/05/02/fix-page-checksum/. To protect yourself, please ensure you are operating on the database that you want, not system databases. Taking a full backup before playing this command is important. Never run it in your production. Beside that, there is a simple way to rollback changes made by DBCC WritePage…
When use DBCC Page to read the data. Data will come to the buffer pool first. When the page is not valid, wrong page checksum, conceptually, data will bypass the buffer pool and directly returned to the user.
When use DBCC WritePage to write the data. Data changes will be done in the buffer pool. Buffer pool will harden the changes to disk. What will happen if the file is read only logically? Will DBCC WritePage returns error? Let’s take a look
01
use master
02
if db_id('TestDB') is not null
03
drop database TestDB
04
go
05
create database TestDB
06
GO
07
create table TestDB.dbo.t1(Field1 char(10))
08
go
09
insert into TestDB.dbo.t1 values('AAAAAAAAAA')
10
go
11
select * from TestDB.dbo.t1
12
-- Field1
13
-- ----------
14
-- AAAAAAAAAA
15
go
16
---set the database to read only
17
alter database TestDB set read_only
18
go
19
delete TestDB.dbo.t1
20
--Msg 3906, Level 16, State 1, Line 1
21
--Failed to update database "TestDB" because the database is read-only.
22
go
23
dbcc traceon(3604)
24
dbcc ind(TestDB, t1, 1)
25
26
--PageFID PagePID PageType
27
--------- ----------- --------
28
--1 150 10
29
--1 147 1
30
go
31
dbcc page(TestDB, 1, 147,3)
32
dbcc writepage(TestDB, 1, 147,105, 3, 0x424344 )
33
select * from TestDB.dbo.t1 -- data get changed without any error
34
--Field1
35
------------
36
--AAAAABCDAA
37
go
38
-- this change is done in the memory.
39
-- Now let's change the database state
40
alter database TestDB set read_write
41
go
42
-- Check it again. all changes done by DBCC WritePage are "rolled back"
43
select * from TestDB.dbo.t1
44
--Field1
45
------------
46
--AAAAAAAAAA
Turn your database into read-only mode can protect you from being hurt by DBCC WritePage
John Huang – SQL MCM & MVP, http://www.sqlnotes.info
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 一个奇形怪状的面试题:Bean中的CHM要不要加volatile?
· [.NET]调用本地 Deepseek 模型
· 一个费力不讨好的项目,让我损失了近一半的绩效!
· .NET Core 托管堆内存泄露/CPU异常的常见思路
· PostgreSQL 和 SQL Server 在统计信息维护中的关键差异
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 上周热点回顾(2.17-2.23)
2013-10-25 带您理解SQLSERVER是如何执行一个查询的
2013-10-25 SQL Server 2000中的并行处理和执行计划中的位图运算符
2012-10-25 SQLSERVER model数据库