SQL Server – Database Restore Steps thru lenses of errorlog

Introduction

Wanted to document database restore steps as seen from trail left in SQL Server Instance’s errorlog file.

Restore

Restore – Full Database

Step Sub Task Logged Explanation

Start Database (Database started in boot-strap mode )
Starting up database ‘csAnalytics’.

Recovery or NO Recovery
NO Recovery
The database ‘csAnalytics’ is marked RESTORING and is in a state that does not allow recovery to be run. Indicate that we are restoring and will not proceed to actual database recovery; as we would have done during normal SQL Server Instance start.
Recovery
Recovery is writing a checkpoint in database ‘csAnalytics’ (9). This is an informational message only. No user action is required. Database restore wrote a checkpoint ledger to indicate progress.
2018-03-20 05:46:51.110    spid70    Recovery completed for database rbpivr1 (database ID 42) in 12 second(s) (analysis 5444 ms, redo 20 ms, undo 5171 ms.) This is an informational message only. No user action is required. Informational in terms of duration.

Here is what is logged – Database name, Assigned Database ID, and total time.

Time breakdown – Analysis, redo, undo.

2018-03-20 05:46:51.550    spid70    Filegroup fgLOB in database rbpivr1 is unavailable because it is Offline. Restore or alter the filegroup to be available. In the case of partial restores, the file groups implicitly excluded.
2018-03-20 11:10:58.790    Backup    Database was restored: Database: DBMirror, creation date(time): 2018/03/05(14:52:06), first LSN: 35:341:37, last LSN: 35:360:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘C:\Microsoft\SQLServer\Backup\DBMirror.bak’}). Informational message. No user action required. Informational in terms of statistics.

Data logged includes name given to target database, creation time, LSN, number of dump/backup devices, and backup device names.


In-Memory Database ( Hekathon )
[INFO] HkCheckpointCtxtImpl::StartOfflineCkpt(): Database ID: [42]. Starting offline checkpoint worker thread on a hidden SOS scheduler. Hekathon Offline Checkpoint
Check Database
CHECKDB for database ‘csAnalytics’ finished without errors on 2011-08-03 00:06:43.420 (local time). This is an informational message only; no user action is required. Minimal Database Consistency ran.
Resource DB Alignment
2018-03-20 05:46:51.950    spid70    Synchronize Database ‘rbpivr1’ (42) with Resource Database. Database system objects aligned with Instance’s Resource DB.

Especially important when database backup file restored from earlier SQL Server version.


Restore Complete
Restore is complete on database ‘rbpivr1’.  The database is now available. Restore Completed

Database Setting
Setting database option MULTI_USER to ON for database csAnalytics. Database transitioned from Single to Multi User

 

Restore – Transaction Log

Logged Explanation
Log was restored. Database: DBMirror, creation date(time): 2018/03/05(14:52:06), first LSN: 35:341:37, last LSN: 35:422:1, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {‘C:\Microsoft\SQLServer\Backup\DBMirror.trn’}). This is an informational message. No user action is required. Conveys that Transaction Log backup file was applied to specified database.  It lets us in on the starting and ending LSN.  And, the name of the transaction backup file.

Summary

Data logged in the SQL Server Instance log is quite useful in gleaning what is occurring behind the scene when a database is restored.

Comparing logs from different SQL Server Instances and Database setup affords familiarity with how various database setup and restore options is considered and acted upon by the DB restore facility.

As summary, the code will consider:

  1. Will the database be recovered or left in no_recovery
    • No recovery basically means subsequent transaction logs will be applied
  2. Fast Database Consistency check occurs
  3. Redo and Undo Log Processing
    • Logs contain both redo and undo portions
      • Redo
        • Once database is fully restored, committed transactions that are still in the transaction log, but have yet to be checkpoint into the database data files are applied to restored database datafiles
      • Undo
        • Undo means that data is not committed and still in flight.
        • The data can later be committed ( commit tran ) or discarded ( rollback )
  4. Resource DB Alignment
    • When a database from a previous version of SQL Server is restored unto a newer version the restored db system dictionary has to be brought up to date
  5. In Memory Database
    • In-Memory tables and programmable objects are read from database files into memory

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s