Major SQL Server outage.
-
Well over the weekend we had a server loss its stripe and cause major problems with SQL Server. I was asked by the DBA who owns this server for some help. He had been able to get SQL Server running again after may hours and a recovery of the Master database from a back up. That was the good news.
No the bad. Several other databases were marked suspect. Get worse! There were no back ups of these databases!
Looking at the devices I see that on on of the databases that the MDF and LDF file reads "0" for the size. (I know no hope of recovery there) Another has data in the MDF but none in the LDF. Still another has data in the MDF but none in the LDF.
Any suggestions?
(Note: This is a Death penalty State so he still lives)
Stacey W. A. Gregerson -
Unfortunately, if you have no backups there is nothing to restore...
How did you lose a stripe?
Did you have RAID 0 (no fault tolerance)?
-
First, "I" was not the one that did not have any backups!
Second, Not sure what the problem was but it hit three drives on a raid 5 system. We ran a chkdsk a couple of times and each time we did a few more files on the server were repaired. Just noe of the SQL Server ones.
Stacey W. A. Gregerson -
The "you" was more of a royal you. I was just stating a fact, not criticising.
As for the 3 disks packing up, that's unlucky in anyone's book...
-
First, "I" was not the one that did not have any backups!
Second, Not sure what the problem was but it hit three drives on a raid 5 system. We ran a chkdsk a couple of times and each time we did a few more files on the server were repaired. Just none of the SQL Server ones.
Another starange fact. I did a an emergency recover and then copied the data over to a new database. Problem is that last months data is missing. Not sure why.
Stacey W. A. Gregerson -
Attaching an MDF without an LDF inplace will create a new log file, the db may recover to a point, maybe, maybe not - worth a go.
unlucky thou
Dan
-
Not really I have seen the hot spare not to kick in and lost of two other drives so I've been there maybe I am also in that lucky group. This is one of the reasons I hate raid 5
* Noel -
just my 2ct.
- Also in desaster cases, first make a copy of what you (still) have !
- What made you (all drp-actors) decide to recover master ??
- wich version of sqlserver is this ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
SQL Server 2000 Standard. He recovered master because it was also corrupt. (He had a back up of that).
We have rebuilt everything on another server. He lost two small database which he created a month ago. He also lost about a months worth of data out of another database after I recovered it using the emergency mode and then copying the data out to a new server.
I plan on keeping the old server around for a couple of weeks and playing with it to see if I can learn anything or find fast modes of recovery.
I would like to thank everyone for their responses and suggestions.
And one other thing. After a fair nights sleep I have granted a stay of execution for the DBA responsible for this server. After all he just got engaged and his mind is not where it is normally.
Stacey W. A. Gregerson -
so his master-backup was older than one month ??
Define new backup Schedules using sql-backups and using sqlagent.
BEGIN TRANSACTION
DECLARE @JobID BINARY(16)
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'Database Maintenance') < 1
EXECUTE msdb.dbo.sp_add_category @name = N'Database Maintenance'
IF (SELECT COUNT(*) FROM msdb.dbo.sysjobs WHERE name = N'YOURServerFull') > 0
PRINT N'The job "YOURServerFull" already exists so will not be replaced.'
ELSE
BEGIN
-- Add the job
EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'YOURServerFull', @owner_login_name = N'sa', @description = N'Full backup Server volgens ALZ-systeem', @category_name = N'Database Maintenance', @enabled = 0, @notify_level_email = 0, @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1, @step_name = N'YOURUserDb Full Database Backup', @command = N'BACKUP DATABASE [YOURUserDb] TO [YOURUserDbFull] WITH INIT ,
NOUNLOAD , NAME = ''YOURUserDb_Full'',
SKIP , STATS = 10, DESCRIPTION = ''Full Database Backup'' , NOFORMAT ', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 0, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 3
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 2, @step_name = N'master Full Database Backup', @command = N'BACKUP DATABASE [master] TO [masterFull] WITH INIT ,
NOUNLOAD , NAME = ''master_Full'',
SKIP , STATS = 10, DESCRIPTION = ''Full Database Backup'' , NOFORMAT ', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 0, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 3
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 3, @step_name = N'msdb Full Database Backup', @command = N'BACKUP DATABASE [msdb] TO [msdbFull] WITH INIT ,
NOUNLOAD , NAME = ''msdb_Full'',
SKIP , STATS = 10, DESCRIPTION = ''Full Database Backup'' , NOFORMAT ', @database_name = N'master', @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 0, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 3
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 4, @step_name = N'DeleteOldBackupSrv', @command = N'Del \\uncname\bu2tapeDay$\YOURServer\Database\*.* /F /Q ', @database_name = N'', @server = N'', @database_user_name = N'', @subsystem = N'CmdExec', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 3, @on_fail_step_id = 0, @on_fail_action = 3
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 5, @step_name = N'CopyToBackupSrv', @command = N'XCOPY C:\MSSQL\BACKUP\*.* \\uncname\bu2tapeDay$\YOURServer\Database\ /c /k /h /v /y', @database_name = N'', @server = N'', @database_user_name = N'', @subsystem = N'CmdExec', @cmdexec_success_code = 0, @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'', @on_success_step_id = 0, @on_success_action = 1, @on_fail_step_id = 0, @on_fail_action = 2
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the job schedules
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Backup Schema x', @enabled = 1, @freq_type = 4, @active_start_date = 20050706, @active_start_time = 193000, @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0, @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
-- Add the Target Servers
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply