1,105,578 Community Members

Recover SQL server 2008 database

Member Avatar
lordmwesh
Newbie Poster
2 posts since Feb 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

SQL 2008 database disappeared after canceling copy

Hi All, We have an SQL 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.

Member Avatar
vuyiswamb
Posting Whiz
316 posts since Mar 2007
Reputation Points: 17 [?]
Q&As Helped to Solve: 14 [?]
Skill Endorsements: 0 [?]
 
0
 

ok tell me , you dont see your Database or it shows but with a Red "Arrow" Pointing Down

Member Avatar
lordmwesh
Newbie Poster
2 posts since Feb 2011
Reputation Points: 0 [?]
Q&As Helped to Solve: 0 [?]
Skill Endorsements: 0 [?]
 
0
 

The database detached after I canceled a copy operation. The .ndf and .mdf files where on the server still, but my colleague renamed them and attempted to reattach them via the normal attach database command. The attach refused since the database was locked and was in single user mode.
We tried to hack the database by creating a dummy database and replacing the dummy files with the original files, to which we failed. We are not able to bring the database into emergency mode to backup the files

Member Avatar
buddylee17
Practically a Master Poster
668 posts since Nov 2007
Reputation Points: 216 [?]
Q&As Helped to Solve: 137 [?]
Skill Endorsements: 2 [?]
 
0
 

The easiest way to bring the db back online would be to drop it then attach the db, specifying the correct data and log files. Dropping the db is key, because it will clear the db's info from the master and insure that your attachment will be clean.

ALTER DATABASE [DBName] SET OFFLINE
DROP DATABASE [DBName]

I'd recommend attaching this database to a test server (with a copy of your data and log files) before dropping it from production. This will verify that the data and log files aren't corrupt.

Member Avatar
vuyiswamb
Posting Whiz
316 posts since Mar 2007
Reputation Points: 17 [?]
Q&As Helped to Solve: 14 [?]
Skill Endorsements: 0 [?]
 
1
 

Run this Script

USE MyDatabasename
        GO
        sp_configure 'allow updates', 1
        GO
        RECONFIGURE WITH OVERRIDE
        GO 
   
        sp_resetstatus 'MyDatabasename'
        GO

        sp_configure 'allow updates', 0
        GO
        RECONFIGURE WITH OVERRIDE
        GO
You
This article has been dead for over three months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: