Recovery of a corrupted Database due to power failure

Hi,
Iam looking for some tools or methods to recover a corrupted mdf file (SQL
-MSDE 2000)..please help me and I have tried the following :
1. used osql to open the master database and run the DBCC checkdb with
specified database name and given the following message :
Database 'RubyDB' cannot be opened. It has been marked SUSPECT by recovery.
See the SQL Server errorlog for more information.
... i didn't find any useful information in the error log and looked in the
msdn articles and tried to reset the status using sp_resetstatus and didn't
helped. also tried to used the update to change the suspect flag status.
didn't helped
2. also tried to detach the database using the sp_detach_db and tried to
re-attach the particular db by using :
sp_attach_db @.dbname = N'RubyDB', @.filename1 = N'C:\Program Files\Microsoft
SQL Server\MSSQL$RUBYMSDEINSTANCE\Data\RubyDB_Data.MDF', @.filename2 = N'C:\Program Files\Microsoft SQL
Server\MSSQL$RUBYMSDEINSTANCE\Data\RubyDB_Log.LDF' ;
An got a message :
Msg 9003, Level 20, State 6, Server ADDSV1WD67Z461\RUBYMSDEINSTANCE, Line 1
The LSN (742:208:1) passed to log scan in database 'RubyDB' is invalid.
Please help me if there are any tools to analyze the database problem (in
the .mdf file) and a way to get back the data (at least in partial)...
Thanks in advance
Hari"Got Backup?"
If you're against restoring from your last good backup for some reason, then
Google up on suspect databases. There are "unofficial" ways to reset
suspect databases. They're not 100% though, and you may end up having to
restore from backup anyway.
"Hari" <Hari@.discussions.microsoft.com> wrote in message
news:3AB6DE2D-FE0A-450C-A8CF-8E597C97ED5B@.microsoft.com...
> Hi,
> Iam looking for some tools or methods to recover a corrupted mdf file (SQL
> -MSDE 2000)..please help me and I have tried the following :
> 1. used osql to open the master database and run the DBCC checkdb with
> specified database name and given the following message :
> Database 'RubyDB' cannot be opened. It has been marked SUSPECT by
> recovery.
> See the SQL Server errorlog for more information.
> ... i didn't find any useful information in the error log and looked in
> the
> msdn articles and tried to reset the status using sp_resetstatus and
> didn't
> helped. also tried to used the update to change the suspect flag status.
> didn't helped
> 2. also tried to detach the database using the sp_detach_db and tried to
> re-attach the particular db by using :
> sp_attach_db @.dbname = N'RubyDB', @.filename1 = N'C:\Program
> Files\Microsoft
> SQL Server\MSSQL$RUBYMSDEINSTANCE\Data\RubyDB_Data.MDF', @.filename2 => N'C:\Program Files\Microsoft SQL
> Server\MSSQL$RUBYMSDEINSTANCE\Data\RubyDB_Log.LDF' ;
> An got a message :
>
> Msg 9003, Level 20, State 6, Server ADDSV1WD67Z461\RUBYMSDEINSTANCE, Line
> 1
> The LSN (742:208:1) passed to log scan in database 'RubyDB' is invalid.
>
> Please help me if there are any tools to analyze the database problem (in
> the .mdf file) and a way to get back the data (at least in partial)...
>
> Thanks in advance
> Hari
>|||Mike C# wrote:
> "Got Backup?"
>
What is this "backup" of which you speak? :-)
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Mike, could you give me more deatils on this unoffical way of resetting the
suspect database.
hari
"Mike C#" wrote:
> "Got Backup?"
> If you're against restoring from your last good backup for some reason, then
> Google up on suspect databases. There are "unofficial" ways to reset
> suspect databases. They're not 100% though, and you may end up having to
> restore from backup anyway.
> "Hari" <Hari@.discussions.microsoft.com> wrote in message
> news:3AB6DE2D-FE0A-450C-A8CF-8E597C97ED5B@.microsoft.com...
> > Hi,
> >
> > Iam looking for some tools or methods to recover a corrupted mdf file (SQL
> > -MSDE 2000)..please help me and I have tried the following :
> >
> > 1. used osql to open the master database and run the DBCC checkdb with
> > specified database name and given the following message :
> >
> > Database 'RubyDB' cannot be opened. It has been marked SUSPECT by
> > recovery.
> > See the SQL Server errorlog for more information.
> >
> > ... i didn't find any useful information in the error log and looked in
> > the
> > msdn articles and tried to reset the status using sp_resetstatus and
> > didn't
> > helped. also tried to used the update to change the suspect flag status.
> > didn't helped
> >
> > 2. also tried to detach the database using the sp_detach_db and tried to
> > re-attach the particular db by using :
> >
> > sp_attach_db @.dbname = N'RubyDB', @.filename1 = N'C:\Program
> > Files\Microsoft
> > SQL Server\MSSQL$RUBYMSDEINSTANCE\Data\RubyDB_Data.MDF', @.filename2 => > N'C:\Program Files\Microsoft SQL
> > Server\MSSQL$RUBYMSDEINSTANCE\Data\RubyDB_Log.LDF' ;
> >
> > An got a message :
> >
> >
> > Msg 9003, Level 20, State 6, Server ADDSV1WD67Z461\RUBYMSDEINSTANCE, Line
> > 1
> > The LSN (742:208:1) passed to log scan in database 'RubyDB' is invalid.
> >
> >
> > Please help me if there are any tools to analyze the database problem (in
> > the .mdf file) and a way to get back the data (at least in partial)...
> >
> >
> > Thanks in advance
> >
> > Hari
> >
> >
>
>|||I haven't had to do it in a long time, but basically you try to trick SQL
Server into thinking the database is no longer suspect. As I mentioned, my
good buddies Larry Page and Sergey Brin have a ton of information on it over
at their website (GOOGLE.COM).
If you do get it to work, then I would recommend immediately copying
everything to a NEW database and immediately starting to take daily BACKUPS
so you don't have to go through this again.
"Hari" <Hari@.discussions.microsoft.com> wrote in message
news:BB4CE1FD-CC3C-4DFA-A1D2-6BA056CBFA79@.microsoft.com...
> Mike, could you give me more deatils on this unoffical way of resetting
> the
> suspect database.
> hari
> "Mike C#" wrote:
>> "Got Backup?"
>> If you're against restoring from your last good backup for some reason,
>> then
>> Google up on suspect databases. There are "unofficial" ways to reset
>> suspect databases. They're not 100% though, and you may end up having to
>> restore from backup anyway.
>> "Hari" <Hari@.discussions.microsoft.com> wrote in message
>> news:3AB6DE2D-FE0A-450C-A8CF-8E597C97ED5B@.microsoft.com...
>> > Hi,
>> >
>> > Iam looking for some tools or methods to recover a corrupted mdf file
>> > (SQL
>> > -MSDE 2000)..please help me and I have tried the following :
>> >
>> > 1. used osql to open the master database and run the DBCC checkdb with
>> > specified database name and given the following message :
>> >
>> > Database 'RubyDB' cannot be opened. It has been marked SUSPECT by
>> > recovery.
>> > See the SQL Server errorlog for more information.
>> >
>> > ... i didn't find any useful information in the error log and looked in
>> > the
>> > msdn articles and tried to reset the status using sp_resetstatus and
>> > didn't
>> > helped. also tried to used the update to change the suspect flag
>> > status.
>> > didn't helped
>> >
>> > 2. also tried to detach the database using the sp_detach_db and tried
>> > to
>> > re-attach the particular db by using :
>> >
>> > sp_attach_db @.dbname = N'RubyDB', @.filename1 = N'C:\Program
>> > Files\Microsoft
>> > SQL Server\MSSQL$RUBYMSDEINSTANCE\Data\RubyDB_Data.MDF', @.filename2 =>> > N'C:\Program Files\Microsoft SQL
>> > Server\MSSQL$RUBYMSDEINSTANCE\Data\RubyDB_Log.LDF' ;
>> >
>> > An got a message :
>> >
>> >
>> > Msg 9003, Level 20, State 6, Server ADDSV1WD67Z461\RUBYMSDEINSTANCE,
>> > Line
>> > 1
>> > The LSN (742:208:1) passed to log scan in database 'RubyDB' is invalid.
>> >
>> >
>> > Please help me if there are any tools to analyze the database problem
>> > (in
>> > the .mdf file) and a way to get back the data (at least in partial)...
>> >
>> >
>> > Thanks in advance
>> >
>> > Hari
>> >
>> >
>>|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AF77C7.8000807@.realsqlguy.com...
> Mike C# wrote:
>> "Got Backup?"
> What is this "backup" of which you speak? :-)
It's them high-falutin' words I done heard the DBA's slangin' around the
uther day :)
On the plus side, nothing makes you start thinking about backups like losing
(or almost losing) a bunch of critical data because you didn't bother
backing it up in the first place :)

Recover System Stored Procedure

I mistakenly deleted the system stored procedure 'sp_columns' from my MSDE. Any idea about how to recover/reinstall it back without reinstalling the entire MSDE? Please help. Thanks.

you could restore your master database if you've a recent copy. Alternatively, the following script will recreate it. You'll need to either run it via a GUI tool, or else use osql from Dos.

use master
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

/* Procedure for 8.0 server */
CREATE PROCEDURE sp_columns (
@.table_name nvarchar(384),
@.table_owner nvarchar(384) = null,
@.table_qualifier sysname = null,
@.column_name nvarchar(384) = null,
@.ODBCVer int = 2)
AS
DECLARE @.full_table_name nvarchar(769)
DECLARE @.table_id int

if @.ODBCVer <> 3
select @.ODBCVer = 2
if @.column_name is null /* If column name not supplied, match all */
select @.column_name = '%'
if @.table_qualifier is not null
begin
if db_name() <> @.table_qualifier
begin /* If qualifier doesn't match current database */
raiserror (15250, -1,-1)
return
end
end
if @.table_name is null
begin /* If table name not supplied, match all */
select @.table_name = '%'
end
if @.table_owner is null
begin /* If unqualified table name */
SELECT @.full_table_name = quotename(@.table_name)
end
else
begin /* Qualified table name */
if @.table_owner = ''
begin /* If empty owner name */
SELECT @.full_table_name = quotename(@.table_owner)
end
else
begin
SELECT @.full_table_name = quotename(@.table_owner) +
'.' + quotename(@.table_name)
end
end

/* Get Object ID */
SELECT @.table_id = object_id(@.full_table_name)
if ((isnull(charindex('%', @.full_table_name),0) = 0) and
(isnull(charindex('[', @.table_name),0) = 0) and
(isnull(charindex('[', @.table_owner),0) = 0) and
(isnull(charindex('_', @.full_table_name),0) = 0) and
@.table_id <> 0)
begin
/* this block is for the case where there is no pattern
matching required for the table name */

SELECT
TABLE_QUALIFIER = convert(sysname,DB_NAME()),
TABLE_OWNER = convert(sysname,USER_NAME(o.uid)),
TABLE_NAME = convert(sysname,o.name),
COLUMN_NAME = convert(sysname,c.name),
d.DATA_TYPE,
convert (sysname,case
when t.xusertype > 255 then t.name
else d.TYPE_NAME collate database_default
end) TYPE_NAME,
convert(int,case
when d.DATA_TYPE in (6,7) then d.data_precision /* FLOAT/REAL */
else OdbcPrec(c.xtype,c.length,c.xprec)
end) "PRECISION",
convert(int,case
when type_name(d.ss_dtype) IN ('numeric','decimal') then /* decimal/numeric types */
OdbcPrec(c.xtype,c.length,c.xprec)+2
else
isnull(d.length, c.length)
end) LENGTH,
SCALE = convert(smallint, OdbcScale(c.xtype,c.xscale)),
d.RADIX,
NULLABLE = convert(smallint, ColumnProperty (c.id, c.name, 'AllowsNull')),
REMARKS = convert(varchar(254),null), /* Remarks are NULL */
COLUMN_DEF = text,
d.SQL_DATA_TYPE,
d.SQL_DATETIME_SUB,
CHAR_OCTET_LENGTH = isnull(d.length, c.length)+d.charbin,
ORDINAL_POSITION = convert(int,
(
select count(*)
from syscolumns sc
where sc.id = c.id
AND sc.number = c.number
AND sc.colid <= c.colid
)),
IS_NULLABLE = convert(varchar(254),
substring('NO YES',(ColumnProperty (c.id, c.name, 'AllowsNull')*3)+1,3)),
SS_DATA_TYPE = c.type
FROM
sysobjects o,
master.dbo.spt_datatype_info d,
systypes t,
syscolumns c
LEFT OUTER JOIN syscomments m on c.cdefault = m.id
AND m.colid = 1
WHERE
o.id = @.table_id
AND c.id = o.id
AND t.xtype = d.ss_dtype
AND c.length = isnull(d.fixlen, c.length)
AND (d.ODBCVer is null or d.ODBCVer = @.ODBCVer)
AND (o.type not in ('P', 'FN', 'TF', 'IF') OR (o.type in ('TF', 'IF') and c.number = 0))
AND isnull(d.AUTO_INCREMENT,0) = isnull(ColumnProperty (c.id, c.name, 'IsIdentity'),0)
AND c.xusertype = t.xusertype
AND c.name like @.column_name
ORDER BY 17
end
else
begin
/* this block is for the case where there IS pattern
matching done on the table name */

if @.table_owner is null /* If owner not supplied, match all */
select @.table_owner = '%'

SELECT
TABLE_QUALIFIER = convert(sysname,DB_NAME()),
TABLE_OWNER = convert(sysname,USER_NAME(o.uid)),
TABLE_NAME = convert(sysname,o.name),
COLUMN_NAME = convert(sysname,c.name),
d.DATA_TYPE,
convert (sysname,case
when t.xusertype > 255 then t.name
else d.TYPE_NAME collate database_default
end) TYPE_NAME,
convert(int,case
when d.DATA_TYPE in (6,7) then d.data_precision /* FLOAT/REAL */
else OdbcPrec(c.xtype,c.length,c.xprec)
end) "PRECISION",
convert(int,case
when type_name(d.ss_dtype) IN ('numeric','decimal') then /* decimal/numeric types */
OdbcPrec(c.xtype,c.length,c.xprec)+2
else
isnull(d.length, c.length)
end) LENGTH,
SCALE = convert(smallint, OdbcScale(c.xtype,c.xscale)),
d.RADIX,
NULLABLE = convert(smallint, ColumnProperty (c.id, c.name, 'AllowsNull')),
REMARKS = convert(varchar(254),null), /* Remarks are NULL */
COLUMN_DEF = text,
d.SQL_DATA_TYPE,
d.SQL_DATETIME_SUB,
CHAR_OCTET_LENGTH = isnull(d.length, c.length)+d.charbin,
ORDINAL_POSITION = convert(int,
(
select count(*)
from syscolumns sc
where sc.id = c.id
AND sc.number = c.number
AND sc.colid <= c.colid
)),
IS_NULLABLE = convert(varchar(254),
rtrim(substring('NO YES',(ColumnProperty (c.id, c.name, 'AllowsNull')*3)+1,3))),
SS_DATA_TYPE = c.type
FROM
sysobjects o,
master.dbo.spt_datatype_info d,
systypes t,
syscolumns c
LEFT OUTER JOIN syscomments m on c.cdefault = m.id
AND m.colid = 1
WHERE
o.name like @.table_name
AND user_name(o.uid) like @.table_owner
AND o.id = c.id
AND t.xtype = d.ss_dtype
AND c.length = isnull(d.fixlen, c.length)
AND (d.ODBCVer is null or d.ODBCVer = @.ODBCVer)
AND (o.type not in ('P', 'FN', 'TF', 'IF') OR (o.type in ('TF', 'IF') and c.number = 0))
AND isnull(d.AUTO_INCREMENT,0) = isnull(ColumnProperty (c.id, c.name, 'IsIdentity'),0)
AND c.xusertype = t.xusertype
AND c.name like @.column_name
ORDER BY 2, 3, 17
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

|||Cathal, thanks a lot!

Records getting deleted mysteriously..

I'm running a DB using MSDE (2000) that is interfaced by 2 different
ades running on PCs with Access 2000 Runtime. One of the ADEs is a
package accounting system that is very solid and stable, the other is
a custom application that I wrote (much less solid and stable). The
custom app only deals with a select few tables in the database, and
the table in question is not one of those.
With alarming regularity(daily), records are getting deleted out of a
particular table. I've set up a couple of dummy records in the table
and put a delete trigger on the table that creates record in a 'log'
table that tells me the user and the time that the records are
deleted.
The deletion (all records in the table) always occurs during business
hours (never over the weekend or at night) and the user responsible
varies among 3 or 4 different users. 2 of those users don't even have
rights to that table, so I'm really confused how those logins could
cause a delete on the table they don't have access to!??!
As far as I can tell, this is only happening to this particular table
( I hope!).
Is there a way that I can get more information on the process or
machine or anything else that is behind the deletion?Hi

Check out any stored procedures to see if the they will delete them as the
ownership chain may allow them to access the table. You may also want to
check for any FKs with cascading deletes.

Usually I would profile this to get an overall view of what activity is
occuring.

John

"C Kirby" <ckirby@.mindspring.com> wrote in message
news:rub5tv0fnkodkf4d4qjtnl2rj1ahbv6976@.4ax.com...
> I'm running a DB using MSDE (2000) that is interfaced by 2 different
> ades running on PCs with Access 2000 Runtime. One of the ADEs is a
> package accounting system that is very solid and stable, the other is
> a custom application that I wrote (much less solid and stable). The
> custom app only deals with a select few tables in the database, and
> the table in question is not one of those.
> With alarming regularity(daily), records are getting deleted out of a
> particular table. I've set up a couple of dummy records in the table
> and put a delete trigger on the table that creates record in a 'log'
> table that tells me the user and the time that the records are
> deleted.
> The deletion (all records in the table) always occurs during business
> hours (never over the weekend or at night) and the user responsible
> varies among 3 or 4 different users. 2 of those users don't even have
> rights to that table, so I'm really confused how those logins could
> cause a delete on the table they don't have access to!??!
> As far as I can tell, this is only happening to this particular table
> ( I hope!).
> Is there a way that I can get more information on the process or
> machine or anything else that is behind the deletion?|||C Kirby (ckirby@.mindspring.com) writes:
> The deletion (all records in the table) always occurs during business
> hours (never over the weekend or at night) and the user responsible
> varies among 3 or 4 different users. 2 of those users don't even have
> rights to that table, so I'm really confused how those logins could
> cause a delete on the table they don't have access to!??!
> As far as I can tell, this is only happening to this particular table
> ( I hope!).
> Is there a way that I can get more information on the process or
> machine or anything else that is behind the deletion?

In addition to John's suggestion, here are a few more tips of what you
could put in the log table:

o The value of @.@.nestlevel. If the value is 1, the trigger was fired from
an explicit DELETE statement. If the value is 2, the trigger may have
been fired from a direct DELETE statement in a stored procedure, or
from another trigger.
o INSERT #tbl EXEC('DBCC INPUTBUFFER (' + ltrim(str(@.@.spid)) +
') WITH TABLE_RESULTS')
This will give you the command the user/application submitted. Look
up DBCC INPUTBUFFER in Books Online for details on the result set.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp