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.
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
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.