Restoring a Microsoft SQL Server Database

This blog article is a scenario that I sent a coworker a while back about recovering a SQL Server database up to the point in time where a catastrophic hard disk drive failure occurs for the hard drive containing a SQL Server database. The transaction log for this database is on a separate physical disk and is still accessible.

I added the Northwind database to SQL Express on my machine, changed the recovery model to full, and then backed up the database and transaction log:

1BACKUP DATABASE Northwind TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup\Northwind.bak'
2WITH DESCRIPTION = 'Northwind Full Backup', NAME = 'Full Backup'
3GO
4BACKUP LOG Northwind TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup\Northwind1.trn'
5WITH DESCRIPTION = 'Northwind Log Backup', NAME = 'Log Backup'
6GO
1Processed 392 pages for database ‘Northwind’, file ‘Northwind’ on file 1.
2Processed 2 pages for database ‘Northwind’, file ‘Northwind_log’ on file 1.
3BACKUP DATABASE successfully processed 394 pages in 0.922 seconds (3.331 MB/sec).
4Processed 2 pages for database ‘Northwind’, file ‘Northwind_log’ on file 1.
5BACKUP LOG successfully processed 2 pages in 0.074 seconds (0.171 MB/sec).

I modified a row of data:

1UPDATE Categories
2SET CategoryName = 'Drinks'
3WHERE CategoryID = '1'

I stopped the SQL Server service and deleted the Northwind.mdf file and then restarted the SQL Server service, simulating a disk drive failure for the drive that contains the Northwind database. Keep in mind that the transaction log is on a separate physical disk.

db-restore1.jpg

I backed up the transaction log since it was still accessible:

1BACKUP LOG Northwind TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup\Northwind2.trn'
2WITH CONTINUE_AFTER_ERROR, DESCRIPTION = 'Northwind Log Backup - Tail', NAME = 'Log Backup'
1Processed 9 pages for database ‘Northwind’, file ‘Northwind_log’ on file 1.
2BACKUP LOG successfully processed 9 pages in 0.045 seconds (1.562 MB/sec).

I restored the database and applied all of the transaction log backups:

 1RESTORE DATABASE Northwind
 2FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup\Northwind.bak'
 3WITH NORECOVERY
 4GO
 5RESTORE LOG Northwind
 6FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup\Northwind1.trn'
 7WITH NORECOVERY
 8GO
 9RESTORE LOG Northwind
10FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\Backup\Northwind2.trn'
11WITH NORECOVERY
12GO
13RESTORE DATABASE Northwind WITH RECOVERY
14GO
 1Processed 392 pages for database ‘Northwind’, file ‘Northwind’ on file 1.
 2Processed 2 pages for database ‘Northwind’, file ‘Northwind_log’ on file 1.
 3RESTORE DATABASE successfully processed 394 pages in 0.335 seconds (9.168 MB/sec).
 4Processed 0 pages for database ‘Northwind’, file ‘Northwind’ on file 1.
 5Processed 2 pages for database ‘Northwind’, file ‘Northwind_log’ on file 1.
 6RESTORE LOG successfully processed 2 pages in 0.012 seconds (1.057 MB/sec).
 7Processed 0 pages for database ‘Northwind’, file ‘Northwind’ on file 1.
 8Processed 9 pages for database ‘Northwind’, file ‘Northwind_log’ on file 1.
 9RESTORE LOG successfully processed 9 pages in 0.020 seconds (3.515 MB/sec).
10RESTORE DATABASE successfully processed 0 pages in 0.512 seconds (0.000 MB/sec).

I validated my data changes which were only part of the transaction log backup that occurred after the simulated hard disk drive failure were restored to the database:

db-restore2.jpg

µ