Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

"Duplicate" database names cause backup failure #145

Closed
igorchernysh opened this issue Sep 27, 2018 · 5 comments
Closed

"Duplicate" database names cause backup failure #145

igorchernysh opened this issue Sep 27, 2018 · 5 comments

Comments

@igorchernysh
Copy link

Hi,

My instance has 2 databases: [ 18752] and [18752]. Backups fail with error:

Msg 50000, Level 16, State 1, Procedure DatabaseBackup, Line 686
The names of the following databases are not unique in the file system: [ 18752], [18752].

Any attempts to separate/exclude those 2 databases in parameter @Databases failed.

Thanks,
Igor.

@olahallengren
Copy link
Owner

This is by design.

The reason for that is that, by default I am using the database name as a directory name.

As it is not possible to have a directory name in Windows that starts with a space, I would end up backing up two databases to the same directory.

@igorchernysh
Copy link
Author

igorchernysh commented Sep 29, 2018 via email

@srutzky
Copy link

srutzky commented Sep 30, 2018

To be clear about the default rules: it is trialing spaces that are not allowed, not leading spaces.

The rules are stated in the "Naming Files, Paths, and Namespaces" documentation, under "Naming Conventions".

For testing:

  1. we should test directory names, not file names, since this issue is about directory names,
  2. we should use xp_create_subdir since that is what MaintenanceSolution uses, and could possibly be different than doing this directly in a Command Prompt.
-- Make sure test directory exists:
EXEC [master].dbo.xp_create_subdir N'C:\TEMP';
EXEC [master].dbo.xp_create_subdir N'C:\TEMP\PathTests';

-- Try a varying number of leading spaces:
EXEC [master].dbo.xp_create_subdir N'C:\TEMP\PathTests\H';
EXEC [master].dbo.xp_create_subdir N'C:\TEMP\PathTests\ H';
EXEC [master].dbo.xp_create_subdir N'C:\TEMP\PathTests\  H';
EXEC [master].dbo.xp_create_subdir N'C:\TEMP\PathTests\   H';
EXEC [master].dbo.xp_create_subdir N'C:\TEMP\PathTests\H  H';
EXEC [master].dbo.xp_dirtree N'C:\TEMP\PathTests';
-- Success (all 5 directories exist)

-- Try both leading and trailing spaces:
EXEC [master].dbo.xp_create_subdir N'C:\TEMP\PathTests\   K  ';
EXEC [master].dbo.xp_dirtree N'C:\TEMP\PathTests';
-- Success (kinda)

EXEC [master].dbo.xp_create_subdir N'C:\TEMP\PathTests\   K  \L';
/*
Msg 22048, Level 16, State 1, Line XXXXX
xp_create_subdir() returned error 3, 'The system cannot find the path specified.'
*/

EXEC [master].dbo.xp_create_subdir N'C:\TEMP\PathTests\   K\L';
-- Success (trailing spaces were removed from xp_create_subdir N'C:\TEMP\PathTests\   K  ')
EXEC [master].dbo.xp_dirtree N'C:\TEMP\PathTests';


EXEC [master].dbo.xp_cmdshell N'mkdir "C:\TEMP\PathTests\   K  "';
-- A subdirectory or file C:\TEMP\PathTests\   K   already exists.


EXEC [master].dbo.xp_cmdshell N'mkdir "C:\TEMP\PathTests\   K  \L"';
-- The system cannot find the path specified.

EXTRA CREDIT

The reason I emphasized "default" rules at the very top is that, technically, it is possible to create directories with trailing spaces. It just requires using the special \\?\ syntax (which also allows you to use long paths -- blog post on this coming soon -- but disallows using relative paths).

Continuing the previous examples:

EXEC [master].dbo.xp_create_subdir N'\\?\C:\TEMP\PathTests\  M  ';
EXEC [master].dbo.xp_create_subdir N'\\?\C:\TEMP\PathTests\  N  \Y';
EXEC [master].dbo.xp_dirtree N'C:\TEMP\PathTests';
-- Success (but you can't really see the trailing spaces when using xp_dirtree)

EXEC [master].dbo.xp_cmdshell N'dir /w C:\TEMP\PathTests';
/*
[.]     [..]    [   H]  [   K]  [  H]   [  M  ] [  N  ] [ H]    [H]     [H  H]
*/

EXEC [master].dbo.xp_cmdshell N'dir /s /b C:\TEMP\PathTests';
/*
C:\TEMP\PathTests\   H
C:\TEMP\PathTests\   K
C:\TEMP\PathTests\  H
C:\TEMP\PathTests\  M  
C:\TEMP\PathTests\  N  
C:\TEMP\PathTests\ H
C:\TEMP\PathTests\H
C:\TEMP\PathTests\H  H
C:\TEMP\PathTests\   K\L
C:\TEMP\PathTests\  N  \Y
*/

HOWEVER, the documentation states that even if you can create directory / file names with trailing spaces, you shouldn't.

@SQL-Matt
Copy link

SQL-Matt commented Oct 2, 2018

HOWEVER, the documentation states that even if you can create directory / file names with trailing spaces, you shouldn't.

I agree wholeheartedly with that comment. I don't want to think about the potential for administrative nightmare that could become when resorting to manual methods for trouble-shooting.

@olahallengren
Copy link
Owner

To be clear about the default rules: it is trialing spaces that are not allowed, not leading spaces.

I have changed the code to allow leading spaces in database names.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

4 participants