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...
>
>
|||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...[vbcol=seagreen]
> Mike, could you give me more deatils on this unoffical way of resetting
> the
> suspect database.
> hari
> "Mike C#" wrote:
|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AF77C7.8000807@.realsqlguy.com...
> Mike C# wrote:
> 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

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\RubyD
B_Data.MDF', @.filename2 =
N'C:\Program Files\Microsoft SQL
Server\MSSQL$RUBYMSDEINSTANCE\Data\RubyD
B_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\RubyD
B_Data.MDF', @.filename2 =
> N'C:\Program Files\Microsoft SQL
> Server\MSSQL$RUBYMSDEINSTANCE\Data\RubyD
B_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, th
en
> 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...
>
>|||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...[vbcol=seagreen]
> Mike, could you give me more deatils on this unoffical way of resetting
> the
> suspect database.
> hari
> "Mike C#" wrote:
>|||"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45AF77C7.8000807@.realsqlguy.com...
> Mike C# wrote:
> 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 sql

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 :)

Records drilled through multiple times due to Partitions

Hi,

I have created a cube for a telephony database. In this database there are two fields that point to the same table named tblPhonebook. These fields are FromPhonebookID and ToPhonebookID, which respectively contain the Person in the phonebook who placed the call and the Person in the phonebook who received the call. (the latter could both be an internal person and an external person).

I created one dimension. The Phonebook dimension which points to the table tblPhonebook.

When I select a person in this dimension I want to see all the calls this person placed and also all the calls this person received. So I created two "views" in SQL Server.

view1: SELECT ToID as UseID, * FROM tblData

view2: SELECT FromID as UseID, * FROM tblData

With the Cube I created two partitions. The first partition takes view1 as data table, the second partition takes view2 as data table. My Phonebook dimension is related to the UseID in these views.

So far so good. When I select a person in the Phonebook dimension, I get a neat result of his ingoing and outgoing phonecalls. However, when I select "All Phonebook", so not a specific user in the phonebook list, and instead select a date for instance, there occurs a problem. Because no Phonebook entry is specified there are always two entries per phonecall. One from view1 and one from view2. So my calls are counted double.

For counting the calls this is also not a problem, because I can perform a Distinct Count on the tblData.ID. I also measure the Average of the Conversation Time. This is also not a problem, because every value is counted double, not one excluded, so the average stays the same (isn't it? conversations lasting 1, 8 and 12 seconds average on 7 seconds. If those are counted twice then it would be 1,1,8,8,12,12 which also average on 7).

But when I select a drillthrough (with "All Phonebook" selected) the problem arrises. All entries are showed double in my drillthrough. First all the From Data ordered by date, then all the To Data ordered by date.

Ofcourse I don't want that. I want them only to be listed once. How do I filter this drillthrough that when no phonebook entry is selected, they still only appear once in my list?

(Sorry for the long introduction to a one line question ;))

Greets,

Edward

For one, Analysis Server in this case delivers exactly to the promise : in the result of the drillrhough it returns you all the data you selected to perform the drillthrough into.

Now about your situation. To solve your problem, you might decide to model your cube a bit different. You can create another dimension with only a single non-aggregatable attribute and 2 values "From" and "To". Then your user will choose to drill through into which part of the data.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Hmmm... so no filter possible? that was what I was afraid for ;)

Thanks for looking into my problem....

Greets,

Edward