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

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

#0424 – SQL Server – Null value is eliminated by an aggregate or other SET operation. – Why? How to fix?

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.

#0423 – SQL Server – Exporting Database Diagrams for offline viewing

1 Reply

We a colleague of mine used Database Diagrams to explain our database structure to a new team member. Now typically, we would have started to “draw” the relationships on a white board, but in a world where everyone was working remotely, this was the only option.

The Visual Aspect

I am sure almost everyone in the audience has also used database diagrams at some point as a means of database documentation before switching to methods (like Extended Properties) more suited to modern database development and deployment techniques.

Unfortunately, these techniques do not have the ability to demonstrate the relationships visually. Database diagrams provide this unique ability and hence, warrant a rethink in terms of usage.

Now, the goal of this post is not to show “how” to build a database diagram, but to:

Demonstrate effective ways of providing as much detail as possible on the diagram
Show how to export the diagram for offline reference

Increasing the level of detail in a Database Diagram

For effective database diagraming, the recommendation is to group the tables/information shown on the diagram by one of the following two (2) strategies:

By schema: If you use database schemas, group objects by schema and have at least one database diagram per schema
By Use-case: Alternatively, tables that are related from a domain perspective (or for a particular use-case, e.g. Authentication) can be selected and be the subject of a diagram

Once you have put all the required tables on the diagram, you may want to right-click on the canvas and choose “Show Relationship Labels”. Additional annotations may also be applied as necessary by using the “New Text Annotation” functionality.

Image showing how to enable visibility of relationship labels on the diagram by right-clicking on the canvas and choosing "Show Relationship Labels" — Image showing how to enable visibility of Relationship labels on a database diagram

By default, the database diagram will only show the table name and list of columns. For maximum details, you can right-click on the table name -> select “Table View” -> select “Standard”.

Screenshot showing how to select the "Standard" view of tables on a database diagram. This will add more details (like datatype and null-ability of columns). — Screenshot showing how to select the “Standard” table view, which increases the level of detail on the diagram

As you will notice, using the “Standard” table view will add more details (like datatype and null-ability of columns) on the diagram. The columns can be added/removed by using the “Modify Column” option of the same menu.

Screenshot showing the "Modify Columns" screen which allows the user to select/choose columns on the table that may be necessary to review the design. — Column selector for the “Standard” view

Image showing how the standard view adds more details (like datatype and null-ability of columns) on the diagram. — “Standard” view of database tables on a diagram.

This process will need to be done for all tables. Once done, arrange the diagram on the canvas manually.

Exporting the Database Diagram

Once a database diagram is prepared, it can be saved in the database. However, there is no way to export or save a diagram into a file that can be sent via E-mail or stored on a collaboration tool for offline viewing. There is however, a very simple way by which the ultimate goal can be achieved – by storing it as an image!

Now, I am now talking about taking multiple screenshots and stitching them together in an image editing app. It is very simple to copy the diagram as an image.

Simply right-click on the canvas and choose “Copy Diagram to Clipboard”

Screenshot showing how to copy the database diagram to the clipboard. — Image showing how to copy the diagram to clipboard.

Once the diagram is on the clipboard, it can be pasted as an image to any image editing application or document!

#0422 – SQL Server – SSIS – Delete or rename files and perform other file operations without a script or writing code

The Solution

Assume that I have a set of files in a folder (following the pattern – SQLTwins*.txt) and I wanted to delete them. The Control Flow is quite procedural:

A ForEach Iterator is used to iterate through files in the directory
- The iterator is completely configurable – allowing the user to specify the folder name and the file name pattern
A FileSystem task is used to perform the actual file operation

Control Flow of a package that manipulates files without a script or code!

Now, allow me to walk-you through the package configuration:

Variables

The package is dependent upon the following variables:

Variable Name	DataType	Expression / Default Value	Remarks
SourceFolder	String	(My source folder path)
FileNamePattern	String	SQLTwins*.txt
CurrentFile	String		Variable to hold current file being iterated upon by the ForEach Iterator
FullyQualifiedFileName	String	SourceFolder + CurrentFile	Fully-Qualified file name to be used by the FileSystem task

List of User Variables on the SSIS pacakge

ForEach Iterator

The configuration of the Foreach Iterator is quite simple:

Collection
- The “Descriptions” and “FileSpec” expressions are set with the user variables – “SourceFolder” and “FileNamePattern” respectively
Variable Mappings
- This allows the package to capture the output of the iterator
- The variable “CurrentFile” will be used to capture the current file name

“Collection” tab of the ForEach Iterator showing “Descriptions” and “FileSpec” expressions set with the user variables – “SourceFolder” and “FileNamePattern” respectively

Variable Mappings showing the output of the ForEach Iterator setting the “CurrentFile” variable

File System Task

The configuration of the FileSystem task is even simpler! Other than the Name, the only configuration I did was to set the “Operation” and the “SourceVariable” variables.

That’s it! We are all set to give the package a spin and did not write a single line of code!

When we run the package, we can see right away that the files have been deleted.

Prior to execution, we can see that the files are still present.

Once the package is executed, the files are deleted!

The intention of the post was to demonstrate that with Microsoft SQL Server and related services, there are tools and components available which allow one to get started extremely quickly. If you have never worked with SSIS before, do explore the components available in the SSIS toolbox before getting into some serious scripting!

#0421 – SQL Server – SSIS – Wild card search to find if a file exists in a folder or directory

11 Replies

I recently answered a question on a forum which I believe will be useful to many of the readers in the audience.

SSIS packages are widely used for data import from and export to files. One of the main tasks in this situation would be to check if files with certain kinds of names exist in a particular folder or directory, i.e. basically perform a wild-card search in a directory.

The solution

This can be achieved by using the EnumerateFiles() method of the System.IO.Directory class in the SSIS Script task. Here’s the sample package:

In a folder, I have a set of files, some with similar names (which we will search from the SSIS package).

Files existing in the directory to be searched

The SSIS package has two (2) variables:

Variable Name	Configuration on the Script Task	Description
SearchPath	ReadOnly, Input	This is the path to be searched
SearchPattern	ReadOnly, Input	Pattern to be searched
FileExists	ReadWrite, Output	A boolean indicating downstream processes whether files were found or not

Table 1: Variables on the SSIS package

Screenshot showing the variables and their configuration on the script task

The script is a quite simple implementation as below:

public void Main()
{
    // SQLTwins: SSIS: Blog #0421

    string searchPath = Dts.Variables["User::SearchPath"].Value.ToString();
    string searchPattern = Dts.Variables["User::SearchPattern"].Value.ToString();

    System.Collections.Generic.List<string> searchResults = System.IO.Directory.EnumerateFileSystemEntries(searchPath, searchPattern).ToList();

    if (searchResults.Any())
    {
        Dts.Variables["User::FileExists"].Value = true;
    }

    Dts.TaskResult = (int)ScriptResults.Success;
}

Ensure that you have the following directive in the “namespaces” section of your script:

using System.Linq;

Here’s the script in action:

Screenshot showing the script in debug mode indicating that files were found matching the pattern.

As you can see, the script can help perform a wild-card search in a given folder or directory.

SQLTwins by Nakul Vachhrajani

SQL Server tips and experiences dedicated to my twin daughters.