Help with corrupt mdf file

I really hope someone out there can help me with this one... My company
recently acquired another company that was using SQL Server 7. The machine
that the database was housed on began to have hard drive issues. We were
able to extract the mdf and ldf files before the hard drives crashed
completely. When attempting to load these files into a SQL Server 2k
database, we hit a wall. SQL Server 2k refuses to attach the mdf file,
saying that the file is not an mdf file. We also received a similar error
in Query Analyzer, using both sp_attach_db and sp_attach_single_file_db;
this error claimed that the file name might be incorrect.
After this failure, we created a new database, stopped the services, and
switched the mdf files with the new databases. This brought the database up
in suspect mode. We were then able to switch it to suspect/emergency mode.
From here, we attempted a dbcc checkdb, which errored out with an version
error. We also attempted to import the data from the new emergency db into
a newer clean db. This failed with a login error, even though we were
logged in under sa.
I realize that we will probably not be able to retrieve all of the records
from the corrupt file, but does anyone know a way to access any of the data?
Any ideas or suggestions are welcome.
P.S. all of the servers that we manage are backed up daily... just trying
to fix another company's error
Thanks,
JoeOne of the things you might try to do is to attach it to a SQL 7 server..
Then the upgrade will not have to occur...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Joe" <joe@.nospam.com> wrote in message
news:uFAJlKCwDHA.1888@.TK2MSFTNGP10.phx.gbl...
> I really hope someone out there can help me with this one... My company
> recently acquired another company that was using SQL Server 7. The
machine
> that the database was housed on began to have hard drive issues. We were
> able to extract the mdf and ldf files before the hard drives crashed
> completely. When attempting to load these files into a SQL Server 2k
> database, we hit a wall. SQL Server 2k refuses to attach the mdf file,
> saying that the file is not an mdf file. We also received a similar error
> in Query Analyzer, using both sp_attach_db and sp_attach_single_file_db;
> this error claimed that the file name might be incorrect.
> After this failure, we created a new database, stopped the services, and
> switched the mdf files with the new databases. This brought the database
up
> in suspect mode. We were then able to switch it to suspect/emergency
mode.
> From here, we attempted a dbcc checkdb, which errored out with an version
> error. We also attempted to import the data from the new emergency db
into
> a newer clean db. This failed with a login error, even though we were
> logged in under sa.
> I realize that we will probably not be able to retrieve all of the records
> from the corrupt file, but does anyone know a way to access any of the
data?
> Any ideas or suggestions are welcome.
>
> P.S. all of the servers that we manage are backed up daily... just
trying
> to fix another company's error
> Thanks,
> Joe
>|||Wayne,
I think your system clock or time zone is a bit off..., not sure if you have
noticed.
--
- Anith
( Please reply to newsgroups only )|||thanks... my region was wrong... sorry.
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Joe" <joe@.nospam.com> wrote in message
news:uFAJlKCwDHA.1888@.TK2MSFTNGP10.phx.gbl...
> I really hope someone out there can help me with this one... My company
> recently acquired another company that was using SQL Server 7. The
machine
> that the database was housed on began to have hard drive issues. We were
> able to extract the mdf and ldf files before the hard drives crashed
> completely. When attempting to load these files into a SQL Server 2k
> database, we hit a wall. SQL Server 2k refuses to attach the mdf file,
> saying that the file is not an mdf file. We also received a similar error
> in Query Analyzer, using both sp_attach_db and sp_attach_single_file_db;
> this error claimed that the file name might be incorrect.
> After this failure, we created a new database, stopped the services, and
> switched the mdf files with the new databases. This brought the database
up
> in suspect mode. We were then able to switch it to suspect/emergency
mode.
> From here, we attempted a dbcc checkdb, which errored out with an version
> error. We also attempted to import the data from the new emergency db
into
> a newer clean db. This failed with a login error, even though we were
> logged in under sa.
> I realize that we will probably not be able to retrieve all of the records
> from the corrupt file, but does anyone know a way to access any of the
data?
> Any ideas or suggestions are welcome.
>
> P.S. all of the servers that we manage are backed up daily... just
trying
> to fix another company's error
> Thanks,
> Joe
>|||I'm with Wayne. A secondary option can be to try
http://www.officerecovery.com/mssql/, not sure how much help such a tool can
be, though.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:OSKheOCwDHA.1680@.TK2MSFTNGP12.phx.gbl...
> One of the things you might try to do is to attach it to a SQL 7 server..
> Then the upgrade will not have to occur...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Joe" <joe@.nospam.com> wrote in message
> news:uFAJlKCwDHA.1888@.TK2MSFTNGP10.phx.gbl...
> > I really hope someone out there can help me with this one... My company
> > recently acquired another company that was using SQL Server 7. The
> machine
> > that the database was housed on began to have hard drive issues. We
were
> > able to extract the mdf and ldf files before the hard drives crashed
> > completely. When attempting to load these files into a SQL Server 2k
> > database, we hit a wall. SQL Server 2k refuses to attach the mdf file,
> > saying that the file is not an mdf file. We also received a similar
error
> > in Query Analyzer, using both sp_attach_db and sp_attach_single_file_db;
> > this error claimed that the file name might be incorrect.
> >
> > After this failure, we created a new database, stopped the services, and
> > switched the mdf files with the new databases. This brought the
database
> up
> > in suspect mode. We were then able to switch it to suspect/emergency
> mode.
> > From here, we attempted a dbcc checkdb, which errored out with an
version
> > error. We also attempted to import the data from the new emergency db
> into
> > a newer clean db. This failed with a login error, even though we were
> > logged in under sa.
> >
> > I realize that we will probably not be able to retrieve all of the
records
> > from the corrupt file, but does anyone know a way to access any of the
> data?
> > Any ideas or suggestions are welcome.
> >
> >
> > P.S. all of the servers that we manage are backed up daily... just
> trying
> > to fix another company's error
> >
> > Thanks,
> >
> > Joe
> >
> >
>sql

Help with calculating duration time

I have a table called Tickets which contains ticket information for a machine. Each machine can have more than one ticket number opened at the same time. The ticket number contains start date/time and end date/time of the ticket. Thereefore the table looks something like this:

Ticket_No (int)
Machine_No (int)
Description (char)
Start_Time (datetime)
End_Time (datetime)

I want to be able to calculate total duration time(in hours) that EACH MACHINE had a ticket open...but here is the tricky part. The total duration time that a machine had ticket open has to encompas any tickets that may fall in the same time period. For example:
If Machine A has a ticket open at 8:30 and the ticket is closed at 10:00. Meanwhile, Machine A had another separate ticket open at 9:30 which was closed at 10:30. In this case, the total duration time for this machine would be from 8:30 to 10:30 for a total of 2 hrs duration time.

Can anyone help me get started in tackling this problem or provide any examples?Blind code, but give it a try: create table #Blocks
(Machine_No int,
Start_Time datetime,
End_Time datetime)

insert into #Blocks
(Machine_No,
Start_Time,
End_Time)
select Machine_No,
Start_Time,
max(End_Time)
from Tickets
inner join --FirstTickets
(select Machine_No
min(Start_Time) Start_Time
from Tickets) FirstTickets
on Tickets.Machine_No = FirstTickets.Machine_No
and Tickets.Start_Time = FirstTickets.StartTime

while @.@.RowCount > 0
begin
while @.@.RowCount > 0 --I don't think this will be reset by previous check...
update #Blocks
set End_Time = max(End_Time)
from #Blocks
inner join Tickets
on #Blocks.Machine_No = Tickets.Machine_No
and Tickets.Start_Time between #Blocks.Start_Time and #Blocks.End_Time
and Tickets.End_Time > #Blocks.End_Time

insert into #Blocks
(Machine_No,
Start_Time,
End_Time)
select Machine_No,
Start_Time,
max(End_Time)
from Tickets
inner join --FirstTickets
(select Machine_No
min(Start_Time) Start_Time
from Tickets
where Start_Time >
(select max(End_Time)
from #Blocks
where #Blocks.Machine_No = Tickets.Machine_No) NextTickets
on Tickets.Machine_No = NextTickets.Machine_No
and Tickets.Start_Time = NextTickets.StartTime
end

select Machine_No,
sum(datediff(s, Start_Time, End_Time)/3600.00 as Hours
from #Blocks
group by Machine_No
If you can deal with discrete time segments (say, 1 minute increments) and place an upper limit on the date range, you may be able to do this with a simpler query using a table of integers.|||I really think I can use this (with minor modifications). How can I make this work so that it only compares the records within the same day and not all the records? Thank you.|||This formula will concatenate datetime to whole dates:

select dateadd(d, datediff(d, 0, [Yourdate]), 0) as WholeDate|||How can I modify the following solution so that it only captures the duration time for range of 7 am - 11 pm spanning multiple days? Right now, it works only for 24 hr period. Here is my code:

SELECT dbo.tbl_installed_ATMs.Term_ID, FirstTickets.Object_Key, FirstTickets.Start_Time, MAX(dbo.tbl_install_tickets.END_TIME) AS End_Time,
CONVERT(varchar(12), dbo.tbl_install_tickets.START_TIME, 110) AS Start_Date, CONVERT(varchar(12), dbo.tbl_install_tickets.END_TIME, 110)
AS End_Date, dbo.tbl_status_code_key.DESCRIPTION
INTO dbo.Blocks
FROM dbo.tbl_install_tickets INNER JOIN
(SELECT Object_Key, MIN(Start_Time) Start_Time
FROM tbl_install_tickets
GROUP BY Object_Key) FirstTickets ON dbo.tbl_install_tickets.OBJECT_KEY = FirstTickets.Object_Key AND
dbo.tbl_install_tickets.START_TIME = FirstTickets.Start_Time INNER JOIN
dbo.tbl_status_code_key ON dbo.tbl_install_tickets.STATUS_CODE_KEY = dbo.tbl_status_code_key.LINK INNER JOIN
dbo.tbl_installed_ATMs ON FirstTickets.Object_Key = dbo.tbl_installed_ATMs.Object_Key
GROUP BY FirstTickets.Object_Key, FirstTickets.Start_Time, CONVERT(varchar(12), dbo.tbl_install_tickets.START_TIME, 110), CONVERT(varchar(12),
dbo.tbl_install_tickets.END_TIME, 110), dbo.tbl_status_code_key.DESCRIPTION, dbo.tbl_installed_ATMs.Term_ID

while @.@.RowCount > 0
begin
while @.@.RowCount > 0 --I don't think this will be reset by previous check...
update Blocks
set End_Time = (Select max(Blocks.End_Time)
from Blocks
inner join tbl_install_tickets
On Blocks.Object_Key = tbl_install_Tickets.Object_Key
and tbl_install_Tickets.Start_Time between Blocks.Start_Time and Blocks.End_Time
and tbl_install_Tickets.End_Time > Blocks.End_Time
)
from Blocks
end

SELECT Term_ID, CONVERT(varchar(12), Start_Time, 110) AS Date, DESCRIPTION AS Ticket_type, SUM(DATEDIFF(s, Start_Time, End_Time) / 3600.00)
AS Duration
FROM dbo.Blocks
GROUP BY Term_ID, CONVERT(varchar(12), Start_Time, 110), DESCRIPTION
GO

Help with backups

Hi, I have a SQL server running on a remote network. On my local machine I
have the SQL client tools in which I use to connect to the SQL server. How
can I perform a backup locally. When I try, it looks at the remote drives
rather then my local drives when I try to backup to my local folder.
Thanks
David
You might try this:
backup database DB1 to disk = '\\mymachine\c:\mssql\backups\DB1_date.bck'
You could run that via SQL Analyzer or put it into a script or job.
hth.
"David" <dross@.si.rr.com> wrote in message
news:um2RfUZdEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Hi, I have a SQL server running on a remote network. On my local machine
I
> have the SQL client tools in which I use to connect to the SQL server.
How
> can I perform a backup locally. When I try, it looks at the remote drives
> rather then my local drives when I try to backup to my local folder.
> Thanks
> David
>
sql

Help with backups

Hi, I have a SQL server running on a remote network. On my local machine I
have the SQL client tools in which I use to connect to the SQL server. How
can I perform a backup locally. When I try, it looks at the remote drives
rather then my local drives when I try to backup to my local folder.
Thanks
DavidYou might try this:
backup database DB1 to disk = '\\mymachine\c:\mssql\backups\DB1_date.bck'
You could run that via SQL Analyzer or put it into a script or job.
hth.
"David" <dross@.si.rr.com> wrote in message
news:um2RfUZdEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Hi, I have a SQL server running on a remote network. On my local machine
I
> have the SQL client tools in which I use to connect to the SQL server.
How
> can I perform a backup locally. When I try, it looks at the remote drives
> rather then my local drives when I try to backup to my local folder.
> Thanks
> David
>

Help with backups

Hi, I have a SQL server running on a remote network. On my local machine I
have the SQL client tools in which I use to connect to the SQL server. How
can I perform a backup locally. When I try, it looks at the remote drives
rather then my local drives when I try to backup to my local folder.
Thanks
DavidYou might try this:
backup database DB1 to disk = '\\mymachine\c:\mssql\backups\DB1_date.bck'
You could run that via SQL Analyzer or put it into a script or job.
hth.
"David" <dross@.si.rr.com> wrote in message
news:um2RfUZdEHA.2520@.TK2MSFTNGP12.phx.gbl...
> Hi, I have a SQL server running on a remote network. On my local machine
I
> have the SQL client tools in which I use to connect to the SQL server.
How
> can I perform a backup locally. When I try, it looks at the remote drives
> rather then my local drives when I try to backup to my local folder.
> Thanks
> David
>

help with accessing reportserver with web browser

I am using sql express w/ advanced services on xp pro with IIS 5.1 installed. On the local machine named 'Tester' i am able to access reportserver using a web browser and the url: http://localhost/reportserver. But when i go to another machine on the same network and domain and try to access the reportserver with url: http://Tester.domain/reportserver or http://<ip address of Tester>/reportserver ie tells me page cannot be found and does not give me any errors. How do I set up network access to reportserver on the machine Tester? Help would be greaty appreciated.

Try and turn off Windows Firewall to see if that helps.

To troubleshoot, you should be able to telnet to port 80 from the other machine.

cheers,

Andrew

HELP recovering from .Net 1.1 SP1 install

Report server has been failing on my machine ever since I installed the .Net
framework 1.1 SP1 to correct another problem. The error reported was that the
encryption keys were not working. Based on previous posts to solve this
problem I performed the following staps:
1. I ran rskeymgmt -d to delete the encrypted data.
2. restarted IIS
3. ran rsconfig with the following options
rsconfig -c -s <myserver> -d ReportServer -a Windows
rsconfig -c -s <myserver> -d ReportServerTempDB -a Windows
rsconfig -c -s <server> -d <database> -a Sql -u <username> -p
<password>
The above commands completed successfully
4. I tried to run rsactivate -r -c RSReportServer.config, but it responded
that the web service is already in a valid state or its state cannot be
determined.
When I try to access the report manager, I receive the following error:
The version of the report server database is either in a format that is
not valid, or it cannot be read. The found version is 'T.0.6.51'. The
expected version is 'C.0.6.51'.
I do not know what to try at this point other than trying to reinstall
report server, but from other posts it appears that a reinstall doesn't
always fix the problem.run rsconfig only once using the ReportServer database name. The temp
database name is always derived from the reportserver db name and you do not
need to use RSConfig to tell RS about the tempdb name.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"jbmeeh" <jbmeeh@.discussions.microsoft.com> wrote in message
news:4C7D2D20-F6B5-4616-8476-265FCC42E045@.microsoft.com...
> Report server has been failing on my machine ever since I installed the
.Net
> framework 1.1 SP1 to correct another problem. The error reported was that
the
> encryption keys were not working. Based on previous posts to solve this
> problem I performed the following staps:
> 1. I ran rskeymgmt -d to delete the encrypted data.
> 2. restarted IIS
> 3. ran rsconfig with the following options
> rsconfig -c -s <myserver> -d ReportServer -a Windows
> rsconfig -c -s <myserver> -d ReportServerTempDB -a Windows
> rsconfig -c -s <server> -d <database> -a Sql -u <username> -p
> <password>
> The above commands completed successfully
> 4. I tried to run rsactivate -r -c RSReportServer.config, but it responded
> that the web service is already in a valid state or its state cannot be
> determined.
> When I try to access the report manager, I receive the following error:
> The version of the report server database is either in a format that
is
> not valid, or it cannot be read. The found version is 'T.0.6.51'. The
> expected version is 'C.0.6.51'.
> I do not know what to try at this point other than trying to reinstall
> report server, but from other posts it appears that a reinstall doesn't
> always fix the problem.
>|||What do I do at this point?
"Daniel Reib [MSFT]" wrote:
> run rsconfig only once using the ReportServer database name. The temp
> database name is always derived from the reportserver db name and you do not
> need to use RSConfig to tell RS about the tempdb name.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "jbmeeh" <jbmeeh@.discussions.microsoft.com> wrote in message
> news:4C7D2D20-F6B5-4616-8476-265FCC42E045@.microsoft.com...
> > Report server has been failing on my machine ever since I installed the
> ..Net
> > framework 1.1 SP1 to correct another problem. The error reported was that
> the
> > encryption keys were not working. Based on previous posts to solve this
> > problem I performed the following staps:
> >
> > 1. I ran rskeymgmt -d to delete the encrypted data.
> > 2. restarted IIS
> > 3. ran rsconfig with the following options
> >
> > rsconfig -c -s <myserver> -d ReportServer -a Windows
> > rsconfig -c -s <myserver> -d ReportServerTempDB -a Windows
> > rsconfig -c -s <server> -d <database> -a Sql -u <username> -p
> > <password>
> > The above commands completed successfully
> > 4. I tried to run rsactivate -r -c RSReportServer.config, but it responded
> > that the web service is already in a valid state or its state cannot be
> > determined.
> >
> > When I try to access the report manager, I receive the following error:
> >
> > The version of the report server database is either in a format that
> is
> > not valid, or it cannot be read. The found version is 'T.0.6.51'. The
> > expected version is 'C.0.6.51'.
> >
> > I do not know what to try at this point other than trying to reinstall
> > report server, but from other posts it appears that a reinstall doesn't
> > always fix the problem.
> >
>
>|||First decide if you are going to use windows auth or SQL auth. Then run
rsconfig passing in ReportServer for the -d option your choice for -a -u
and -p.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"jbmeeh" <jbmeeh@.discussions.microsoft.com> wrote in message
news:71D5A499-F838-4507-A64F-D6F6884B3118@.microsoft.com...
> What do I do at this point?
> "Daniel Reib [MSFT]" wrote:
> > run rsconfig only once using the ReportServer database name. The temp
> > database name is always derived from the reportserver db name and you do
not
> > need to use RSConfig to tell RS about the tempdb name.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "jbmeeh" <jbmeeh@.discussions.microsoft.com> wrote in message
> > news:4C7D2D20-F6B5-4616-8476-265FCC42E045@.microsoft.com...
> > > Report server has been failing on my machine ever since I installed
the
> > ..Net
> > > framework 1.1 SP1 to correct another problem. The error reported was
that
> > the
> > > encryption keys were not working. Based on previous posts to solve
this
> > > problem I performed the following staps:
> > >
> > > 1. I ran rskeymgmt -d to delete the encrypted data.
> > > 2. restarted IIS
> > > 3. ran rsconfig with the following options
> > >
> > > rsconfig -c -s <myserver> -d ReportServer -a Windows
> > > rsconfig -c -s <myserver> -d ReportServerTempDB -a Windows
> > > rsconfig -c -s <server> -d <database> -a Sql -u <username> -p
> > > <password>
> > > The above commands completed successfully
> > > 4. I tried to run rsactivate -r -c RSReportServer.config, but it
responded
> > > that the web service is already in a valid state or its state cannot
be
> > > determined.
> > >
> > > When I try to access the report manager, I receive the following
error:
> > >
> > > The version of the report server database is either in a format
that
> > is
> > > not valid, or it cannot be read. The found version is 'T.0.6.51'. The
> > > expected version is 'C.0.6.51'.
> > >
> > > I do not know what to try at this point other than trying to reinstall
> > > report server, but from other posts it appears that a reinstall
doesn't
> > > always fix the problem.
> > >
> >
> >
> >|||I gave up trying rebuild the encrypted data. I couldn't get around the
problem of the database version being incorrect. I reinstalled report server
and that cleared up the problem.
"Daniel Reib [MSFT]" wrote:
> First decide if you are going to use windows auth or SQL auth. Then run
> rsconfig passing in ReportServer for the -d option your choice for -a -u
> and -p.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "jbmeeh" <jbmeeh@.discussions.microsoft.com> wrote in message
> news:71D5A499-F838-4507-A64F-D6F6884B3118@.microsoft.com...
> > What do I do at this point?
> >
> > "Daniel Reib [MSFT]" wrote:
> >
> > > run rsconfig only once using the ReportServer database name. The temp
> > > database name is always derived from the reportserver db name and you do
> not
> > > need to use RSConfig to tell RS about the tempdb name.
> > >
> > > --
> > > -Daniel
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > >
> > > "jbmeeh" <jbmeeh@.discussions.microsoft.com> wrote in message
> > > news:4C7D2D20-F6B5-4616-8476-265FCC42E045@.microsoft.com...
> > > > Report server has been failing on my machine ever since I installed
> the
> > > ..Net
> > > > framework 1.1 SP1 to correct another problem. The error reported was
> that
> > > the
> > > > encryption keys were not working. Based on previous posts to solve
> this
> > > > problem I performed the following staps:
> > > >
> > > > 1. I ran rskeymgmt -d to delete the encrypted data.
> > > > 2. restarted IIS
> > > > 3. ran rsconfig with the following options
> > > >
> > > > rsconfig -c -s <myserver> -d ReportServer -a Windows
> > > > rsconfig -c -s <myserver> -d ReportServerTempDB -a Windows
> > > > rsconfig -c -s <server> -d <database> -a Sql -u <username> -p
> > > > <password>
> > > > The above commands completed successfully
> > > > 4. I tried to run rsactivate -r -c RSReportServer.config, but it
> responded
> > > > that the web service is already in a valid state or its state cannot
> be
> > > > determined.
> > > >
> > > > When I try to access the report manager, I receive the following
> error:
> > > >
> > > > The version of the report server database is either in a format
> that
> > > is
> > > > not valid, or it cannot be read. The found version is 'T.0.6.51'. The
> > > > expected version is 'C.0.6.51'.
> > > >
> > > > I do not know what to try at this point other than trying to reinstall
> > > > report server, but from other posts it appears that a reinstall
> doesn't
> > > > always fix the problem.
> > > >
> > >
> > >
> > >
>
>