Snippet Name: Finding_SQL_Server_Upgrade

Tags:

Created Date: 2025-06-16 13:12:49
Last Modified Date: 2025-05-31 10:31:51

Dependencies:

DBA Notes:

Sample Output:

Title: Finding SQL Server Upgrade
Author: user
is there a query i can run to find out when a server was upgraded from SQL2016 to SQL2019?

Author: ChatGPT
To find out when a SQL Server was upgraded from SQL Server 2016 to SQL Server 2019, you can check the SQL Server error logs for the first instance of the SQL Server 2019 startup. When the SQL Server service starts, it logs the version and build number, which can indicate the upgrade time.

Here is a query to extract that information from the SQL Server error log:

```sql
EXEC sp_readerrorlog 0, 1, 'SQL Server', '2019';
```

This query reads the SQL Server error log and looks for entries that contain both `'SQL Server'` and `'2019'`. The first occurrence of this would typically represent the time when the server was upgraded and restarted.

You may need to examine multiple logs if the server has been running for a while. You can do that by changing the first parameter (e.g., `sp_readerrorlog 1` for the second log file, `sp_readerrorlog 2` for the third, etc.).

Alternatively, you can use the following approach to extract the first log entry when SQL Server 2019 was started:

```sql
DECLARE @LogNum INT = 0;
DECLARE @CurrentLogDate DATETIME;

-- Loop through the error logs to find the first occurrence of SQL Server 2019
WHILE @LogNum <= 99 -- Assuming a maximum of 99 error logs
BEGIN
    EXEC sp_readerrorlog @LogNum, 1, 'SQL Server', '2019';
    -- Assuming the first log entry contains the version change
    SET @CurrentLogDate = (SELECT TOP 1 [LogDate]
                           FROM sys.fn_xe_file_target_read_file
                                ('system_health*.xel', NULL, NULL, NULL)
                           WHERE message LIKE '%SQL Server 2019%'
                           ORDER BY [LogDate]);
    IF @CurrentLogDate IS NOT NULL
    BEGIN
        PRINT 'The server was upgraded to SQL Server 2019 on: ' + CONVERT(VARCHAR, @CurrentLogDate, 120);
        BREAK;
    END;
    SET @LogNum = @LogNum + 1;
END;
```

This will loop through the error logs and print the date of the first occurrence of SQL Server 2019 starting.

Would you like more information on how to automate the log searching process, or how to get the exact build version details?