Restore DB from MDF without LDF
I have SQL Server 2005, yesterday our data was corrupted and we need to know if/how we can recover from an MDF without the LDF. I've found a couple solutions when I searched but they all seem to be for SQL Server 2000. Any help is greatly appreciated.
Not reliably, no.
You can try this:
EXEC sp_attach_single_file_db @dbname = 'dbname',
@physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\dbname.m
But its certainly not guaranteed to work.
You can try this:
EXEC sp_attach_single_file_db @dbname = 'dbname',
@physname = 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\dbname.m
But its certainly not guaranteed to work.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
sp_attach_single_file_db will only work if the file was detached cleanly
CREATE DATABASE NewDB
ON (NAME = 'NewDB_data', FILENAME = 'c:\temp\newdb.MDF')
FOR ATTACH_REBUILD_LOG
will respond with error about ldf not existing, but will still create:
File activation failure. The physical file name "c:\temp\newdb_log.ldf" may be incorrect.
New log file 'c:\temp\newdb_log.LDF' was created.
ON (NAME = 'NewDB_data', FILENAME = 'c:\temp\newdb.MDF')
FOR ATTACH_REBUILD_LOG
will respond with error about ldf not existing, but will still create:
File activation failure. The physical file name "c:\temp\newdb_log.ldf" may be incorrect.
New log file 'c:\temp\newdb_log.LDF' was created.
ASKER
sp_attach_single_file_db doesn't look like an option b/c the mdf was still attached when we our hard drive was corrupted.
ASKER
thanks twoboats. That is very similar to the solutions I've found for SQL Server 2000.
You're welcome.
Yes, it's a modification of same, just with a way round not being able to update systables directly.
Yes, it's a modification of same, just with a way round not being able to update systables directly.
ASKER
When I run this line:
ALTER DATABASE <your db> SET EMERGENCY
I get an error:
...The PageAudit property is incorrect.
ALTER DATABASE <your db> SET EMERGENCY
I get an error:
...The PageAudit property is incorrect.
, [ @physname = ] 'physical_name'