Help!!!! Restore file mdf after exec "Drop table..." to previous state.

Hi all.
First, sorry about my poor english.

I have a database which above 6 gb data and i have droped all table in my database.
I try to restore from log file but my database is set to simple backup and auto shrink so log file doesn't help anymore.
I have used some software to recovery from log file too but useless.
My only hope right now is mdf file.
Please help me. How could i restore my mdf file to state before i droped tables.
Thanks

Since you are in simple recovery model, you can't use transaction log backups and none of the recovery tools on the market will be able to help since they rely on the contents of the transaction log to get the data back. If you don't have a backup of the database, the data is gone.

|||

There is actually a VERY brute force way that you really aren't going to like. Dropping objects in SQL Server works very similar to deleting a file on the operating system. You don't actually wipe out the file, you just remove an 8 byte header that essentially says "I'm a valid file". In SQL Server, you remove the object from the SQL Server metadata. Once the entries for the object have been removed from sysindexes, SQL Server will no longer find the data. Unless you have done something to reallocate those pages to another object in which case everything will be wiped out on that page, you can actually get to the raw pages. This is NOT pretty. You can use the DBCC PAGE command to read a page. You would wind up running this one page at a time and extracting the data from each page as you go. Then you would have to reconstruct everything and manually reload the data. (Did I mention, you REALLY don't want to have to do this which is why you always take frequent backups.)

So, step 1 in this process is to immediately create a backup of the database. Step 2 is to immediately put a backup plan in place so that the database is being backed up on a routine basis. Then you can use the following set of commands to manually extract the data. Below is an example. You would substitute your database name. You would then start at file = 1 and page = 1 and incrementally walk through page 2, 3, 4, ... and then repeat the process for any other files present in the database. The final step in the process would be to test your backup strategy and continue to test it on a frequent basis. (If you don't keep a backup of a database, you are basically saying that it is unimportant and you don't care about losing it.)

--dbcc page ( {'dbname' | dbid}, filenum, pagenum [, printopt={0|1|2|3} ])

dbcc traceon (3604)

dbcc page (pubs,1,88,3)

go

|||Thanks very much.
As you mentioned, i try to use DBCC PAGE and it has a lot of thing which i don't understand.
Please show me the way to read info from what DBCC Page return and meaning of those.
Thanks again.|||You are reading the raw storage on a page using this command. It is easier to just point you at a book. Go out and pick up a copy of "Inside SQL Server 2000" by Kalen Delaney. There are about 100 pages of material that relate back to interpreting and utilizing the output from DBCC PAGE.|||I will read it and find what i can do with that book.
You help me very much, thanks. :)|||BTW, the DBCC PAGE command is an undocumented command which means it is not supported by Microsoft (but nobody is stopping you from not using them). Try Googling around for some references on these undocumented commands, stored procedures and functions and you will find a lot of useful ones.|||I have read the book which you mentioned. I try to find out exactly what you mean when you said i can read pages in mdf file one by one but i don't get it.
Could you tell me which tools or language or anything else to perform that idea.
Thanks|||

If you query sysfiles within a database, you get the list of FileIDs. In general, if you have an mdf file, it will be fileID = 1.

So you use DBCC page to read each page manually by doing the following:

1. Open a query window.

2. Execute:

DBCC TRACEON(3604)

DBCC PAGE(<database name>,1,1,3)

3. Read the contents of the page and extract any data that you need

4. Execute:

DBCC TRACEON(3604)

DBCC PAGE(<database name>,1,2,3)

5. Read the contents of the page and extract any data that you need

6. Execute:

DBCC TRACEON(3604)

DBCC PAGE(<database name>,1,3,3)

7. Read the contents of the page and extract any data that you need

8. Execute:

DBCC TRACEON(3604)

DBCC PAGE(<database name>,1,4,3)

Etc. There isn't any tool to do this. You do this in Transact-SQL. Like I said when I brought this up, this is an absolute last resort if you can access the database, it is EXTREMELY ugly, EXTREMELY resource intensive, and EXTREMELY time consuming. Why, because you pull page 1, then page 2, then page 3, then page 4, then page 5, then.... until you get an error message that the page doesn't exist. Even in a small database, you can have hundreds or thousands of pages, so that means that in even a small database, you would have to execute DBCC PAGE hundreds or thousands of times, read the contents of the page, and manually extract the data. In production databases, you can easily have millions or billions or pages.

Now you get the idea why everyone jumps up and down about backups and why it would be nice if Microsoft could ship a 100 foot tall poster in the box with SQL Server that has exactly one message on it, backup your databases. If you don't have backups and you need to recover data, there are few, if any options, and you are not going to like ANY of the options.

|||Michael Hotek,
I already exec Dbcc page and i get a lot of things which i don't understand.
So, what do you mean when you said:"Read the contents of the page and extract any data that you need". How to read and extract it? By T-SQL or something else?
About my database, I give up.So, right now,I just want to know more about SQL and the way to extract the contents of the page.
Thanks
|||Copy it from the results window and paste it into Notepad, another Query window etc. This is a 100% manual process. There are zero tools. There are no shortcuts. There is no easy way to do this. In essence you are going to manually type back in every single piece of data within the database. If you go through about 100 pages in your database and look at the results, the data is pretty self-explanatory. Since you are starting at page 1, there are going to be entire pages that you aren't going to be able to do things with such as the PFS page, GAM page, SGAM page, index pages, etc. But, there are data pages in your database and when you hit one, it will be very apparent where the data is and what it means. Other than that, it's up to you. You have to look at the results pane and you have to highlight the data and copy it back out into something else in order to stick it back into your database.|||Michael Hotek
Now, I understand what you mean, thanks. :)sql

Help!

Can anyone explain me what i am doing wrong:
I have a script that creates a linked server to them exec
a cmd through it.. when i do this through a cursor i get
this error:
MSDTC on server XPTO is unavailable (whcih is not true)
The operation could not be performed because the OLE DB
provider was unable to begin a ditributed transaction.
However if i do it seperatley.. without cursor.. it works..
Can anyone help me?
P.S
I posted a previous question asking about how to insert
sp_space used in a table.. however either way suggest
doesnt work.. any other suggestions?First of all choose an appropriate subject for your
posting. We all need help here but obviously some are
expert in one domain and others in a different domain.
Your subject does not help your cause.
Second what verison of Sql are you using? Can you post the
actual error message and text. There are a few bugs with
Linked servers and i suspect you are encoutering one.
>--Original Message--
>Can anyone explain me what i am doing wrong:
>I have a script that creates a linked server to them exec
>a cmd through it.. when i do this through a cursor i get
>this error:
>MSDTC on server XPTO is unavailable (whcih is not true)
>The operation could not be performed because the OLE DB
>provider was unable to begin a ditributed transaction.
>However if i do it seperatley.. without cursor.. it
works..
>Can anyone help me?
>P.S
>I posted a previous question asking about how to insert
>sp_space used in a table.. however either way suggest
>doesnt work.. any other suggestions?
>.
>

Help With Variable Containing Datetime

HI,

I HAVE A PROBLEM WITH A VARIABLE THAT I AM NOT BEEN ABLE TO SORT OUT.

DECLARE @.DATE NVARCHAR(100)
SET @.DATE = MONTH(GETDATE())
EXEC ('SELECT ' + @.DATE)

WHEN I RUN THIS, I HAVE NO PROBLEM AS IT GIVES ME THE ANSWER SAY 5 AS IT IS MAY.

BUT,

WHEN I RUN A VARIABLE CONTAINING DATETIME,

DECLARE @.DATE DATETIME
SET @.DATE = GETDATE()
EXEC ('SELECT ' + @.DATE)

IT GIVES ME AN ERROR :-

"Line 1: Incorrect syntax near '12'. "

IS THERE A WAY THAT I CAN USE DATETIME AS VARIABLE IN THIS CASE.Try this:
DECLARE @.DATE DATETIME

SET @.DATE = GETDATE()
EXEC ('SELECT ' + ''''+@.DATE+'''')

Harshal.|||Hi,

Thanks For Your Timely Help,the Problem Got Sorted Out In A Jiffy.|||And what about this approach:

declare @.date datetime
set @.date = getdate()
select @.date

Greetz,
DePrins
;)|||Hi,

Yes, I Know That Method ,but It Can't Be Used Many Times :- For E.g:- If I Want To Create Table_names Containing Month & Year Name Like Customer_data_for_20july2004

Then I Have To Use The Exec Command.|||Hi,

Yes, I Know That Method ,but It Can't Be Used Many Times :- For E.g:- If I Want To Create Table_names Containing Month & Year Name Like Customer_data_for_20july2004

Then I Have To Use The Exec Command.

Try this:
DECLARE @.DATE DATETIME,
@.Dt_Dsc Varchar(50),
@.SQL varchar(200)

SET @.DATE = GETDATE()
Set @.Dt_Ddc = Replace(Cast(Left(@.Date , 11) AS varchar(50)),' ','_')
Set @.SQL = 'Select ' + @.Dt_Dsc

Exec (@.SQL)

Gil|||In order to prevent you from tearing out your hair later, I'd like to strongly suggest that you format your dates differently and use them as a prefix rather than a suffix on your table names. If you format the prefix as E20040720 instead of 20_july2004, you won't have problems with 12_dec2000 sorting between 04_jul2004 and 20_may2010! If you use a prefix instead of a suffix, all of your extract tables will sort together by date of extract when you display table names sorted alphabetically. You can use:DECLARE @.prefix VARCHAR(10)
SET @.prefix = 'E' + Replace(Convert(VARCHAR(10), GetDate(), 120), '-', '')Note that I added a letter before the digits, just to make it easier to work with the tables going forward. Sometimes it gets messy trying to cope with table names that start with a digit.

-PatP|||Hi,

Thanks Pat And Glubstein That Was Wonderful Solved Many Of My Problems And Saved Many Headaches.

Thanks Once Again