Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
timl_84
Starting Member
2 Posts |
 Posted - 2010-05-20 : 07:09:04
|
| Hi,We have some tables that were created in the master database, and (for reasons unknown) the tables are now blank as of 2 days ago.The master database has not been backed up since it was first started to be used, which means we have a complete transaction log history, which we are hoping will be usefull.The usual method to recover files I understand, would be to restore a previous version of the database, and then run the transaction log files up to the specified date/date.To try to resolve this, I have stopped the sql service on the live server, taken a copy of the mdf and ldf files for the master database, and attached these onto another server. Is there any way that we can restore back from the transaction logs (or means of 3rd party software) to a period 2 days ago when these tables were populated?Many Thanks in advance |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-05-20 : 10:05:09
|
| Master behaves as though it is in simple recovery, no matter what the recovery model actually is. Hence the logs will auto-truncate (and be made available to be overwritten) every time a checkpoint occurs. You can try a 3rd party log reader (redgate has one free for SQL 2000), but I wouldn't bet anything on being able to recover.Why are there tables in the master database anyway? That DB should never be changed, tables should be created in user databases.--Gail ShawSQL Server MVP |
 |
|
|
timl_84
Starting Member
2 Posts |
Posted - 2010-05-20 : 10:47:43
|
| Hi Gail,That is the software I have been using today. After attempting a restore, and discovering that only 2 days worth of logs are there then that prompted a google search to discover that master DB is only using simple recovery mode. We have now accepted that this data is lost.We do not know what caused the data to be dropped, but SQL Log Rescue shows that there was a Drop Table run on 4 tables as user dbo.Thanks for the reply anyways, we were currently in the process of planning out the move from master to a specific user DB when this happened, as we know this is bad practice. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-20 : 15:19:25
|
| It would be prudent to include MASTER and MSDB in your backup plans (might as well backup MODEL too).Also, take a file-level copy of MASTER MDF/LDF when the SQL Service is stopped (if MASTER gets corrupted replacing the files from a known-clean-copy can save a whole lot of grief resurrecting the system - then you can restore MASTER from a more recent backup file. Make sure you make fresh copies of MASTER MDF/LDF after each service pack is installed. |
|
|
|
|
|
|
|
|
Restore data from transaction logs in Master DB