Recovery of database

By mistake I deleted the my application database, and I dont have Back up
also...
is there any way to recover it...
Please help me.. its very urgent
Regards,
Prashant C
Oops, no backups!!!
Then its gone...
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Prashant C" wrote:

> By mistake I deleted the my application database, and I dont have Back up
> also...
> is there any way to recover it...
>
> Please help me.. its very urgent
> --
> Regards,
> Prashant C
|||Unless some Widows utility can recovery the deleted database files. If so, you could try
sp_attach_db for the files, but that is not guaranteed.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:050D39A3-1771-46C4-A3A2-79E016B1E150@.microsoft.com...[vbcol=seagreen]
> Oops, no backups!!!
> Then its gone...
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread and time
> asking back if its 2000 or 2005]
>
> "Prashant C" wrote:
sql

Recovery of database

By mistake I deleted the my application database, and I dont have Back up
also...
is there any way to recover it...
Please help me.. its very urgent
--
Regards,
Prashant COops, no backups!!!
Then its gone...
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and
time
asking back if its 2000 or 2005]
"Prashant C" wrote:

> By mistake I deleted the my application database, and I dont have Back up
> also...
> is there any way to recover it...
>
> Please help me.. its very urgent
> --
> Regards,
> Prashant C|||Unless some Widows utility can recovery the deleted database files. If so, y
ou could try
sp_attach_db for the files, but that is not guaranteed.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:050D39A3-1771-46C4-A3A2-79E016B1E150@.microsoft.com...[vbcol=seagreen]
> Oops, no backups!!!
> Then its gone...
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread an
d time
> asking back if its 2000 or 2005]
>
> "Prashant C" wrote:
>

Recovery of database

By mistake I deleted the my application database, and I dont have Back up
also...
is there any way to recover it...
Please help me.. its very urgent
--
Regards,
Prashant COops, no backups!!!
Then its gone...
--
Thanks,
Sree
[Please specify the version of Sql Server as we can save one thread and time
asking back if its 2000 or 2005]
"Prashant C" wrote:
> By mistake I deleted the my application database, and I dont have Back up
> also...
> is there any way to recover it...
>
> Please help me.. its very urgent
> --
> Regards,
> Prashant C|||Unless some Widows utility can recovery the deleted database files. If so, you could try
sp_attach_db for the files, but that is not guaranteed.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:050D39A3-1771-46C4-A3A2-79E016B1E150@.microsoft.com...
> Oops, no backups!!!
> Then its gone...
> --
> Thanks,
> Sree
> [Please specify the version of Sql Server as we can save one thread and time
> asking back if its 2000 or 2005]
>
> "Prashant C" wrote:
>> By mistake I deleted the my application database, and I dont have Back up
>> also...
>> is there any way to recover it...
>>
>> Please help me.. its very urgent
>> --
>> Regards,
>> Prashant C

Recovering the deleted Table

Hi,
I have deleted the table from sql server2000 database by mistake. Is there any way to recover it?It's very urgent.
Thanks in advance.Q1 I have deleted the table from sql server2000 database by mistake. Is there any way to recover it?It's very urgent.


A1 Yes.

1 Restore the most recent DB backup dump to a differently named DB. For example: TestDB

2 Use a script and an insert statement, or DTS, or a select into statement, etc., to 'copy' (recreate & populate) the table back to the production DB from the TestDB. Depending on the technique used you may need to reapply permissions, constraints, etc., etc..|||Hi,
My problem is I don't have the back up of it. Is there any other way say by using the log file or any other?
Thanx|||Q1 I don't have the back up of it. Is there any other way say by using the log file or any other?

A1 If ddl scripts for the table(s) exist somewhere, the dropped tables may be recreated by running them.

As far as using the log for recovery, the answer is generally no. However, (provided certain conditions are met), a product like log explorer may sometimes be of help in addressing such kinds of situations. {Such products and methods are not a substitute for backup dumps.} See: http://www.lumigent.com/products/le_sql/le_sql.htm|||Data saved to LDF (LOG) files depend on recovery schema used.
MDF stores current (commited) state of database.
In full recovery schema, LDF stores all ops after last backup.
Then LDF can be used to locate changes and MDF can contain
deleted data.

That was theory...
Does anybody know somebody able to display info stored in SQL database binaries ...|||Originally posted by ispaleny
Does anybody know somebody able to display info stored in SQL database binaries ...
So DBA does.sql

Recovering from transaction log deletion in 6.5

I was trying to relocate my transaction log to a bigger drive using
sp_movedevice but I made a mistake in the syntax of the second parameter
and put only the path, not the path and the file name.
Now my database is marked as "suspect" and I get an error message in my log
upon database start up saying that the log file cannot be open.

Is there a way to have MS SQL 6.5 "forget" all the logs of this database,
create new ones and restart the database? The logs contained nothing
important, I had truncated them an hour or so before I made my mistake. I
just want to make sure the data are still usable.
When I look at the devices with sp_helpdevice, I can see a log that exist
and is hopefully in pristine condition and the one that doesn't exist
anymore.
I looked in the archives of various newsgroups but couldn't find something
that correspond closely to my situation. I saw something similar but with
MS SQL 7.0
(http://groups.google.com/groups?hl=...om %26rnum%3D4)
using sp_attach_db/sp_detach_db. What would be the equivalent with version
6.5?

Thanks!

Charles

--
Charles-E. Nadeau Ph.D
http://radio.weblogs.com/0111823/[posted and mailed]

Charles Nadeau (charlesnadeau@.hotmail.com) writes:
> I was trying to relocate my transaction log to a bigger drive using
> sp_movedevice but I made a mistake in the syntax of the second parameter
> and put only the path, not the path and the file name. Now my database
> is marked as "suspect" and I get an error message in my log upon
> database start up saying that the log file cannot be open.
> Is there a way to have MS SQL 6.5 "forget" all the logs of this database,
> create new ones and restart the database? The logs contained nothing
> important, I had truncated them an hour or so before I made my mistake. I
> just want to make sure the data are still usable.
> When I look at the devices with sp_helpdevice, I can see a log that exist
> and is hopefully in pristine condition and the one that doesn't exist
> anymore.

Indeed, you should be able to get to the data. I know how to deal with
the situation in SQL2000, but I don't know if those commands work in
6.5. (It's not the same as in the link you posted; sp_attach_db is not
in 6.5.) As the hour is late, I am not going to test, but I'll ask
around with my contacts at Microsoft, to see if they know.

If the mail address is not one you monitor, please send me a mail, since
the answer I get from MS may not be suitable for a public newsgroup. (The
method for SQL2000 is very dangerous if you don't understand what you
are doing.)

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You can set to emergency mode
http://support.microsoft.com/defaul...kb;en-us;165918

Which should allow you to access the data.

Didn't you take a backup first?

Nigel Rivett
www.nigelrivett.net

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Erland Sommarskog wrote:

> [posted and mailed]
> Charles Nadeau (charlesnadeau@.hotmail.com) writes:
>> I was trying to relocate my transaction log to a bigger drive using
>> sp_movedevice but I made a mistake in the syntax of the second parameter
>> and put only the path, not the path and the file name. Now my database
>> is marked as "suspect" and I get an error message in my log upon
>> database start up saying that the log file cannot be open.
>>
>> Is there a way to have MS SQL 6.5 "forget" all the logs of this database,
>> create new ones and restart the database? The logs contained nothing
>> important, I had truncated them an hour or so before I made my mistake. I
>> just want to make sure the data are still usable.
>> When I look at the devices with sp_helpdevice, I can see a log that exist
>> and is hopefully in pristine condition and the one that doesn't exist
>> anymore.
> Indeed, you should be able to get to the data. I know how to deal with

Just a few more details:
My log files are on the f: drive:

F:\mssql\data>dir
*Volume in drive F has no label.
*Volume Serial Number is 8018-882A

*Directory of F:\mssql\data

18/05/04* 01:40p******* <DIR>********* .
18/05/04* 01:40p******* <DIR>********* ..
24/08/01* 02:14p*************** 26,522 defn_ticket.sql.sql
18/05/04* 01:40p******* <DIR>********* dump
22/09/00* 01:45p**************** 1,384 dump.bat
18/05/04* 01:39p********** 268,435,456 master.mir
10/04/03* 08:12a*********** 42,844,160 Support-Processed.ps
18/05/04* 11:47a********** 268,435,456 ticket.mir
18/05/04* 12:16p******** 1,073,741,824 Ticketlog2.DAT
18/05/04* 12:16p******** 1,073,741,824 Ticket_log.dat
18/05/04* 01:39p************ 8,388,608 Winnie.DAT
18/05/04* 01:39p*********** 25,165,824 Winnie_log.DAT
************* 12 File(s)* 2,760,781,058 bytes
************************* 4,938,989,568 bytes free

The valid log file is Ticketlog2.DAT. Ticket_log.dat is simply a copy of
Ticketlog2.DAT I renamed and put there to try to force SQL Server to read
it.

And here is the output of two commands listing the state of my devices:

select * from sysdevices
go
sp_helpdevice
go

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 16995 ms.
SQL Server Parse and Compile Time:
** cpu time = 0 ms.
low******** high******* status cntrltype name**************************
phyname******************************************* ************************************************** ***************************
mirrorname**************************************** ************************************************** ***************************
stripeset*********************

---- ---- -- --- ----------
--------------------------------------
--------------------------------------
----------

0********** 19********* 16**** 3******** diskettedumpa*****************
a:sqltable.dat************************************ ************************************************** ***************************
(null)******************************************** ************************************************** ***************************
(null)************************

0********** 19********* 16**** 4******** diskettedumpb*****************
b:sqltable.dat************************************ ************************************************** ***************************
(null)******************************************** ************************************************** ***************************
(null)************************

0********** 131071***** 739*** 0******** master************************ C
\MSSQL\DATA\MASTER.DAT**************************** ************************************************** *************************
f:\mssql\data\master.mir************************** ************************************************** ***************************
(null)************************

2130706432* 2130709503* 2***** 0******** MSDBData********************** C
\MSSQL\DATA\MSDB.DAT****************************** ************************************************** *************************
(null)******************************************** ************************************************** ***************************
(null)************************

2113929216* 2113930239* 2***** 0******** MSDBLog*********************** C
\MSSQL\DATA\MSDBLOG.DAT*************************** ************************************************** *************************
(null)******************************************** ************************************************** ***************************
(null)************************

16777216*** 16908287*** 2***** 0******** ticket************************ f
\mssql\data\ticket.mir**************************** ************************************************** *************************
(null)******************************************** ************************************************** ***************************
(null)************************

33554432*** 33832959*** 2***** 0******** Ticket_log******************** f
\mssql\data\ticket_log.dat************************ ************************************************** *************************
(null)******************************************** ************************************************** ***************************
(null)************************

0********** 0********** 16**** 2******** TicketBackUp****************** F
\mssql\BackUp\TicketBackUp.DAT******************** ************************************************** *************************
(null)******************************************** ************************************************** ***************************
(null)************************

83886080*** 84410367*** 2***** 0******** Ticketlog2******************** F
\mssql\data\Ticketlog2.DAT************************ ************************************************** *************************
(null)******************************************** ************************************************** ***************************
(null)************************

50331648*** 50335743*** 2***** 0******** Winnie************************ F
\MSSQL\DATA\Winnie.DAT**************************** ************************************************** *************************
(null)******************************************** ************************************************** ***************************
(null)************************

0********** 0********** 16**** 2******** Winnie_backup***************** f
\MSSQL\BACKUP\Winnie_backup.DAT******************* ************************************************** *************************
(null)******************************************** ************************************************** ***************************
(null)************************

67108864*** 67121151*** 2***** 0******** Winnie_log******************** F
\MSSQL\DATA\Winnie_log.DAT************************ ************************************************** *************************
(null)******************************************** ************************************************** ***************************
(null)************************

(12 row(s) affected)

Table: sysdevices* scan count 1,* logical reads: 1,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 0 ms.
SQL Server Parse and Compile Time:
** cpu time = 0 ms.
SQL Server Parse and Compile Time:
** cpu time = 70 ms.
Table: sysdevices* scan count 1,* logical reads: 1,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 261 ms.

SQL Server Execution Times:
** cpu time = 10 ms.* elapsed time = 50 ms.

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 0 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 12,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 1,* logical reads: 1,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 10 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 16,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 8,* logical reads: 8,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 1,* logical reads: 1,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 10 ms.* elapsed time = 40 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 4,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 2,* logical reads: 2,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 1,* logical reads: 1,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 10 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 4,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 2,* logical reads: 2,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 1,* logical reads: 1,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 20 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 0,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 0,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 1,* logical reads: 1,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 20 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 0,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 0,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 1,* logical reads: 1,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 10 ms.* elapsed time = 20 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 4,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 2,* logical reads: 2,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 1,* logical reads: 1,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 110 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 0,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 0,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 1,* logical reads: 1,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 521 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 0,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 0,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 1,* logical reads: 1,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 20 ms.
Table: sysdevices* scan count 1,* logical reads: 1,* physical reads: 0,*
read ahead reads: 0
Table: spt_values* scan count 1,* logical reads: 2,* physical reads: 1,*
read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 1,* logical reads: 1,*
physical reads: 0,* read ahead reads: 0

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 10 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 2,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 1,* logical reads: 1,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 12,* logical reads: 12,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 10 ms.* elapsed time = 20 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 0,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 1,* logical reads: 1,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 12,* logical reads: 12,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 10 ms.* elapsed time = 20 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 2,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 1,* logical reads: 1,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 12,* logical reads: 12,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 10 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 2,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 1,* logical reads: 1,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 12,* logical reads: 12,* physical reads: 0,*
read ahead reads: 0
Table: spt_values* scan count 12,* logical reads: 24,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 10 ms.* elapsed time = 20 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 0,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 1,* logical reads: 1,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 12,* logical reads: 12,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 10 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 2,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 1,* logical reads: 1,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 12,* logical reads: 12,* physical reads: 0,*
read ahead reads: 0
Table: spt_values* scan count 12,* logical reads: 24,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 20 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 8,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 1,* logical reads: 1,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 12,* logical reads: 12,* physical reads: 0,*
read ahead reads: 0
Table: spt_values* scan count 12,* logical reads: 24,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 10 ms.* elapsed time = 20 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 2,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 1,* logical reads: 1,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 12,* logical reads: 12,* physical reads: 0,*
read ahead reads: 0
Table: spt_values* scan count 12,* logical reads: 24,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 10 ms.* elapsed time = 20 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 16,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 1,* logical reads: 1,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 12,* logical reads: 12,* physical reads: 0,*
read ahead reads: 0
Table: spt_values* scan count 12,* logical reads: 24,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 20 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 16,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 1,* logical reads: 1,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 12,* logical reads: 12,* physical reads: 0,*
read ahead reads: 0
Table: spt_values* scan count 1,* logical reads: 2,* physical reads: 1,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 10 ms.* elapsed time = 70 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 0,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 1,* logical reads: 1,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 12,* logical reads: 12,* physical reads: 0,*
read ahead reads: 0
Table: spt_values* scan count 12,* logical reads: 24,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 10 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 0,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 1,* logical reads: 1,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 12,* logical reads: 12,* physical reads: 0,*
read ahead reads: 0
Table: spt_values* scan count 12,* logical reads: 24,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 20 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 0,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 1,* logical reads: 1,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 12,* logical reads: 12,* physical reads: 0,*
read ahead reads: 0
Table: spt_values* scan count 12,* logical reads: 24,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 20 ms.
Table: #spdevtab___________0100000002* scan count 0,* logical reads: 0,*
physical reads: 0,* read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 1,* logical reads: 1,*
physical reads: 0,* read ahead reads: 0
Table: sysdevices* scan count 12,* logical reads: 12,* physical reads: 0,*
read ahead reads: 0
Table: spt_values* scan count 12,* logical reads: 24,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 10 ms.

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 0 ms.
device_name******************* physical_name*********************************
description*************************************** ************************************************** ************************************************** ************************************************** ************************************************** *****
status cntrltype device_number low******** high*******

----------
--------------
----------------------------------------------------------------------------
-- --- ---- ---- ----

TicketBackUp****************** F:\mssql\BackUp\TicketBackUp.DAT**************
disk, dump device******************************************** ************************************************** ************************************************** ************************************************** ********************************************
16**** 2******** 0************ 0********** 0**********

Winnie_backup***************** f:\MSSQL\BACKUP\Winnie_backup.DAT*************
disk, dump device******************************************** ************************************************** ************************************************** ************************************************** ********************************************
16**** 2******** 0************ 0********** 0**********

diskettedumpa***************** a:sqltable.dat********************************
diskette, 1.2 MB, dump device******************************************** ************************************************** ************************************************** ************************************************** ********************************
16**** 3******** 0************ 0********** 19*********

diskettedumpb***************** b:sqltable.dat********************************
diskette, 1.2 MB, dump device******************************************** ************************************************** ************************************************** ************************************************** ********************************
16**** 4******** 0************ 0********** 19*********

MSDBData********************** C:\MSSQL\DATA\MSDB.DAT************************
special, physical disk, 6 MB************************************************ ************************************************** ************************************************** ************************************************** *****************************
2***** 0******** 127********** 2130706432* 2130709503*

MSDBLog*********************** C:\MSSQL\DATA\MSDBLOG.DAT*********************
special, physical disk, 2 MB************************************************ ************************************************** ************************************************** ************************************************** *****************************
2***** 0******** 126********** 2113929216* 2113930239*

ticket************************ f:\mssql\data\ticket.mir**********************
special, physical disk, 256 MB************************************************ ************************************************** ************************************************** ************************************************** ***************************
2***** 0******** 1************ 16777216*** 16908287***

Ticket_log******************** f:\mssql\data\ticket_log.dat******************
special, physical disk, 544 MB************************************************ ************************************************** ************************************************** ************************************************** ***************************
2***** 0******** 2************ 33554432*** 33832959***

Ticketlog2******************** F:\mssql\data\Ticketlog2.DAT******************
special, physical disk, 1024 MB************************************************ ************************************************** ************************************************** ************************************************** **************************
2***** 0******** 5************ 83886080*** 84410367***

Winnie************************ F:\MSSQL\DATA\Winnie.DAT**********************
special, physical disk, 8 MB************************************************ ************************************************** ************************************************** ************************************************** *****************************
2***** 0******** 3************ 50331648*** 50335743***

Winnie_log******************** F:\MSSQL\DATA\Winnie_log.DAT******************
special, physical disk, 24 MB************************************************ ************************************************** ************************************************** ************************************************** ****************************
2***** 0******** 4************ 67108864*** 67121151***

master************************ C:\MSSQL\DATA\MASTER.DAT**********************
special, MIRROR ENABLED, mirrored on 'f:\mssql\data\master.mir', serial
writes, reads mirrored, default disk, physical disk, 256 MB************************************************ ************************************************** **************************
739*** 0******** 0************ 0********** 131071*****

(1 row(s) affected)

Table: sysdevices* scan count 12,* logical reads: 12,* physical reads: 0,*
read ahead reads: 0
Table: #spdevtab___________0100000002* scan count 1,* logical reads: 1,*
physical reads: 0,* read ahead reads: 0
Table: spt_values* scan count 1,* logical reads: 2,* physical reads: 0,*
read ahead reads: 0

SQL Server Execution Times:
** cpu time = 10 ms.* elapsed time = 10 ms.

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 0 ms.

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 20 ms.
SQL Server Parse and Compile Time:
** cpu time = 0 ms.

SQL Server Execution Times:
** cpu time = 0 ms.* elapsed time = 0 ms.

I hope this will shed more lights on my problem. Thanks again for your help!

Charles

> the situation in SQL2000, but I don't know if those commands work in
> 6.5. (It's not the same as in the link you posted; sp_attach_db is not
> in 6.5.) As the hour is late, I am not going to test, but I'll ask
> around with my contacts at Microsoft, to see if they know.
> If the mail address is not one you monitor, please send me a mail, since
> the answer I get from MS may not be suitable for a public newsgroup. (The
> method for SQL2000 is very dangerous if you don't understand what you
> are doing.)

--
Charles-E. Nadeau Ph.D
http://radio.weblogs.com/0111823/|||Nigel Rivett wrote:

> You can set to emergency mode
> http://support.microsoft.com/defaul...kb;en-us;165918

Using this I can backup my data and truncate the log but I don't think it
can take care of the bad information inside sysdevices. I'll take a deeper
look at it later on.

> Which should allow you to access the data.
> Didn't you take a backup first?

I backup the database daily by "dumping" them using bcp (i.e. bcp
Ticket.dbo.Item2Ticket out f:\mssql\data\dump\Item2Ticket.tbl /n /U
username /P password /S WINHKONG ).
This is my last backup:

K:\mssql\data\dump>dir
*Volume in drive K has no label.
*Volume Serial Number is 8018-882A

*Directory of K:\mssql\data\dump

18/05/04* 01:40p******* <DIR>********* .
18/05/04* 01:40p******* <DIR>********* ..
15/10/01* 04:10p*************** 29,022 all db scripts.sql.sql
17/05/04* 04:15p****************** 184 Item2Ticket.tbl
17/05/04* 04:15p*************** 15,895 LastInteraction.tbl
17/05/04* 04:15p*************** 30,647 Mission.tbl
17/05/04* 04:15p****************** 164 RSM.tbl
17/05/04* 04:15p******************* 28 Status.tbl
17/05/04* 04:15p**************** 2,059 TaskItems.tbl
17/05/04* 04:15p******************* 69 Tasks.tbl
17/05/04* 04:15p******************* 24 TaskType.tbl
17/05/04* 04:20p*********** 43,394,880 Ticket.tbl
17/05/04* 04:20p****************** 286 Topics.tbl
17/05/04* 04:20p******************* 60 Types.tbl
17/05/04* 04:20p************** 316,258 Words.tbl
17/05/04* 04:24p*********** 19,785,780 WordsInTicket.tbl
************* 16 File(s)**** 63,575,356 bytes
************************* 4,938,989,568 bytes free

> Nigel Rivett
> www.nigelrivett.net

Thanks for your help, much appreciated!

Charles

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

--
Charles-E. Nadeau Ph.D
http://radio.weblogs.com/0111823/|||Have you tried setting to emergency mode?
That should allow you to bcp all the data out and copy to another
database.

Nigel Rivett
www.nigelrivett.net

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Charles Nadeau (charlesnadeau@.hotmail.com) writes:
> Using this I can backup my data and truncate the log but I don't think it
> can take care of the bad information inside sysdevices. I'll take a deeper
> look at it later on.

Once you have gotten the data out, I suggest that you drop the database
and create a new one. You should not continue with the database once it
has been marked suspect.

I assume that you have your scripts on version control, but if not there are
scripting facitilies in Enterprise Manager.

> I backup the database daily by "dumping" them using bcp (i.e. bcp
> Ticket.dbo.Item2Ticket out f:\mssql\data\dump\Item2Ticket.tbl /n /U
> username /P password /S WINHKONG ).
> This is my last backup:

While that saves your data, you don't save metadata and stored procedures
this way. What's wrong with the regular DUMP command.

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

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

> Charles Nadeau (charlesnadeau@.hotmail.com) writes:
>> Using this I can backup my data and truncate the log but I don't think it
>> can take care of the bad information inside sysdevices. I'll take a
>> deeper look at it later on.
> Once you have gotten the data out, I suggest that you drop the database

This is what I did. Everything was smooth:
I put the database is emergency mode, bcp all the data out of it.
I cheked where my last back-up and the database scripts were.
I droped the database and its devices, then recreated the devices.
I created the database FOR LOAD.
I restored from my latest back-up (I didn't loose any data!).
I set the databse for use not only by dbo.
At this point I panicked a bit because the database was flagged as loading
even after I had put the data back into it. I restarted the server and I
was back in business. I was expecting the whole thing to be longer and more
painfull.
Thanks a lot for your help!

Charles

> and create a new one. You should not continue with the database once it
> has been marked suspect.
> I assume that you have your scripts on version control, but if not there
> are scripting facitilies in Enterprise Manager.
>> I backup the database daily by "dumping" them using bcp (i.e. bcp
>> Ticket.dbo.Item2Ticket out f:\mssql\data\dump\Item2Ticket.tbl /n /U
>> username /P password /S WINHKONG ).
>> This is my last backup:
> While that saves your data, you don't save metadata and stored procedures
> this way. What's wrong with the regular DUMP command.

--
Charles-E. Nadeau Ph.D
http://radio.weblogs.com/0111823/|||Charles Nadeau (charlesnadeau@.hotmail.com) writes:
> This is what I did. Everything was smooth:
> I put the database is emergency mode, bcp all the data out of it.
> I cheked where my last back-up and the database scripts were.
> I droped the database and its devices, then recreated the devices.
> I created the database FOR LOAD.
> I restored from my latest back-up (I didn't loose any data!).
> I set the databse for use not only by dbo.
> At this point I panicked a bit because the database was flagged as
> loading even after I had put the data back into it. I restarted the
> server and I was back in business. I was expecting the whole thing to be
> longer and more painfull.

Glad to hear that it worked out fine!

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

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

Recovering database from Transactional Log File(.ldf file)

Hi

I am in a trouble without doing any mistake. I restarted while in parallely my sql server was restarting i.e I opened a management studio.

When it booted up next time. I see that my database is gone. Though it is showed up in the server but has no node below it.

I then checked the mdf files and ldf files and found mdf file to be of 0 KB and ldf file is of 1.73 GB. My database was in full recovery mode but i did not take any database backup.

Here ais the entry from my SQL server log:

The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0000000000000000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Hello.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

I wonder if now there is a case to recover my database..Can I restore/recover the database from .ldf file as it was in full recovery model?Any help i shighly appreciated.It is not possible to recover the mdf file with ldf alone. The transaction log files holds the log information that is used to recover the database. MDF is the data file which contains the startup information for the database and points to the other files in the database. User data and objects will be stored in this file.|||

The LDF file would be the log file of the database. There is no way from SQL Server to recover a database from the LDF file alone. However you could try other tools such as:

Lumigent Log Explorer

Red Gate

The above have tools to read SQL Server Database LDF files and might be of some help to you.

|||

As mentioned by vidya and amit its not possible to restore database from LDF. This is the significance of Backups. You have to have Backup of all the database which can be easily scheduled. In this case though your db is in FULLRecovery model, you have to have a base line(full) backup to restore the database. I don't think any tool can help you on this.

Madhu

|||I have the LDF file and the database file (MDF). The MDF file is 2 days old then ldf file. Is there any way, I can apply all the transaction for these 2 days.

Rajeev

recover deleted data

I want to learn that if my query deleted all data in the table by mistake, is it possible recovery and how?

Hi Sinem,

If you did taht in a transaction:

BEGIN TRANSACTION
<DELETQuery>
--YOu Can do a ROLLBACK Here

If not or you commited it′, you need a backup to restore. If you have a Full backup and kept the transaction log backups you can do a Point in time recovery to the second where you deleted it. No backup, no data.

HTH, Jens Suessmeyer.

Recover a deleted SQL Express database

One of my SQL Express databases was deleted by mistake through MS SQL Server Management Studio Express. It is not in the Recycle Bin and I have tried to use a tool such a Active@. Undelete to recover the file but it cannot find the deleted file.

I have a backup file *.bak in the ...MSSQL\Backup folder I made using MS SQL Server Management Studio Express. When I try to use the restore database feature in SSMSE I get various errors.

Any Suggestions on how to recover the deleted file or

Any suggestions or documentation on using the restore feature in SSMSE?

Any help at all?

Thanks

George

You are in luck you have a .bak use the link below to download the eval version of SQL Server install it as a named instance and right click at the top of management studio and register the Express. Then use the Backup and restore wizard to restore in .bak to recreate your database. In the Backup and Restore wizard choose the restore from device option. Post again if you need more help.

Hope this helps.

http://www.microsoft.com/sql/downloads/trial-software.mspx

|||

Does anyone know why I can't recovery a deleted SQL Database file like I can most other deleted files. Is there something special about this file. I can't believe someone hasn't developed a procedure or application that can do this.

In addition is there a known error in SSMSE that prevents it from restoring a backup made with SSMSE through SSMSE.

Frustration is mounting!

Thanks

George

|||

The reason is database files are system files when you delete it you are undoing a task so there is no file to recover. That is the reason you use either the.bak which is the best or Transaction Log to recover. Run a search for SQL Server recovery in the BOL (books online).

There is nothing preventing it from recovering but you need the Backup and Restore Wizard which is part of Management Studio to do the Restore. That is the reason I gave you the Eval version or you could buy the developer edition it is about $32 on the web.

Hope this helps.

|||

Can I install the eval version of SQL Server on my XP Pro machine. At the present I do have access to a Window Server 200X. If so what will it conflict with the SQL Express that I have installed?

Thanks for you help.

George

|||

Yes you can the eval is Microsoft way of getting users comfortable with the product so SQL Server 2005 eval almost self install after you choose components please choose all components. One more thing the reason you need the Backup and Restore Wizard is when you choose the restore from device option SQL Server chooses the file part for you through the .bak file so you need the Wizard and it is part of Management Studio.

There is no conflict just install the eval as named instance right click at the top of Management Studio and register the Express so you it becomes local to the eval which is Enterprise edition and choose restore from device option in the Backup and Restore Wizard. Post again if you need more help. Hope this helps.

|||

Thanks, for your patience and explanation. I have restore from the backup. I was able to restore it through SQL Server Manager Express, as advertized.

George