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?

Answer 1

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.

Answer 2

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: