Deadlock in SQL Server

 A  video demonstration of a simple Deadlock between two sessions (processes) in SQL Server and how you, as the DBA, can identify the two processes and the SQL statements in the Deadlock.

SQL Server Migration Assistant for Oracle

 Here's a Video Demo of the SQL Server Migration Assistant for Oracle  https://youtu.be/zNTF1ncr45g

The tool is available for download here

Restoring the Master Database

 In my previous blog post, I've provided a link to a Microsoft Document on Restoring the Master Database.

Here's another method.

First, I find that my master.mdf file is corrupt when I try to start the Instance.

Message in the ERRORLOG file when I try to start the Instance, because the file is corrupt :

date-time (removed) Server  Configuration block version 0 is not a valid version number. 
SQL Server is exiting. 
Restore the master database or reinstall

I than look for a Backup of the master database and Restore it to *another* SQL Server Instance running exactly the same Release and Patch Level.

The Restore of the Backup is done with a *different* database name !! This is vitally important, as I don't want to attempt to overwrite master in the second SQL Server Instance.

I then DETACH this Restored Database from the 2nd Instance

The Detached Database and Log file are now available to me  (note the mdf file name)

I then copy these two files as "master.mdf" and "mastlog.ldf" to the SQL Server Installation DATA folder of the 1st Instance (or whichever folder these files were configured in during the initial install)

I now attempt to start the SQL Server Instance that had failed and find that it is successful

And this is from the ERRORLOG now that shows that master database is good

2021-09-19 12:59:13.86 Server      Registry startup parameters: 
	 -d D:\SQL_Server_1\MSSQL14.SQL_SERVER_1\MSSQL\DATA\master.mdf
	 -e D:\SQL_Server_1\MSSQL14.SQL_SERVER_1\MSSQL\Log\ERRORLOG
	 -l D:\SQL_Server_1\MSSQL14.SQL_SERVER_1\MSSQL\DATA\mastlog.ldf
2021-09-19 12:59:13.86 Server      Command Line Startup Parameters:
	 -s "SQL_SERVER_1"
2021-09-19 12:59:13.93 Server      SQL Server detected 1 sockets with 6 cores per socket and 12 logical processors per socket, 12 total logical processors; using 12 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
2021-09-19 12:59:13.93 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2021-09-19 12:59:13.95 Server      Detected 16234 MB of RAM. This is an informational message; no user action is required.
2021-09-19 12:59:13.95 Server      Using conventional memory in the memory manager.
2021-09-19 12:59:13.97 Server      Page exclusion bitmap is enabled.
2021-09-19 12:59:14.39 Server      Default collation: SQL_Latin1_General_CP1_CI_AS (us_english 1033)
2021-09-19 12:59:14.82 Server      Buffer pool extension is already disabled. No action is necessary. 
2021-09-19 12:59:15.47 Server      InitializeExternalUserGroupSid failed. Implied authentication will be disabled.
2021-09-19 12:59:15.47 Server      Implied authentication manager initialization failed. Implied authentication will be disabled.
2021-09-19 12:59:15.81 Server      Query Store settings initialized with enabled = 1, 
2021-09-19 12:59:15.88 Server      The maximum number of dedicated administrator connections for this instance is '1'
2021-09-19 12:59:15.95 Server      This instance of SQL Server last reported using a process ID of 5072 at 16/9/2021 9:13:05 PM (local) 16/9/2021 1:13:05 PM (UTC). This is an informational message only; no user action is required.
2021-09-19 12:59:16.09 Server      Node configuration: node 0: CPU mask: 0x0000000000000fff:0 Active CPU mask: 0x0000000000000fff:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2021-09-19 12:59:16.15 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2021-09-19 12:59:16.28 Server      In-Memory OLTP initialized on lowend machine.
2021-09-19 12:59:16.41 Server      CLR version v4.0.30319 loaded.
2021-09-19 12:59:16.46 Server      Database Instant File Initialization: disabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.
2021-09-19 12:59:16.66 Server      Common language runtime (CLR) functionality initialized using CLR version v4.0.30319 from C:\Windows\Microsoft.NET\Framework64\v4.0.30319\.
2021-09-19 12:59:16.71 Server      Software Usage Metrics is disabled.
2021-09-19 12:59:17.09 spid6s      Starting up database 'master'.
2021-09-19 12:59:18.94 spid6s      CHECKDB for database 'master' finished without errors on 2021-03-20 18:34:11.700 (local time). This is an informational message only; no user action is required.

Of course, any updates to master (e.g. new databases created in the instance) that were done after the backup are not available in the restored instance

The same method can be used for msdb as well.  With msdb, when you restore msdb, you would lose any new jobs that were defined after the backup and also information about recent backups that were present in the corrupt msdb database.