четверг, сентября 07, 2006

Bug in MS SQL 2k in the backup/restore subsystem

Hi All! Another tech note on MS SQL. During testing of SP4 for MS SQL 2k I've found the bug in such critical place as backup/restore subsystem - if you put a database into readonly mode and then backup up the log, you'll be unable to restore this log at some point of time or savepoint - database will come into 'suspected' state. This bug also exists in the previous versions of MS SQL 2k. It is not fixed yet. Guys from MS let me to share this info. So, here is the repro (don't run it if you already have a database named 'x'!!!):
set nocount on
go
drop database x
create database x
go
backup database x to disk='c:\x.bak' with format
use x
create table t1 (id int,c1 char(4100))
go
declare @id int
select @id = 1
while @id <= 100
begin
insert into t1 values (@id,'junk')
select @id = @id + 1
end
go
backup log x to disk='c:\x.log.bak' with format
go
declare @id int
select @id = 1
while @id <= 100
begin
insert into t1 values (@id,'junk')
select @id = @id + 1
end
go
begin tran deltran with mark
delete from t1
commit
go
alter database x set read_only
go
backup log x to disk='c:\x.log.bak' with no_truncate
go
use master
go
drop database x
go
restore database x from disk='c:\x.bak' with norecovery
restore log x from disk='c:\x.log.bak' with norecovery
go
restore log x from disk='c:\x.log.bak' with file=2,recovery,stopbeforemark='deltran'
go
dbcc checkdb ('x') with no_infomsgs
go
select count(*) from x..t1
go
select * from x..t1
go

Комментариев нет: