SQL SERVER – How to Connect Using NT AUTHORITY\SYSTEM Account?

Sometimes it is needed to connect to SQL Server using System account. Don’t literally ask me if this is a valid scenario in first place. Someone just pinged to say – they want to do it. I was personally not sure why, but they had their own internal requirement to do the same.

It is not possible to provide windows credential in SSMS and they are always grayed out. My exploration is always to keep trying to find a solution to such typical use cases. I was able to find that PSExec can be used to achieve this.  I am a big fan of some of the tools from sysinternals. These are handy and quite small footprint of the servers. One of the tools I have day-in day-out is ZoomIt while doing presentations.

Step 1: Download PSTools from https://technet.microsoft.com/en-us/sysinternals/psexec.aspx

Step 2: Extract and open command prompt to the extracted location.

Step 3: Provide below command

psexec -i -s ssms.exe

-i parameter allow the program to run so that it interacts with the desktop of the specified session on the remote system.

-s parameter launches the process using SYSTEM account.

Here is what we would see on the command prompt.

NtAuth 01 SQL SERVER   How to Connect Using NT AUTHORITY\SYSTEM Account?

And it would launch SSMS program with User name populated as “NT AUTHORITY\SYSTEM”

NtAuth 02 SQL SERVER   How to Connect Using NT AUTHORITY\SYSTEM Account?

When I disable that account, I was getting below error in ERRORLOG

2016-02-03 15:31:07.560 Logon        Error: 18470, Severity: 14, State: 1.
2016-02-03 15:31:07.560 Logon        Login failed for user ‘WORKGROUP\SQLSERVER2016$’. Reason: The account is disabled. [CLIENT: <local machine>]

Notice that we do not see NT AUTHORITY\SYSTEM in Errorlog. We are seeing machine name followed by a dollar symbol. Since my machine is in a Workgroup called “WORKGROUP” and machine name is SQLSERVER2016, we are seeing WORKGROUP\SQLSERVER2016$

Have you ever used any such tool to troubleshoot anytime in your environment? What use cases were you able to use these tools in the past? Do let me know via comments.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #057 – What is GO in SQL SERVER?

GO is not a Transact-SQL statement; it is often used in T-SQL code. Go causes all statements from the beginning of the script or the last GO statement (whichever is closer) to be compiled into one execution plan and sent to the server independent of any other batches. SQL Server utilities interpret GO as a signal that they should send the current batch of Transact-SQL statements to an instance of SQL Server. The current batch of statements is composed of all statements entered since the last GO, or since the start of the ad hoc session or script if this is the first GO.

The GO Statement must be written in new line as it is not a T-SQL command. T-SQL statement cannot occupy the same line as GO. GO statement can contain comments.

Following is example for SQL SERVER for database Adventureworks.

USE AdventureWorks2014;
SELECT @MyMsg = 'Hello, World.'
GO ---- @MyMsg is not valid after this GO ends the batch.

—- Yields an error because @MyMsg not declared in this batch.


Reference: Pinal Dave (http://blog.sqlauthority.com)

MySQL – How to Generate Random Number

In MySQL, UUID() function returns Universal Unique Identifier that generates 36 characters long value which is 5 part hexadecimal numbers. If you want to create random password, you can make use of this function.


Returns the string 85aeb064-8f73-11e5-85ef-02fcc4101968 (Note that this is random, when you execute you will get different value). As the total length is 36, you can make use of the result to get a random password with any length.

Suppose you want to generate 8 character length, you can generate it using

SELECT LEFT(UUID(),8) random_password ;

If you want to generate 10 character length, you can generate it using

SELECT RIGHT(UUID(),10) random_password ;

This way you can generate a random password. As UUID () returns the hexadecimal number with five parts separated by hyphen, your word will have numbers, alphabets and hyphen depends on the length you use.

There can be many ways of doing this, but this is the simplest one.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Install Error – The /UIMode setting cannot be used in conjunction with /Q or /QS

Doing repetitive tasks is something everyone hates. I am sure many of us have resorted to ingenious techniques to figure out doing the task efficiently. There is no standard tool or technique that everyone uses. Recently I was asked to install a SQL instance with same configuration on 10 servers. Who would like to log in to all those servers, run setup.exe and go through the same setup wizard? Luckily, the SQL Server setup allows to create a configuration file. The very thought of extracting it as configuration is something that made me jump out of my seat.

UIMode 01 SQL SERVER   Install Error   The /UIMode setting cannot be used in conjunction with /Q or /QS

I copied the file from above location and moved to a second server on C drive. This was the easy part because I know the exact replica of configurations are going to flow to the new servers. Then I ran the below command:


This is a silent install that I am trying to do. The above command failed with below error which was strange to me:

The following error occurred:
The /UIMode setting cannot be used in conjunction with /Q or /QS.

Error result: -2054422500
Result facility code: 1420
Result error code: 28
Please review the summary.txt log for further details

I looked into the file and found below line.

; Parameter that controls the user interface behavior. Valid values are Normal for the full UI,AutoAdvance for a simplied UI, and EnableUIOnServerCore for bypassing Server Core setup GUI block.

I have added ; before that so that it is commented (you can remove that line also) and after that setup went fine and I was able to install it to nine servers in very short time. I also found that I can run setup remotely using PowerShell below command:

Invoke-Command -ComputerName xxx -ScriptBlock
{D:\SQL2012\setup.exe /ConfigurationFile=”D:\SQL2012\ConfigurationFile.ini”}

Have you ever used configuration file in your environments? What has been some of the automation techniques you used for installing softwares? Do let me know via comments below.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SSIS – Get Started with the For Loop Container – Notes from the Field #113

[Notes from Pinal]: SSIS is very well explored subject, however, there are so many interesting elements when we read, we learn something new. A similar concept has been Get Started with the For Loop Container.

Tim Mitchell SQL SERVER   SSIS   Get Started with the For Loop Container   Notes from the Field #113Linchpin People are database coaches and wellness experts for a data driven world. In this 113th episode of the Notes from the Fields series database expert Tim Mitchell (partner at Linchpin People) shares very interesting conversation related to how to get started with the FOR LOOP Container.

SQL Server Integration Services is equipped with tasks and containers to make it easy to design and maintain the flow of ETL: which logic should be executed, when should it be executed, and how many times should it occur. Most SSIS developers are familiar with the sequence container and the For Each Loop container, which can be used to group together tasks and execute the same logic a discrete number of times. In addition to these, there is a lesser-known but still very useful container for controlling logic flow: the For Loop container.

Simply put, the For Loop container executes its ETL logic zero to n times. It has three functions that control how many times the loop will be executed:

  • InitExpression: Used for the initial setting of a starting variable (such as setting a counter variable to zero).
  • EvalExpression: This is the expression that evaluates whether the loop should continue. Of the three functions described here, this is the only one that requires a value – the others are optional.
  • AssignExpression: This allows the use of an assignment expression, such as incrementing a loop counter.

For those with a programming background, this look very much like a for() loop statement in the C-derived languages. Functionally, it works in the exact same way as the for() loop, by continuing to execute the contained logic as long as the control condition remains true. This helps to draw contrast between the For Each Loop and the For Loop in SSIS. The former is list-based, and will execute for every item in the list supplied to it. The latter is value-based, and will execute as long as the EvalExpression is true.

In fairness, most ETL loads lean toward the list-based approach, but there are valid cases where a value-based approach is necessary. Some of those include:

  • Processing a fixed subset of data
  • Sampling for test or validation purposes
  • Forcing a “wait state” until some milestone is reached
  • Allowing the loop logic to be executed for some specified amount of time

Configuring the For Loop Container

As noted above, the only value that is required for the For Loop container is the EvalExpression. A very simple For Loop configuration is shown below.

113 1 SQL SERVER   SSIS   Get Started with the For Loop Container   Notes from the Field #113

The above supplies only the required value – a value of true to the EvalExpression. However, this is a very poorly configured For Loop, because the loop will continue executing indefinitely! True will always be true, so there is no logical end to this loop.

A more practical design pattern would use either an initialization expression, an assignment expression, or possibly both, to constrain the number of iterations. A simple example of this is shown below. I set up an SSIS package variable, typed as an Integer and named @vLoopCounter, with a default value of 0. In the For Loop settings, I’ve used the EvalExpression to check to see if this value is less than 10, and I use the AssignExpression to increment the @vLoopContainer value by 1 for every iteration of the loop.

113 2 SQL SERVER   SSIS   Get Started with the For Loop Container   Notes from the Field #113

This example works, executing any logic contained in the For Loop exactly ten times. However, this pattern is very static. What if I want to increase the value expression to let the loop run more than 10 times? I’d need to open the package and modify it. Fortunately, the configurations expressions allow for the use of both variables and parameters. Below, the package has a couple of updates: an initial value of 1 is set for the @vLoopCounter variable, and the static comparison in EvalExpression is replaced by using the package parameter @pMaxLoops for the maximum number of loops.

113 3 SQL SERVER   SSIS   Get Started with the For Loop Container   Notes from the Field #113

In the example above, the number of maximum loops can be specified at runtime, making for a more dynamic pattern.

The examples above show only an iteration based on the number of times the loop has run. Keep in mind when using the For Loop container, this logic can be based on any statement we choose: whether a particular set of files exist, how long the For Loop has been running, how many records have been processed, or some other custom metric specified in the expression. Even with a small number of inputs controlling how many times the For Loop container will execute, the possible uses for this are many, and can be as complex as needed.


The For Loop container provides another way to execute repeating logic in SSIS. By using an approach similar to the for() loop in structured programming languages, the For Loop container adds more ETL flexibility through a value-based iterative pattern.

If you want me to take a look at your server and its settings, or if your server is facing any issue we can Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER InMemory OLTP: Understanding Optimistic Multi-Version Concurrency Concepts

InMemory is an awesome concept and I have been fortunate to learn about the same from my friends in the industry over blogs. It holds deep concepts and is sometimes tough to understand how the building blocks come together. I am not the only one doing the exploration, but a number of you also do and pass those learnings to me via your interesting questions. Thanks to each one of you who take a moment to ask me some of these questions that shake the fundamentals and make my understanding stronger. In the same lines, one of the readers wrote back to me after reading the MSDN on InMemory OLTP, what does the concepts of “Optimistic multi-version concurrency” really mean. On first thought, it looks simple, but the question was very loaded.

This blog is very much inspired by this question and I thought to pen this down as a blog with examples to show how SQL Server handles the same. We will look at various simple examples to illustrate the behavior for easy understanding.

Creating our basic DB and Table

I will do the basic structure from previous posts on what the template would be. This is a simple script to start:

USE [master]
-- Below statement works only from SQL Server 2016
-- Change this appropriately if you are running in prior versions
( NAME = N'InMem_OLTP_data', FILENAME = N'C:\DATA\InMem_OLTP_data.mdf' , SIZE = 20480KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),
( NAME = N'InMem_OLTP_InMemory', FILENAME = N'C:\Data\InMem_OLTP_mopt' , MAXSIZE = UNLIMITED)
( NAME = N'InMem_OLTP_log', FILENAME = N'C:\DATA\InMem_OLTP_log.ldf' , SIZE = 20480KB, FILEGROWTH = 10%)

-- Creating the table for experimentations
CREATE TABLE dbo.SalesOrder_inmem
order_status tinyint NOT NULL,
INSERT INTO dbo.SalesOrder_inmem VALUES

Understanding Optimistic part

Let me try to break the question into multiple parts and we will create the scenario that will showcase the optimistic part. The steps I am going to take has been explained in the table below:

Steps Session 1 Session 2
2 INSERT INTO InMemory Table
3 Read from InMemory Table
5 Read from InMemory Table

As you can see above, this is will be done in two sessions windows and we will switch between sessions for the demo.
-- Session 1
dbo.SalesOrder_inmem VALUES

-- Session 2
SELECT * FROM dbo.SalesOrder_inmem

 SQL SERVER InMemory OLTP: Understanding Optimistic Multi Version Concurrency Concepts

As you can see, we are still seeing only 5 records and the 6th record for which the transaction is still open is not visible. This will be the state in our session 2 infinitely till the transaction in Session 1 finishes. You can see SQL Server has made a version and made sure our query on Session 2 is not getting blocked. Since we have taken an optimistic concurrency, the Session 2 is still able to query.

Let us Commit on Session 1 and do the query again on Session 2.

-- Session 1

-- Session 2
SELECT * FROM dbo.SalesOrder_inmem

 SQL SERVER InMemory OLTP: Understanding Optimistic Multi Version Concurrency Concepts

Now you can see, without doing anything, the additional record now pops up automagically here now.

Understanding Multi-Versioning better

The multi-versioning is not clear yet and clearly. So to make that point, let me go ahead an update a record and see the different versions in our session.

Steps Session 1 Session 2
2 UPDATE InMemory Table
3 Read from InMemory Table
5 Read from InMemory Table

Let us create the transaction to update the InMemory table. This is achieved as below:

-- Scenario 2
-- Session 1
dbo.SalesOrder_inmem WITH (SNAPSHOT)
SET amount = 10000
WHERE order_id = 6

-- Session 2
SELECT * FROM dbo.SalesOrder_inmem

 SQL SERVER InMemory OLTP: Understanding Optimistic Multi Version Concurrency Concepts

The value of 10000 is NOT visible to the Session 2 till the transaction is committed on Session 1. This shows SQL Server’s capability to maintain multiple version for the records in an InMemory objects much more clearly.

Hope you were able to get a clearer understanding of how InMemory tables work in an optimistic concurrency model. Do let me know if you learnt something new or if I have missed anything that you would like to have more clarity on.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – dbForge Object Search for SQL Server

SSMS is a nice tool for database developers. However, it is far from being ideal, and this is because some routine operations in SSMS are extremely inconvenient to perform.

Very often, developers face the challenge of searching a database for all occurrences of an object, column, variable, or search simultaneously all the objects, in which a search value occurs. If you happen to solve a similar problem, then you must know that this problem is not the most trivial and Ctrl + F will not help here.

Of course, you can write a quick metadata query:

SELECT SCHEMA_NAME(o.[schema_id]), o.name, o.type_desc, m.[definition]
FROM sys.sql_modules m
JOIN sys.objects o ON m.[object_id] = o.[object_id]
WHERE [definition] LIKE '%PersonID%'

However, not all developers know metadata structure … and why waste time writing a query, if you already have an excellent free plug-in that helps to effectively search the wilds of DDL.

Though dbForge Object Search for SQL Server has been released not so long ago, it occupied the place of pride in my gentleman’s set.

This plug-in impresses me with the simplicity of use — type a text in the search box and click Enter:

devsearch01 SQL SERVER   dbForge Object Search for SQL Server

All the search results are displayed in a table that supports filtering. When you select a respective object, its DDL is displayed below… but not as a simple text. The plug-in has a convenient syntax highlighting.

If you need to restrict your search, you can configure the filtering by object type. For example, we can search only within stored procedures and triggers.

devsearch02 SQL SERVER   dbForge Object Search for SQL Server

Additional filters allow you to search much faster.

dbForge Object Search for SQL Server does not cache the information between the searches and directly accesses metadata. For me, it’s definitely a plus, especially when in active development and continuously updating database schema — you don’t need to constantly press Refresh cache to get the proper search results.

If necessary, you can do a search on multiple databases at the same time:

devsearch03 SQL SERVER   dbForge Object Search for SQL Server

The plug-in supports navigation. Simply select the context menu command Find in Database Explorer, and you will automatically jump to the found object:

devsearch04 SQL SERVER   dbForge Object Search for SQL Server

When working with this plug-in, I discovered some pleasant things. For example, previous search queries are saved in the search history:

devsearch05 SQL SERVER   dbForge Object Search for SQL Server

If we talk about the object search, doing it in SQL Server Management Studio is rather inconvenient. Queries dealing with this task are inefficient and require deep knowledge of the SQL Server system objects. By contrast, dbForge Object Search for SQL Server does the task brilliantly.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – 2016 IF EXISTS Functions on SQL Azure Databases and More

The experimentations of working with the next release of SQL Server is always fun and when I wrote the blog SQL Server – 2016 – T-SQL Enhancement “Drop if Exists” clause, lesser did I know such capabilities are already existing with Azure SQL DB. When I was presenting at one of the local usergroups, I told most of the capabilities come to Cloud first, get test and the very reason that it scales without problem on cloud gives one the confidence that it would work in our enterprise. One of the attendees asked, has all features first introduced to cloud only?

That got me curious and I started to hunt to find is the rollout always to cloud first. I thought let me experiment first and see. I took my DIE (DROP IF EXISTS) post to see if I can run it on SQLAzureDB to know if it worked seamlessly. First to my connection to the latest database I created.

 SQL SERVER   2016 IF EXISTS Functions on SQL Azure Databases and More

As I was running the CTP2.0 of SQL Server 2016, I wanted to see what version was running on cloud already. It was a surprise that the production was already running the latest bits. It was a pleasant surprise and I know by the time you read this article and try out – maybe Microsoft would have upgraded to the latest bits too. So is the scale and agility that cloud brings to the table.

Now coming back to the core, I was now getting a feeling that my DIE (DROP IF EXISTS) code will work now perfectly. I went ahead with the following code and it executed just fine.

DROP PROCEDURE IF EXISTS my_test_procedure

Now, it was time to search for something interesting that I can do on cloud that was not available on-premise SQL Server. So I went to the documentation to search for something interesting. I found a new function DATEDIFF_BIG() that was interesting. Currently the DATETIME functions that we use with SQL Server cannot give the high precision values. Let me take a typical value of – number of nanoseconds in a year?

SELECT DATEDIFF_BIG(nanosecond, '2015-1-1 00:00:00.0000000', '2016-1-1 00:00:00.0000000')

This returns “31536000000000000” which is not available with SQL Server 2016 on-premise version currently. It was a great learning to see the rate at which innovations happen on cloud. If you run this on an on-premise SQL Server – you will get the following error today.

Msg 195, Level 15, State 10, Line 1
‘DATEDIFF_BIG’ is not a recognized built-in function name.

I am curious to know, how many times have you wanted the higher level of precision when working with DATEDIFF functions? What are some of your usecases for the same? I would surely like to learn some from you. Do let me know via the comments.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Interview Question of the Week #056 – How to fix Installation Failure – Rule “Setup account privileges” Failed in SQL Server

Sometimes in the interviews I observe that users are able to answer all the theoretical questions correct but when it is about doing practical they fail. A while ago, when I was helping a large organization with interview, suddenly their DBA reported that one of their server has installation error. We found this as a great opportunity to test a new candidate and gave him problem to solve. He was successfully able to solve the problem.

Question: How will you fix the installation failure error setup account privileges error in SQL Server?

setup rule 01 Interview Question of the Week #056   How to fix Installation Failure – Rule Setup account privileges Failed in SQL Server


When I clicked on “failed” hyperlink, here is the message.

setup rule 02 Interview Question of the Week #056   How to fix Installation Failure – Rule Setup account privileges Failed in SQL Server

Here is the text of the message.

Rule Check Result
Rule “Setup account privileges” failed.
The account that is running SQL Server Setup does not have one or all of the following rights: the right to back up files and directories, the right to manage auditing and the security log and the right to debug programs. To continue, use an account with both of these rights. For more information, see http://msdn.microsoft.com/en-us/library/ms813696.aspx, http://msdn.microsoft.com/en-us/library/ms813959.aspx and http://msdn.microsoft.com/en-us/library/ms813847.aspx.

I always look at setup logs to see the exact issue. I opened “SystemConfigurationCheck_Report.htm” and below is the failure.

setup rule 03 Interview Question of the Week #056   How to fix Installation Failure – Rule Setup account privileges Failed in SQL Server

Then I looked into “Detail.txt” and searched for “HasSecurityBackupAndDebugPrivilegesCheck” as shown in above screenshot. Failure is listed below.

(09) 2016-01-28 19:58:11 Slp: Initializing rule      : Setup account privileges
(09) 2016-01-28 19:58:11 Slp: Rule is will be executed  : True
(09) 2016-01-28 19:58:11 Slp: Init rule target object: Microsoft.SqlServer.Configuration.SetupExtension.FacetPrivilegeCheck
(09) 2016-01-28 19:58:11 Slp: Rule ‘HasSecurityBackupAndDebugPrivilegesCheck’ Result: Running process has SeSecurity privilege, has SeBackup privilege and does not have SeDebug privilege.
(09) 2016-01-28 19:58:11 Slp: Evaluating rule        : HasSecurityBackupAndDebugPrivilegesCheck
(09) 2016-01-28 19:58:11 Slp: Rule running on machine: PINALVM1
(09) 2016-01-28 19:58:11 Slp: Rule evaluation done   : Failed

As highlighted above, the account which was running setup was missing second one.

SeSecurity: Manage auditing and the security log

SeDebug: Debug Programs

SeBackup: Back up files and directories

To see the “Local Security Policy”, we can go to Start > Run > SecPol.msc or open it from Administrative Tools.

setup rule 04 Interview Question of the Week #056   How to fix Installation Failure – Rule Setup account privileges Failed in SQL Server

“Debug programs” doesn’t have any user. By default, “Administrators” group is part of it which was removed by me during some hardening. Once I added default account, I have to restart the computer to get forward from that error.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’

I do apply patches to my SQL instances as and when they are released by Microsoft. This is important because I always feel keeping the bits uptodate is essential because I don’t want to get infected as I travel quite a bit. Since I play a lot with my SQL Server, there are more chances that things are going to break sooner than your production server. Here is one such incident.

I was trying to apply patch to my SQL instance and it went fine. But then I was NOT able to start SQL Server service. Here are the messages from SQL ERRORLOG.

2016-01-24 06:14:40.63 spid7s      Error: 537, Severity: 16, State: 3.
2016-01-24 06:14:40.63 spid7s      Invalid length parameter passed to the LEFT or SUBSTRING function.
2016-01-24 06:14:40.63 spid7s      Error: 912, Severity: 21, State: 2.
2016-01-24 06:14:40.63 spid7s      Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 537, state 3, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
2016-01-24 06:14:40.64 spid7s      Error: 3417, Severity: 21, State: 3.
2016-01-24 06:14:40.64 spid7s      Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.
2016-01-24 06:14:40.64 spid7s      SQL Trace was stopped due to server shutdown. Trace ID = ‘1’. This is an informational message only; no user action is required.

I knew that I need to use trace flag 902 to bypass script upgrade mode and fix something. It was challenging to find what to fix. So I started looking for ‘sqlagent100_msdb_upgrade.sql’ which was found under “Install” folder in “C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL”. I started SQL using trace flag 902 as below


Refer: SQL SERVER – 2005 – Start Stop Restart SQL Server from Command Prompt

Then I was able to connect to SQL Server because the problem script didn’t run due to trace flag. I ran the script manually and found below piece of code failing.

DECLARE @dataDirName NVARCHAR(520)
SELECT @dataDirName = SUBSTRING(physical_name, 1, CHARINDEX(N'master.mdf', LOWER(physical_name)) - 1)
FROM MASTER.sys.master_files
WHERE (name = N'master')

script upgrade 01 SQL SERVER    Script level upgrade for database master failed because upgrade step sqlagent100 msdb upgrade.sql

Now, we need to figure out what’s wrong and how to fix it. So, I modified query to get details.

SELECT  DB_NAME(database_id) 'DB name', name 'logical name', physical_name
FROM MASTER.sys.master_files
WHERE (name = N'master')

script upgrade 02 SQL SERVER    Script level upgrade for database master failed because upgrade step sqlagent100 msdb upgrade.sql

Now we can see a problem. The problem is because we have two rows and master.mdf doesn’t exist in second row. It is failing because a test database had filename set correctly but it’s logical name was set as master.

script upgrade 03 SQL SERVER    Script level upgrade for database master failed because upgrade step sqlagent100 msdb upgrade.sql

To fix the problem I corrected the logical name of the user database which had been incorrectly set as master. Once this was done then, I stopped SQL and started it normally (without trace flag 902) and it was able to start successfully.

Reference: Pinal Dave (http://blog.sqlauthority.com)