MSSQL Server – Database Recovery [Manual] (after a crash) – MS SQL Server v2005 and above

 

Tried out Paul Randal’s database recovery steps (http://www.sqlskills.com/blogs/paul/post/TechEd-Demo-Creating-detaching-re-attaching-and-fixing-a-suspect-database.aspx)  and it works a charm —

Note that the database server will try to and most like recover itself.  But, in our case, the time for a successful recovery was too much and so we chose to ‘circumvent’ possible recovery by renaming the database log files.

Here are the steps to re-create database crash & manual recovery:

  • Create database Demo
  • Create Table
  • Generate Transactions
  • Get MS SQL Server’s Process ID
  • Kill MS SQL Server Process
  • Rename Database Log Files
  • Restart MS SQL Server Service
  • Execute Manual Restore
    • Set database to emergency mode
    • Set database to single user
    • Execute DBCC CheckDB with repair_allow_data_loss
    • Set database to multi_user

 

Create Database Demo

 


if db_id('Demo') is not null
begin
    DROP DATABASE Demo;
end
go

CREATE DATABASE Demo
ON
PRIMARY
(
  NAME = Demo_PrimaryDataFile,
  FILENAME = 'E:\MSSQL\datafiles\DEMO\datafiles\Demo_PrimaryDataFile.mdf'
    , SIZE = 3 MB
    , MAXSIZE = 10 MB
    , FILEGROWTH = 10 %
),
(
  NAME = Demo_SecondaryDataFile,
  FILENAME = 'E:\MSSQL\datafiles\DEMO\datafiles\Demo_SecondaryDataFile.ndf'
    , SIZE = 1 MB
    , MAXSIZE = 10 MB
    , FILEGROWTH = 10 %
),
(
  NAME = Demo_SecondaryDataFile2,
  FILENAME = 'E:\MSSQL\datafiles\DEMO\datafiles\Demo_SecondaryDataFile2.ndf'
    , SIZE = 1 MB
    , MAXSIZE = 10 MB
    , FILEGROWTH = 10 %
)
LOG ON
(
  NAME = Demo_LogFile,
  FILENAME = 'E:\MSSQL\datafiles\DEMO\logfiles\Demo_LogFile.ldf'
    , SIZE = 2 MB
    , MAXSIZE = 15 MB
    , FILEGROWTH = 3 MB
)
,
(
  NAME = Demo_SecondaryLogFile,
  FILENAME = 'E:\MSSQL\datafiles\DEMO\logfiles\Demo_SecondaryLogFile.ldf'
    , SIZE = 2 MB
    , MAXSIZE = 15 MB
    , FILEGROWTH = 3 MB
)
COLLATE Latin1_General_CI_AS;

 

Create Table – Session

 

use [Demo]
go

if object_id('session') is not null
begin
drop table session
end
go

create table session
(
  [id] int not null identity(1,1) primary key
, [dateAdded] datetime not null default getdate()
, [serverName] sysname not null default @@servername
)
go

 

Generate Transactions


use [demo]
go

set nocount on
go

declare @id int

set @id = 1

while (@id != 0)
begin

      insert into session default values

      set @id = @id + 1

      print @id

end
go

 

Get MS SQL Server’s System Process ID (PID)

use [master]
go

select serverproperty('processid') as processID


 

Using Task Manager kill that process (PID)

  • Launch Task Manager
  • Identify the matching Process ID
  • Kill that Process

 

 

 

Using Windows Explorer – Rename \ Move the Log files

  • Rename E:\MSSQL\datafiles\DEMO\logfiles\Demo_LogFile.ldf to xDemo_LogFile.ldf

 

  • Rename E:\MSSQL\datafiles\DEMO\logfiles\Demo_SecondaryLogFile.ldf to xDemo_SecondaryLogFile.ldf

 

 

 

Restart MS SQL Server – And, the erroglog  will indicate that the Demo database can not be brought online


2010-09-27 22:34:28.48 spid24s     Error: 17207, Severity: 16, State: 1.

2010-09-27 22:34:28.48 spid24s     FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'E:\MSSQL\datafiles\DEMO\logfiles\Demo_LogFile.ldf'. Diagnose and correct the operating system error, and retry the operation.

2010-09-27 22:34:28.48 spid24s     File activation failure. The physical file name "E:\MSSQL\datafiles\DEMO\logfiles\Demo_LogFile.ldf" may be incorrect.
2010-09-27 22:34:28.48 spid24s     Error: 17207, Severity: 16, State: 1.

2010-09-27 22:34:28.48 spid24s     FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'E:\MSSQL\datafiles\DEMO\logfiles\Demo_SecondaryLogFile.ldf'. 

Diagnose and correct the operating system error, and retry the operation.
2010-09-27 22:34:28.48 spid24s     File activation failure. The physical file name "E:\MSSQL\datafiles\DEMO\logfiles\Demo_SecondaryLogFile.ldf" may be incorrect.

2010-09-27 23:20:26.75 spid24s     The log cannot be rebuilt because the database was not cleanly shut down.

 

To fix do the following:

  • Set database to emergency mode
  • Set database to single user
  • Execute DBCC CheckDB with repair_allow_data_loss
  • Set database to multi_user

 

 


ALTER DATABASE Demo SET EMERGENCY;
GO 

ALTER DATABASE Demo SET SINGLE_USER;
GO

DBCC CHECKDB (Demo, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO

ALTER DATABASE Demo SET MULTI_USER;
GO

 

 

References

  1. Creating, detaching, re-attaching, and fixing a suspect database
    http://www.sqlskills.com/blogs/paul/post/TechEd-Demo-Creating-detaching-re-attaching-and-fixing-a-suspect-database.aspx

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s