ASE Database forum - Verifying dump

Author

Verifying dump

Barlow

2005-12-15, 8:25 pm

I am interested in anyone's steps to ensure they have a
valid backup of their databases.
We have experienced issues when dumping the database,
capturing rowcounts, then restoring from the backup,
capturing rocounts. The rowcounts do not match. No one has
access to the server between the dump and load except sa.

Sherlock, Kevin

2005-12-16, 8:25 pm

how do you capture rowcounts? Do you use the builtin function rowcnt(), "select
count(*)", or "select rowcnt from systabstats" ?
<Barlow> wrote in message news:43a1cb54.2fbc.1681692777@sybase.com...
> I am interested in anyone's steps to ensure they have a
> valid backup of their databases.
> We have experienced issues when dumping the database,
> capturing rowcounts, then restoring from the backup,
> capturing rocounts. The rowcounts do not match. No one has
> access to the server between the dump and load except sa.

Barlow

2005-12-20, 8:25 pm

We are running a sp we created.
IF OBJECT_ID('dbo.sp_get_rowcounts') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_get_rowcounts
IF OBJECT_ID('dbo.sp_get_rowcounts') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE
dbo.sp_get_rowcounts >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo. sp_get_rowcounts[col
or=darkred]
END
go
create proc sp_get_rowcounts
as
INSERT RTIwork..rowcounts
SELECT db_name(),
-- USER_NAME(uid),
O. name,rowcnt(doampg),

-- S.name,creation = O.crdate,
case
sysstat2 & 57344
when 32768 then 'datarows' when 16384 then 'datapages'
else 'allpages'
end
FROM sysobjects O, sysindexes I, syssegments S
WHERE O.type = 'U' AND O.id=I.id AND I.indid IN (0,1)
AND I.segment=S.segment AND O.type!='S'
ORDER BY 1,2

go
IF OBJECT_ID('dbo.sp_get_rowcounts') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.sp_get_rowcounts >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE
dbo.sp_get_rowcounts >>>'
go
EXEC sp_procxmode 'dbo. sp_get_rowcounts','u
nchained'
go

> how do you capture rowcounts? Do you use the builtin
> function rowcnt(), "select count(*)", or "select rowcnt
> from systabstats" ? <Barlow> wrote in message
> between the dump and load except sa.
>
>

species8472

2005-12-20, 8:25 pm

Barlow wrote:[color=darkred
]
> We are running a sp we created.
> IF OBJECT_ID('dbo.sp_get_rowcounts') IS NOT NULL
> BEGIN
> DROP PROCEDURE dbo.sp_get_rowcounts
> IF OBJECT_ID('dbo.sp_get_rowcounts') IS NOT NULL
> PRINT '<<< FAILED DROPPING PROCEDURE
> dbo.sp_get_rowcounts >>>'
> ELSE
> PRINT '<<< DROPPED PROCEDURE dbo.sp_get_rowcounts
>
>
> END
> go
> create proc sp_get_rowcounts
> as
> INSERT RTIwork..rowcounts
> SELECT db_name(),
> -- USER_NAME(uid),
> O. name,rowcnt(doampg),

> -- S.name,creation = O.crdate,
> case
> sysstat2 & 57344
> when 32768 then 'datarows' when 16384 then 'datapages'
> else 'allpages'
> end
> FROM sysobjects O, sysindexes I, syssegments S
> WHERE O.type = 'U' AND O.id=I.id AND I.indid IN (0,1)
> AND I.segment=S.segment AND O.type!='S'
> ORDER BY 1,2
>
> go
> IF OBJECT_ID('dbo.sp_get_rowcounts') IS NOT NULL
> PRINT '<<< CREATED PROCEDURE dbo.sp_get_rowcounts >>>'
> ELSE
> PRINT '<<< FAILED CREATING PROCEDURE
> dbo.sp_get_rowcounts >>>'
> go
> EXEC sp_procxmode 'dbo. sp_get_rowcounts','u
nchained'
> go
>
>
Usually this count will be correct. However, it is maintained in memory,
so if it is
not flushed to disk after the last change, it can get out of sync.

To ensure correct rowcounts you'll have to do a count(*) from the table.

joop

--
Senior Consultant Sybase Professional Services

Lunatic Asylum, n.:
The place where optimism most flourishes.

Sherlock, Kevin

2005-12-20, 8:25 pm

Using rowcnt() builtin has it's caveats. Take a look at the documentation for
this system function for more details:

http://infocenter.sybase.com/help/i...p.ase_12.5.1.bl
ocks/html/blocks/blocks206.htm

<Barlow> wrote in message news:43a87deb.1116.1681692777@sybase.com...[color=darkred]
> We are running a sp we created.
> IF OBJECT_ID('dbo.sp_get_rowcounts') IS NOT NULL
> BEGIN
> DROP PROCEDURE dbo.sp_get_rowcounts
> IF OBJECT_ID('dbo.sp_get_rowcounts') IS NOT NULL
> PRINT '<<< FAILED DROPPING PROCEDURE
> dbo.sp_get_rowcounts >>>'
> ELSE
> PRINT '<<< DROPPED PROCEDURE dbo.sp_get_rowcounts
> END
> go
> create proc sp_get_rowcounts
> as
> INSERT RTIwork..rowcounts
> SELECT db_name(),
> -- USER_NAME(uid),
> O. name,rowcnt(doampg),

> -- S.name,creation = O.crdate,
> case
> sysstat2 & 57344
> when 32768 then 'datarows' when 16384 then 'datapages'
> else 'allpages'
> end
> FROM sysobjects O, sysindexes I, syssegments S
> WHERE O.type = 'U' AND O.id=I.id AND I.indid IN (0,1)
> AND I.segment=S.segment AND O.type!='S'
> ORDER BY 1,2
>
> go
> IF OBJECT_ID('dbo.sp_get_rowcounts') IS NOT NULL
> PRINT '<<< CREATED PROCEDURE dbo.sp_get_rowcounts >>>'
> ELSE
> PRINT '<<< FAILED CREATING PROCEDURE
> dbo.sp_get_rowcounts >>>'
> go
> EXEC sp_procxmode 'dbo. sp_get_rowcounts','u
nchained'
> go
>

Sponsored Links