• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 15829
  • Last Modified:

HELP!!!!!!!!!! How to recover suspect sql 2000 database

I have a sql 2000 database that has been made suspect by having the log file deleted then replaced with an older copy.  This is what happened.  The database went into readonly mode because the log file had gotten so big it filled the volume.  Well my programmer deleted the log file thinking he could just put in an empty dummy in its place.  As you know this would not work so he restored a log file that was on a 3 day old backup.  This made the database suspect. I have detached the database at this time and it won't reattach.  Any suggestions?
0
rhdyes
Asked:
rhdyes
3 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
This is bot tricky, but i found it as usefull

detach ur db,
move ur log file to someother location
Create a new balnk text document (Better in the same folder) and rename it as urLog file (remember to remove the extension )
then attach the db, point the log as new log file we just created
0
 
roshkmCommented:
U have to restore the Log file from the mdf file. There is no use with the log file from the back up,because it will definitely will have mismatches.

Once it is done, u can dettach the database, delete the log file, reattach the mdf file. U need not have to create a dummy log file. SQL Server will create one if it is not present.

Regards,
RKM.
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
rhdyesAuthor Commented:
My database is already dettached.  When Iry to attach without a log file I get an error that tells me "the physical file name may be incorrect.  When I use a dummy empty text file I get an i/o error telling me I reached the end of file trying to read the log file.  When I dettached this database, it was already in the suspect mode.
0
 
mcmonapCommented:
Hi rhdyes,

SQL will not recreate the log file on its own, you must use the sp_attach_single_file_db stored procedure.  
http://msdn2.microsoft.com/en-us/library/ms174385.aspx

Depending on when your last SQL backup was you may lose data from the actions that have been taken.  In this situation the DB and log should have been backed up and then the DBCC SHINKFILE command should have been used, check out these:

http://msdn2.microsoft.com/en-us/library/ms186865.aspx
http://msdn2.microsoft.com/en-us/library/ms189493(SQL.90).aspx
0
 
mcmonapCommented:
That second link doesn't work correctly, it will need to be copied and pasted into a browser
http://msdn2.microsoft.com/en-us/library/ms189493(SQL.90).aspx
0
 
Scott PletcherSenior DBACommented:
I agree -- you have very likely damaged the db, possibly beyond the point of recovery.

For the future, remember these rules:

Rule 1) *NEVER* detach a db that has any problem.

SQL may not allow the db to be re-attached.


Rule 2) *NEVER* use EM to do a detach/attach.  *ALWAYS* use QA.

EM is too flaky and can't be trusted for that, I've seen too many issues with it.


Rule 3) *ALWAYS* backup a detached mdf before attempting to (re)attach it if you suspect that for any reason the attach won't work.  

For each attach attempt, use a fresh copy of the original .mdf.  The attach attempt will update the .mdf, likely preventing future attach even once you correct the previous error(s).

If the db is very large, you might have to skip this.  If so, if possible, take a complete backup of the db before beginning any activity.
0
 
ptjcbCommented:
Sadly you may have to walk away from trying to reattach the damaged db and do a restore with move option to restore the 3 day old backup and lose all of the changes since then (unless you have transaction backups).
0
 
rhdyesAuthor Commented:
Thanks for the input from everyone.  I called mircosoft and with some assistance from them I was able to recover my server.  FYI. here are the steps I took.

I copied my mdf, ndf and ldf files of the orignal database to another location. I then deleted the files from the locations they were at on the drive.
I "created" the same exact database with the same structure and names. I had my database spread across 2 drives.
I then took the server down, renamed my log file, copied my original mdf and ndf files over the newly created files.
Start sql and the database of course came up suspect.

I started query analyzer to the master or temp database.
I then ran these series of statements
exec sp_configure 'allow override',1
reconfigure with override
update sysdatabases set status = 32768 where name='dbname'
dbcc rebuild_log

This rebuilt the transaction log.  

I then ran
dbcc checkdb('dbname')
all the tables checked out  and everything seems to be fine.
0
 
rhdyesAuthor Commented:
This question can be closed
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now