While writing my earlier blog, I broke a few settings and started getting new errors. Earlier, when I used to run below command, it used to work fine. However, just a day ago, it has given me error related to the BackupDirectory Registry Key.
BACKUP DATABASE [SQLAuthority] TO DISK = N'SQLAuthority.bak'
When I ran the command today, I faced below error.
Msg 3047, Level 16, State 1, Line 1
The BackupDirectory registry key is not configured correctly. This key should specify the root path where disk backup files are stored when full path names are not provided. This path is also used to locate restart checkpoint files for RESTORE.
Msg 3038, Level 16, State 1, Line 1
The file name “SQLAuthority.bak” is invalid as a backup device name. Reissue the BACKUP statement with a valid file name.
Msg 3013, Level 16, State 1, Line 1
BACKUP DATABASE is terminating abnormally.
Here is the UI message for the same error:
The command would work if I provide the complete path of the backup. If you notice the command which I normally run (to avoid more typing) is just a file name and it goes to the configured default backup directory. We are getting error because I messed up that setting.
Here is an earlier blog which talks about this setting.
SQL SERVER – Error: Property BackupDirectory is Not Available for Settings
Solution/Workaround
Enter the correct value in backup folder under “Database Settings” tab under “Properties” of a SQL instance in management studio.
Once I entered the path in the “Backup” textbox, backup command was working as earlier.
Have you ever shot yourself in the foot like me?
Reference: Pinal Dave (https://blog.sqlauthority.com)
