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
, make it like RAID-5. Only allow striping across 3 or more devices.|||