May | 2021 | SQLTwins by Nakul Vachhrajani

Recently, I ran into a forum post where the ask was to figure out why a perfectly valid backup was not visible when attempting to restore it via the wizard in SSMS. Today, I will reproduce the issue, explain the root cause and provide the solution for the same.

Building the scenario

In one of the my test SQL Servers, I have a copy of the [AdventureWorks2019] sample database, which I have backed up using the following simple script.

USE [master];
GO
BACKUP DATABASE [AdventureWorks2019]
 TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.SQL2K17\MSSQL\Backup\AdventureWorks2019.bak'
    WITH COMPRESSION;
GO

Now, to simulate the movement of the backup to a different machine, I created a new folder under my default “Documents” folder and placed the backup there.

Screenshot showing the placement of the target folder where the backup is placed

Reproducing the symptom

Restoring via SSMS

Connect to the target SQL Server using SSMS
Right-click on the “Databases” folder in the Object Explorer
Choose to Restore a database
Under “Source”, select the radio-option for restoring from a “Device”
Use the ellipsis to open the “Select Backup Devices” window and open the File explorer by choosing “Add”
Navigate to the folder where the backup has been placed
1. Expected Result: We should be able to see the folder and the backup file
2. Actual Result: The backup file is not seen (the folder may or may not be seen)

Screenshot showing that the backup exists, but it is not seen in the "Locate Backup File" window — Screenshot showing that the backup exists, but it is not seen in the “Locate Backup File” window

Restoring via T-SQL

While the UI keeps things a bit mysterious, attempting to restore via T-SQL does point us to the right direction.

USE [master];
GO
RESTORE DATABASE [AdventureWorks2019_Copy]
    FROM DISK = 'C:\Users\sqltwins\Documents\AdventureWorksBackup\AdventureWorks2019.bak';
GO

Here’s the error that we run into:

Msg 3201, Level 16, State 2, Line 3
Cannot open backup device 'C:\Users\sqltwins\Documents\AdventureWorksBackup\AdventureWorks2019.bak'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 3
RESTORE DATABASE is terminating abnormally.

Notice that the error clearly says – “Access is denied.“

Root Cause

As highlighted by the results of the T-SQL script, SQL Server is actually running into a security problem. The operation is done under the context of the SQL Server instance service user (i.e. the user under which the SQL Server service runs).

Because the user doesn’t have access to the folder we just created, the service cannot see the files underneath.

Solution

The solution is to use the SQL Server Configuration Manager to figure out the user under which the SQL Server service runs.

Once the user is identified, provide access to the target folder to the user and the files should now be visible – both to SSMS and to T-SQL.

Screenshot showing the backup file is now visible once the SQL Server instance service has been granted to the folder

A quick test

The test below is simple – I am creating a sample test table which allows NULL values to be inserted. I am then trying to perform a simple aggregate function (SUM) over the NULL-able column. Upon checking the “Messages” tab, we see that no warning is returned.

USE [tempdb];
GO
SET NOCOUNT ON;

--Safety Check
IF OBJECT_ID('dbo.NULLAggregation','U') IS NOT NULL
BEGIN
    DROP TABLE [dbo].[NULLAggregation];
END
GO

--Create the test table
CREATE TABLE [dbo].[NULLAggregation] 
    ([Id]          INT           NOT NULL IDENTITY (1,1),
     [Value]       INT               NULL,
     [ValueString] VARCHAR(50)   NOT NULL,
     CONSTRAINT [pk_NULLAggregation] PRIMARY KEY ([Id])
    );
GO

--Insert the test data
INSERT INTO [dbo].[NULLAggregation] ([Value], [ValueString])
VALUES ( 1, 'One'),
       (10, 'Ten'),
       (22, 'Twenty-Two');
GO

--Perform the aggregation
--NOTE: No NULL values are in the table at this point
SELECT SUM([na].[Value]) AS [SumOfValues]
FROM [dbo].[NULLAggregation] AS [na];
GO

/****************************************
RESULT
-----------
SumOfValues
-----------
33
****************************************/

Now, I add a single record with a NULL value in the [Value] column and repeat the aggregation. While the result is the same, we have a warning in the “Messages” tab.

INSERT INTO [dbo].[NULLAggregation] ([Value], [ValueString])
VALUES ( NULL, 'One');
GO

SELECT SUM([na].[Value]) AS [SumOfValues]
FROM [dbo].[NULLAggregation] AS [na];
GO

/****************************************
RESULT
-----------
SumOfValues
-----------
33
Warning: Null value is eliminated by an aggregate or other SET operation.
****************************************/

A screenshot showing the warning encountered in SSMS when an aggregation operation is performed on a NULL value. — Warning encountered when aggregating on NULL values

The reason for the warning

To begin – this is just a warning and not an error. If your script/job is failing it is probably failing due to some other data condition OR as an indirect result of operating on NULL values.

The warning simply suggests that an aggregation operation is being done on a NULL value. If no NULL values are present in the dataset being evaluated, then the warning is not encountered.

Solutions

The important thing is to consider what is important for the business/domain.

If processing on NULL values are okay for the business/domain, then one of the following two (2) workaround can be applied:
- The warning can either be ignored OR
- Use the “SET ANSI_WARNINGS OFF” option for your query/procedure
If processing on NULL values is not acceptable for your business/domain, then
- Either use a simple WHERE clause to remove the records with NULL values from the aggregation
- Use input validations in the application code and NOT NULL checks in the database to stop the NULL values from being entered by the users

NOTE: If you do decide to use the “SET ANSI_WARNINGS OFF” option, please do so with caution. It can have unintended consequences with string operations as well (https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-warnings-transact-sql?view=sql-server-ver15)

I trust you found this post useful.

Until we meet next time,

Be courteous. Drive responsibly.

SQLTwins by Nakul Vachhrajani

SQL Server tips and experiences dedicated to my twin daughters.

Monthly Archives: May 2021

#0425 – SQL Server – Backup exists but doesn’t display on the restore window in SSMS. Why? How to fix?