Help! Unable to attach DB

[DotNetNewbie]

Help!

I've come back off holiday to a nightmare!

One of the SQL servers ran out of space and one of my collegues decided that deleting files was the best solution.

Now I dont know which files he deleted but one of our main DB's is now no longer mountable, the .ldf file has either been corrupted, deleted, renamed! so all I have is the .mdf file.

Now to make matters worse its one of those databases that was created and never added to the normal backup and the SQL backup overwrites the previous days backup. So basically I have no working files to work from.

I'm currently stuck at what to do!

Any ideas most welcome.

D.

 

[Jamfool]

Have you tried using attach sp_attach_single_file_db:

USE master;
GO
EXEC sp_detach_db @dbname = 'AdventureWorks';
EXEC sp_attach_single_file_db @dbname = 'AdventureWorks',
@physname = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf';

 

[bikerboy718]

I agree with Jam. Barring that the mdf file has not gotten corrupted you should be able to restore the database with this command. Also it may be wise to keep a local backup on your box as well as creating a taped backup of the database.

With Great Power Comes Great Responsibility!!!

Michael

 

[DotNetNewbie]

Morning,

We've been able to find a working backup which is 3 months old. We also have .trn files which I assume we can restore over the database to bring it back up to date?

D.

 

[SQLBill]

Maybe. Do you have all the .trn files from the time the full backup was made? Restoring log files requires an 'unbroken chain' of them.

Remember to restore all files using the WITH NORECOVERY option until the very last file.

-SQLBill

Posting advice: FAQ481-4875