1.11M Members

Recover SQL server 2008 database

 
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.

 
0
 

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

 
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

 
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.

 
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 six months: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: