=?Utf-8?B?UmljaGFyZCBNLg==?= (anonymous@discussions.microsoft.com) writes:
[quoted text, click to view]

If it only would take several hours to get that server back in order,
you should be glad. Particularly, you should be glad if
sp_attach_single_file_db actually works.

It is possible you can get some help from this forum if you provide some
more detailed information, but if sp_attach_single_file_db does help
you, I would give Microsoft support a call first thing in the morning.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

Richard,

If you can programmatically with a bit of string handling work out the dbname based on the filename,
and the files are all in a single directory, and they actually attach ...

Then you may find the following helpful:

set nocount on

declare @CrLf char(2)
set @CrLf = CHAR(13) + CHAR(10)
declare @Path varchar(200)
set @Path = 'c:\mssql\data\'

-- Get list of files in directory
declare @SqlCmd varchar(300)
set @SqlCmd = 'dir ' + @Path + '*.* /a-d /o:d /t:c /n /B'

create table #DirList
(
RowID int identity,
FileOnly varchar(400)
)

insert #DirList ( FileOnly )
exec master..xp_cmdshell @SqlCmd

select 'sp_attach_single_file_db @dbname = [' + left(FileOnly, len(FileOnly)-4) + '], @physname = ''' + @Path + FileOnly
+ '''' + @CrLF + 'Go' + @CrLF
from #DirList
where FileOnly like '%.mdf'
order by RowID

drop table #DirList


Cutting and pasting in Query Analyser ( with a bit of editing to get rid of system databases etc ).

Regards
AJ

[quoted text, click to view]

Hi Erland,

THank you for your reply. You are right! The "sp_attach_single_file_db " did work, but with 300 DBs it would take for ever. However, as luck may have it the hard drive on the SQL server containing the OS completely crashed! It went dead. So, the cause seems to be a combination of file curruption due to hardware failure. So, what I did was create a new server and migrated the data and log files just before the server completely died. On the new server, when I installed SQL and attempted to recover by installing the exact version and replacing the existing ystem databases witht he original so the server can see all the databases, the server completely all the databases were suspect. Hence, this means that allt eh files I transferred were corrupted some how. Now, I have two options:

A: Manually use "sp_attach_single_file_db " on all databases to recover it since it seems to work or

B: Brign the old server back online... and attempt to rectify a hand full of suspect databases.


I choose B, however the OS drive was completely dead. Luckily the drivers containing the data files and the otehr drive containg the log files were OK. The server was then rebuilt, with a new OS drive. Win2K server ws installed and surely it saw the other two drives with the data and log files. I then installed SQL server and the updates. As luck may have it, all the databses were ther with an exception of a dozen datasbes still suspect. S, I then applied "sp_attach_single_file_db : to repair those dozen databases and performed an immediate backup for all the databases. Right now, everything seems to be working but I just don't trust those drives. What do you think?

Regards,

Rchard

Hi

THank you for your reply. You are right! The "sp_attach_single_file_db " did work, but with 300 DBs it would take for ever. However, as luck may have it the hard drive on the SQL server containing the OS completely crashed! It went dead. So, the cause seems to be a combination of file curruption due to hardware failure. So, what I did was create a new server and migrated the data and log files just before the server completely died. On the new server, when I installed SQL and attempted to recover by installing the exact version and replacing the existing ystem databases witht he original so the server can see all the databases, the server completely all the databases were suspect. Hence, this means that allt eh files I transferred were corrupted some how. Now, I have two options:

A: Manually use "sp_attach_single_file_db " on all databases to recover it since it seems to work o

B: Bring the old server back online... and attempt to rectify a hand full of suspect databases

I choose B, however the OS drive was completely dead. Luckily the drivers containing the data files and the otehr drive containg the log files were OK. The server was then rebuilt, with a new OS drive. Win2K server ws installed and surely it saw the other two drives with the data and log files. I then installed SQL server and the updates. As luck may have it, all the databses were ther with an exception of a dozen datasbes still suspect. S, I then applied "sp_attach_single_file_db : to repair those dozen databases and performed an immediate backup for all the databases. Right now, everything seems to be working but I just don't trust those drives. What do you think

Regards

Thank you Andrew!