SQL Server is trying to use two paths for backup location

Question

I am struggling with a backup job created under Maintenance Plans for one installation.

A "Back Up Database Task" is set for specific databases, option "Disk" is selected, as well as "Create a backup file for every database", and "Create a sub-directory for each database". Folder is set as a browseable UNC path:

\\\backupserver\SQLBack\servername

View T-SQL shows the same correct backup location:

BACKUP DATABASE [DBName] 
TO DISK = N' \\\backupserver\SQLBack\servername\DBName\DBName_backup_2018_04_27_141724_2876433.bak' 
WITH NOFORMAT, NOINIT, 
     NAME = N'DBName_backup_2018_04_26_141724_2856433', 
     SKIP, REWIND, NOUNLOAD, NO_COMPRESSION,  STATS = 10

Everything looks great, but the actual path is failing with the actual path being different from what it should be:

Executing the query "BACKUP DATABASE [DBName] TO DISK = N' \\..." failed with the following error: "Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\ \\backupserver\SQLBack\servername\\DBName\DBName_backup_2018_04_26_124542_5356659.bak'.

I can't figure out why is it appending C:\\Program Files\\Microsoft SQL Server\\MSSQL.1\\MSSQL\\Backup\\ to the entered backup location, any help is appreciated.

Both locations are available to the SQL Server.

Check the extra space in TO DISK = N' \\backupserver`. Right after the N' `. My guess is that the backup task is seeing that extra space and switching it to your default SQL install location. — Shawn
– Shawn, Commented Apr 27, 2018 at 21:29
I'll be damned. Thank you, with that space removed it is backing up successfully! — user2101153
– user2101153, Commented Apr 27, 2018 at 21:36
OK, just confirmed. The extra space will cause the backup process to prepend your default SQL backup location. — Shawn
– Shawn, Commented Apr 27, 2018 at 21:37
I never knew it would do that either. Learned something new today. :-) — Shawn
– Shawn, Commented Apr 27, 2018 at 21:38
Take a look at this. This might also help: community.spiceworks.com/topic/107157-problem-with-sql-backups — DxTx
– DxTx, Commented Apr 28, 2018 at 6:02

Shawn · Accepted Answer · 2018-04-27 21:40:22Z

1

There is an extra space in TO DISK = N' \\...", after the N'. This will cause the default SQL backup location to be prepended to the TO DISK setting. Removing that extra space will allow it to backup normally.

answered Apr 27, 2018 at 21:40

Shawn

4,7861 gold badge22 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2101153 Over a year ago

Thank you again! I was fooled by View T-SQL script.

SMor Over a year ago

@user2101153 Can you accept the response as the answer please?

Collectives™ on Stack Overflow

SQL Server is trying to use two paths for backup location

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related