We have an SQL Server 2008 database of around 12GB. I was trying to create a copy of the database through the Microsoft SQL Management Server Studio. The server is in production mode, and it was taking too long to copy, and had locked off all users, so i canceled the copy, but it was still taking too long so I closed the Management Server Studio and restarted it. The database was not listed, and I cannot reattach it. I have traveled the whole internet looking for a recovery solution to no avail. I have these file .ldf (1MB), .mdf(62MB) .ndf(12GB).

How can I recover the database? The backup I have is three weeks old.

migrated from stackoverflow.com Feb 5 '11 at 15:40

This question came from our site for professional and enthusiast programmers.

  • 2
    Latest backup is three weeks old!?!!?! ..... – marc_s Feb 2 '11 at 5:34
  • 1
    Backups? We don't need no stickin' backups... – Thomas Feb 2 '11 at 5:45
  • What method were you using to do the copy? Was it the detach/reattach method? Also what error do you get if you try and reattach it? If you weren't using detach/reattach to copy then did you detach it yourself somehow? Even if the service restarts unexpectedly it should just continue recovery and rollback any uncommitted transactions on restart. – Martin Smith Feb 2 '11 at 8:17
  • I Use the attach method to restore the database. I get the error --- An error occurred when attaching the database(s). Click the hyperlink in the Message column for details. --- That's all, the error I get. I did not detach it when doing copy, i just selected the database and chose copy. After canceling the COPY, the database just disappeared. The reason I did not have latest backups is we don't have enough disk space. :( I know disks is dirt cheap, ya, but since we run full backups daily, the disk space runs out very fast, so we have to delete the earlier files now and then. – lordmwesh Feb 2 '11 at 12:27
  • @lordmesh - we're going to need to know what the error message is that it asks you to click on ("click the hyperlink in the message column"). Can you click that link and then tell us what the error is? – Mark Henderson Feb 6 '11 at 5:28

i also had the same situation. what i have done is

Stop the Sql Server

first move your Db files (ie : .ldf, .mdf, .ndf) from you Data folder (better to take two copies). you may find those files in C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA

Start the Sql Server
Create Database with same name.
Stop the Sql Server

Copy the Backup Files back to the Data Folder
Start the Sql Server.

Some times it might be in Suspect Mode. you may refer below links for that

How to Restore SQL Server 2005 Suspect Database

How to restore a SQL Server database marked as "suspect"

How to fix sql server database in recovery mode?

  • When I attach the database, I get the error --- An error occurred when attaching the database(s). Click the hyperlink in the Message column for details. --- That's all, the error I get. Thank you Binil, but my server is SQL server 2008 and commands such as DBCC don't work on it while it is in emergency mode – lordmwesh Feb 2 '11 at 12:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.