Restore Mdf file from a crashed hard disk

Question

I have a MS SQL server 2005 database on my database server. Recently the database server crashed, so I need to move the database to a new server. Is there anyway to restore teh mdf file from a crashed hard disk, assuming that the file wasn't located on bad sectors?

score 5 · Accepted Answer · answered Jul 31 '09 at 04:44

5

Yes. If you have the ldf file you've got a better chance of success.

Simply take the mdf and ldf files are copy them to the new server. Then attach the database to the new instance using the sp_attach_db system stored procedure or using the SQL Server Management Studio.

If you don't have the ldf file you can attach the database using just the mdf file using the sp_attach_single_file_db system stored procedure or by using the SQL Server Management Studio.

answered Jul 31 '09 at 04:44

mrdenny

27,074
4
40
68

MSDN doc (https://docs.microsoft.com/en-us/sql/relational-databases/databases/attach-a-database?view=sql-server-ver15#Prerequisites) says that `the database must first be detached from any existing SQL instance. If you attempt to attach a database that has not been detached returns an error`. So what you are suggesting is unsafe? – variable May 07 '22 at 12:05
It will work in most cases to attach the database without the log, or when the database hasn't been properly detached. If you don't have the log file, there's a risk of there having been an in flight transaction when the database see we went down. But if that's all you have, then that's what you have. – mrdenny May 08 '22 at 18:02

score 0 · Answer 2 · answered Jul 31 '09 at 12:29

Do you have a recent backup file as well? Not only could you follow the instructions from mrdenny but if that fails, you could try restoring from your last backup.

If you dont have backups (and we hope you can restore your db!), think about implementing an SQL Server agent job. You could script the database backup and let the agent run at some time that you know the server is being least used.

If you have system wide backups in place this file will be picked up as SQL server will only lock the file while the backup is being created.

The script below is for SQL Server 2005 SQL Server Agent. This shows you a schedule that runs at 12:10AM every single day and backups a database called MyDB. You can adjust as necessary. Run this inside your management studio to get an understanding of how to setup a scheduled job from SQL Server Agent.

USE [msdb]
GO
/****** Object:  Job [My Backups]    Script Date: 07/31/2009 21:56:21 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 07/31/2009 21:56:21 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'My Backups', 
        @enabled=1, 
        @notify_level_eventlog=0, 
        @notify_level_email=0, 
        @notify_level_netsend=0, 
        @notify_level_page=0, 
        @delete_level=0, 
        @description=N'Automate System Backups', 
        @category_name=N'[Uncategorized (Local)]', 
        @owner_login_name=N'Administrator', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [Backup MyDB]    Script Date: 07/31/2009 21:56:22 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Backup MyDB', 
        @step_id=1, 
        @cmdexec_success_code=0, 
        @on_success_action=1, 
        @on_success_step_id=0, 
        @on_fail_action=2, 
        @on_fail_step_id=0, 
        @retry_attempts=0, 
        @retry_interval=0, 
        @os_run_priority=0, @subsystem=N'TSQL', 
        @command=N'BACKUP DATABASE [MyDB] TO  DISK = N''C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\MyDB.bak'' WITH NOFORMAT, INIT,  NAME = N''MyDB Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
', 
        @database_name=N'master', 
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Daily Backups', 
        @enabled=1, 
        @freq_type=4, 
        @freq_interval=1, 
        @freq_subday_type=1, 
        @freq_subday_interval=0, 
        @freq_relative_interval=0, 
        @freq_recurrence_factor=0, 
        @active_start_date=20090731, 
        @active_end_date=99991231, 
        @active_start_time=1000, 
        @active_end_time=235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

Restore Mdf file from a crashed hard disk

2 Answers2