Database recovery procedure for corrupted databases

Follow the following procedure to recover corrupter SQL Server database. It is assumed that database is in full recovery mode, full database backup is available and transaction log backups was taken sometime before the failure.

If database has been corrupted due to server hardware failure and is no longer accessible through SQL Server Management Studio or SQL Analyzer, execute the following steps to recover the data.

1. Attempt to backup database transaction log so all activities that happen between last transaction log backup and point of failure can be recovered. If not successful, database can only be restored to the state when last transaction log backup was taken. Execute transaction log backup using the following statement: BACKUP LOG <database name> TO DISK = <path\filename.bak> WITH NO_TRUNCATE

2. Drop/Delete corrupted database from the server.

3. Re-Create database by restoring the last full database backup with NO RECOVERY option (use SQL Server Management Studio).

4. Sequentially restore all transaction log backups taken in the period from the last full database backup and the point of corruption. If Trans log backup attempted in step 1 was successful, restore it last and recover the database.

Quick and simple aggregation of Hierarchical Data in SQL Server

Sometimes data containing hierarchies have to be aggregated and total calculated on different levels. The following script will help archive fast calculation of totals on each level of hierarchy.

INPUT DATA STRUCTURE AND VALUES

0100***Level 1*****$21
0110*****Level 2***$6
0111******Level 3**$1
0112******Level 3**$2
0113******Level 3**$3
0120*****Level 2***$15
0121******Level 3**$4
0122******Level 3**$5
0123******Level 3**$6


-- define codes table with hierarchy

 Declare @Code Table (Code varchar(10), Hierarchy varchar(50), Description varchar(100))

 INSERT @Code (Code, Hierarchy, Description) VALUES ('0100', '1.', 'Total')
 INSERT @Code (Code, Hierarchy, Description) VALUES ('0110', '1.01.', 'Total of 0110')
 INSERT @Code (Code, Hierarchy, Description) VALUES ('0111', '1.01.01.', 'Value of 0111')
 INSERT @Code (Code, Hierarchy, Description) VALUES ('0112', '1.01.02.', 'Value of 0112')
 INSERT @Code (Code, Hierarchy, Description) VALUES ('0113', '1.01.03.', 'Value of 0113')
 INSERT @Code (Code, Hierarchy, Description) VALUES ('0120', '1.02.', 'Total of 0120')
 INSERT @Code (Code, Hierarchy, Description) VALUES ('0121', '1.02.01.', 'Value of 0121')
 INSERT @Code (Code, Hierarchy, Description) VALUES ('0122', '1.02.02.', 'Value of 0122')
 INSERT @Code (Code, Hierarchy, Description) VALUES ('0123', '1.02.03.', 'Value of 0123')

-- create table to record values for codes

 Declare @CodeValues Table (Code varchar(10), Value money)

 INSERT @CodeValues (Code, Value) VALUES ('0111', 1)
 INSERT @CodeValues (Code, Value) VALUES ('0112', 2)
 INSERT @CodeValues (Code, Value) VALUES ('0113', 3)
 INSERT @CodeValues (Code, Value) VALUES ('0121', 4)
 INSERT @CodeValues (Code, Value) VALUES ('0122', 5)
 INSERT @CodeValues (Code, Value) VALUES ('0123', 6)

-- create temp table that will combine values with code hierarcies and will be used in the rollup

 Declare @TmpValues Table (Code varchar(10), CodeHierarchy varchar(10), Value money)

 INSERT @TmpValues (Code, Value, CodeHierarchy)
 Select
   v.Code,
   v.Value,
   c.Hierarchy
 From
   @CodeValues v
   Inner Join @Code c On c.Code = v.Code

-- Rollup Data

 Select
  c.Code,
  c.Description,
  Sum(v.Value)
 From
  @TmpValues v
  Join @Code c On v.CodeHierarchy Like c.Hierarchy+'%'
 Group By
  c.Code,
  c.Description
 Order By
  c.Code

This method was originally suggested by Scot Smith, Toronto.