Member Avatar for lordmwesh

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.

  • 3 Contributors
  • 4 Replies
  • 188 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by vuyiswamb
Member Avatar for vuyiswamb

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

Member Avatar for lordmwesh

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 for buddylee17

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.

Edited by buddylee17 because: n/a
Member Avatar for vuyiswamb

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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.